Updating a lookup field in a sharepoint List using SOAP in VBA for Excel

I am trying to update a Sharepoint list using a Macro in Excel 2010. So far I have had no problems updating regular textual or numeric fields, but when it comes to lookup fields, it throws back an error 0x80002005. I am guessing that you cannot simly use an existing literal from the list but cannot for the life of me find any documentation on the appropriate syntax.

Here is my VBA code.

 Set ObjHTTP = New MSXML2.XMLHTTP
 sURL = "http://SITE_NAME_HERE_vti_bin/lists.asmx?op=UpdateListItems"

 sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
 sEnv = sEnv & " <soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" "
 sEnv = sEnv & " xmlns:xsd=""http://www.w3.org/2001/XMLSchema"""
 sEnv = sEnv & " xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
 sEnv = sEnv & " <soap:Body>"
 sEnv = sEnv & " <UpdateListItems xmlns=""http://schemas.microsoft.com/sharepoint/soap/"">"
 sEnv = sEnv & " <listName>MYLISTNAME</listName>"
 sEnv = sEnv & " <updates>"
 sEnv = sEnv & " <Batch OnError=""Continue"" ListVersion=""1"">"
 sEnv = sEnv & " <Method ID=""1"" Cmd=""New"">\"


 sEnv = sEnv & " <Field Name=""MYLOOKUPFIELD"">"
 sEnv = sEnv & ""
 sEnv = sEnv & " </Field>"



 sEnv = sEnv & " </Method>"
 sEnv = sEnv & " </Batch>"
 sEnv = sEnv & " </updates>"
 sEnv = sEnv & " </UpdateListItems>"
 sEnv = sEnv & " </soap:Body>"
 sEnv = sEnv & " </soap:Envelope>"

 MsgBox (sEnv)

 ObjHTTP.Open "Post", sURL, True
 ObjHTTP.setRequestHeader "Content-Type", "text/xml"
 ObjHTTP.setRequestHeader "dataType", "   xml"
 ObjHTTP.setRequestHeader "SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems"

 ObjHTTP.send (sEnv)

 Application.Wait (Now + TimeValue("0:00:10"))
 MsgBox (ObjHTTP.responseText)

 Set ObjHTTP = Nothing
 Set xmlDoc = Nothing 

Try running GetListItems and you will see that a lookup stores its value in an encoded format of ID in Source List;#Value in Source List. For example, ows_MyLookup="13;#MyValue". You will need to pass a value in this format when calling UpdateListItems.