trrailguru
New member
- Joined
- Feb 2, 2016
- Messages
- 2
- Reaction score
- 0
- Points
- 0
I've written my first VBA macro to export data from XLS to a named XML file. My initial, simplified tests went fine but the full version errors with the message:
Run-time error '-2147024809 (80070057)':
The parameter is incorrect
and the line 'doc.Save sFile' is highlighted.
Can anyone help?
Run-time error '-2147024809 (80070057)':
The parameter is incorrect
and the line 'doc.Save sFile' is highlighted.
Can anyone help?
Code:
Sub OrdXMLExport()
sTemplateXML = _
"<?xml version='1.0'?>" + vbNewLine + _
"<WEBORDER>" + vbNewLine + _
" <ORDERNO>" + vbNewLine + "</ORDERNO>" + vbNewLine + "<ORDERDATE>" + vbNewLine + "</ORDERDATE>" + vbNewLine + _
" <EMAIL_ADDRESS>" + vbNewLine + "</EMAIL_ADDRESS>" + vbNewLine + "<CUSTOMER_TYPE/>" + vbNewLine + _
" <TOTAL_ORDER_PRICE_INCL_VAT>" + vbNewLine + "</TOTAL_ORDER_PRICE_INCL_VAT>" + vbNewLine + "<TOTAL_ORDER_PRICE_EXCL_VAT>" + vbNewLine + "</TOTAL_ORDER_PRICE_EXCL_VAT>" + vbNewLine + "<TOTAL_ORDER_PRICE_VAT>0.00</TOTAL_ORDER_PRICE_VAT>" + vbNewLine + _
" <TOTAL_GOODS_PRICE_INCL_VAT>" + vbNewLine + "</TOTAL_GOODS_PRICE_INCL_VAT>" + vbNewLine + "<TOTAL_GOODS_PRICE_EXCL_VAT>" + vbNewLine + "</TOTAL_GOODS_PRICE_EXCL_VAT>" + vbNewLine + "<TOTAL_GOODS_PRICE_VAT>0.00</TOTAL_GOODS_PRICE_VAT>" + vbNewLine + _
" <TOTAL_CARRIAGE_CHARGE_INCL_VAT>0.00</TOTAL_CARRIAGE_CHARGE_INCL_VAT>" + vbNewLine + "<TOTAL_CARRIAGE_CHARGE_EXCL_VAT>0.00</TOTAL_CARRIAGE_CHARGE_EXCL_VAT>" + vbNewLine + "<TOTAL_CARRIAGE_CHARGE_VAT>0.00</TOTAL_CARRIAGE_CHARGE_VAT>" + vbNewLine + _
" <TOTAL_GOODS_PRICE>" + vbNewLine + "</TOTAL_GOODS_PRICE>" + vbNewLine + "<TOTAL_GOODS_VAT>" + vbNewLine + "</TOTAL_GOODS_VAT>" + vbNewLine + _
" <CARRIAGE_CHARGE>0.00</CARRIAGE_CHARGE>" + vbNewLine + "<CARRIAGE_CHARGE_VAT>0.00</CARRIAGE_CHARGE_VAT>" + vbNewLine + _
" <NUMBER_OF_GOODS>" + vbNewLine + "</NUMBER_OF_GOODS>" + vbNewLine + "<NUMBER_OF_GOODS_LINES>" + vbNewLine + "</NUMBER_OF_GOODS_LINES>" + vbNewLine + _
" <CUSTOMER_NAME>" + vbNewLine + "</CUSTOMER_NAME>" + vbNewLine + _
" <ADDRESS_LINE_1>" + vbNewLine + "</ADDRESS_LINE_1>" + vbNewLine + "<ADDRESS_LINE_2>" + vbNewLine + "</ADDRESS_LINE_2>" + vbNewLine + "<ADDRESS_LINE_3/>" + vbNewLine + "<ADDRESS_LINE_4>" + vbNewLine + "</ADDRESS_LINE_4>" + vbNewLine + "<ADDRESS_LINE_5>" + vbNewLine + "</ADDRESS_LINE_5>" + vbNewLine + _
" <COUNTRY>" + vbNewLine + "</COUNTRY>" + vbNewLine + "<POSTCODE>" + vbNewLine + "</POSTCODE>" + vbNewLine + "<TELEPHONE/>" + vbNewLine + _
" <INVOICE_CUSTOMER_NAME>" + vbNewLine + "</INVOICE_CUSTOMER_NAME>" + vbNewLine + _
" <INVOICE_ADDRESS_LINE_1>" + vbNewLine + "</INVOICE_ADDRESS_LINE_1>" + vbNewLine + "<INVOICE_ADDRESS_LINE_2>" + vbNewLine + "</INVOICE_ADDRESS_LINE_2>" + vbNewLine + "<INVOICE_ADDRESS_LINE_3/>" + vbNewLine + "<INVOICE_ADDRESS_LINE_4>" + vbNewLine + "</INVOICE_ADDRESS_LINE_4>" + vbNewLine + "<INVOICE_ADDRESS_LINE_5>" + vbNewLine + "</INVOICE_ADDRESS_LINE_5>" + vbNewLine + _
" <INVOICE_COUNTRY>" + vbNewLine + "</INVOICE_COUNTRY>" + vbNewLine + "<INVOICE_POSTCODE>" + vbNewLine + "</INVOICE_POSTCODE>" + vbNewLine + "<INVOICE_TELEPHONE/>" + vbNewLine + _
" <CREDIT_CARD_TYPE/>" + vbNewLine + "<CARD_NO/>" + vbNewLine + "<CARD_NAME/>" + vbNewLine + "<ISSUE_NO/>" + vbNewLine + "<BATCH_NO/>" + vbNewLine + "<SECURITY_NUMBER/>" + vbNewLine + "<START_DATE/>" + vbNewLine + "<EXPIRY_DATE/>" + vbNewLine + _
" <SHIP_CODE/>" + vbNewLine + _
"<ORDERLINE>" + vbNewLine + _
" <ISBN>" + vbNewLine + "</ISBN>" + vbNewLine + "<BOOK_NO>NONE</BOOK_NO>" + vbNewLine + "<TITLE>" + vbNewLine + "</TITLE>" + vbNewLine + "<QTY>" + vbNewLine + "</QTY>" + vbNewLine + _
" <PRICE>" + vbNewLine + "</PRICE>" + vbNewLine + "<PRICE_INCL_VAT>" + vbNewLine + "</PRICE_INCL_VAT>" + vbNewLine + "<PRICE_EXCL_VAT>" + vbNewLine + "</PRICE_EXCL_VAT>" + vbNewLine + "<PRICE_VAT>0.00</PRICE_VAT>" + vbNewLine + "<PRICE_VAT_PERCENTAGE>0.00</PRICE_VAT_PERCENTAGE>" + vbNewLine + _
"</ORDERLINE>" + vbNewLine + _
"</WEBORDER>" + vbNewLine
Set doc = CreateObject("MSXML2.DOMDocument")
doc.async = False
doc.validateOnParse = False
doc.resolveExternals = False
With ActiveWorkbook.Worksheets(1)
lLastRow = .UsedRange.Rows.Count
For lRow = 2 To lLastRow
sORDERNO = .Cells(lRow, 2).Value
sORDERDATE = Format(.Cells(lRow, 27).Value, "DD/MM/YYYY")
sEMAIL_ADDRESS = .Cells(lRow, 4).Value
sTOTAL_ORDER_PRICE_INCL_VAT = Format(.Cells(lRow, 21).Value, "Standard")
sTOTAL_ORDER_PRICE_EXCL_VAT = Format(.Cells(lRow, 21).Value, "Standard")
sTOTAL_GOODS_PRICE_INCL_VAT = Format(.Cells(lRow, 21).Value, "Standard")
sTOTAL_GOODS_PRICE_EXCL_VAT = Format(.Cells(lRow, 21).Value, "Standard")
sTOTAL_GOODS_PRICE = Format(.Cells(lRow, 21).Value, "Standard")
sTOTAL_GOODS_VAT = Format(.Cells(lRow, 21).Value, "Standard")
sNUMBER_OF_GOODS = .Cells(lRow, 16).Value
sNUMBER_OF_GOODS_LINES = .Cells(lRow, 16).Value
sCUSTOMER_NAME = .Cells(lRow, 3).Value
sADDRESS_LINE_1 = .Cells(lRow, 38).Value
sADDRESS_LINE_2 = .Cells(lRow, 39).Value
sADDRESS_LINE_4 = .Cells(lRow, 40).Value
sADDRESS_LINE_5 = .Cells(lRow, 41).Value
sCOUNTRY = .Cells(lRow, 43).Value
sPOSTCODE = .Cells(lRow, 42).Value
sINVOICE_CUSTOMER_NAME = .Cells(lRow, 3).Value
sINVOICE_ADDRESS_LINE_1 = .Cells(lRow, 6).Value
sINVOICE_ADDRESS_LINE_2 = .Cells(lRow, 7).Value
sINVOICE_ADDRESS_LINE_4 = .Cells(lRow, 8).Value
sINVOICE_ADDRESS_LINE_5 = .Cells(lRow, 9).Value
sINVOICE_COUNTRY = .Cells(lRow, 11).Value
sINVOICE_POSTCODE = .Cells(lRow, 10).Value
sISBN = .Cells(lRow, 34).Value
sTITLE = .Cells(lRow, 15).Value
sQTY = .Cells(lRow, 16).Value
sPRICE = Format(.Cells(lRow, 17).Value, "Standard")
sPRICE_INCL_VAT = Format(.Cells(lRow, 17).Value, "Standard")
sPRICE_EXCL_VAT = Format(.Cells(lRow, 17).Value, "Standard")
doc.LoadXML sTemplateXML
doc.getElementsByTagName("ORDERNO")(0).appendChild doc.createTextNode(sORDERNO)
doc.getElementsByTagName("ORDERDATE")(0).appendChild doc.createTextNode(sORDERDATE)
doc.getElementsByTagName("EMAIL_ADDRESS")(0).appendChild doc.createTextNode(sEMAIL_ADDRESS)
doc.getElementsByTagName("TOTAL_ORDER_PRICE_INCL_VAT")(0).appendChild doc.createTextNode(sTOTAL_ORDER_PRICE_INCL_VAT)
doc.getElementsByTagName("TOTAL_ORDER_PRICE_EXCL_VAT")(0).appendChild doc.createTextNode(sTOTAL_ORDER_PRICE_EXCL_VAT)
doc.getElementsByTagName("TOTAL_GOODS_PRICE_INCL_VAT")(0).appendChild doc.createTextNode(sTOTAL_GOODS_PRICE_INCL_VAT)
doc.getElementsByTagName("TOTAL_GOODS_PRICE_EXCL_VAT")(0).appendChild doc.createTextNode(sTOTAL_GOODS_PRICE_EXCL_VAT)
doc.getElementsByTagName("TOTAL_GOODS_PRICE")(0).appendChild doc.createTextNode(sTOTAL_GOODS_PRICE)
doc.getElementsByTagName("TOTAL_GOODS_VAT")(0).appendChild doc.createTextNode(sTOTAL_GOODS_VAT)
doc.getElementsByTagName("NUMBER_OF_GOODS")(0).appendChild doc.createTextNode(sNUMBER_OF_GOODS)
doc.getElementsByTagName("NUMBER_OF_GOODS_LINES")(0).appendChild doc.createTextNode(sNUMBER_OF_GOODS_LINES)
doc.getElementsByTagName("CUSTOMER_NAME")(0).appendChild doc.createTextNode(sCUSTOMER_NAME)
doc.getElementsByTagName("ADDRESS_LINE_1")(0).appendChild doc.createTextNode(sADDRESS_LINE_1)
doc.getElementsByTagName("ADDRESS_LINE_2")(0).appendChild doc.createTextNode(sADDRESS_LINE_2)
doc.getElementsByTagName("ADDRESS_LINE_4")(0).appendChild doc.createTextNode(sADDRESS_LINE_4)
doc.getElementsByTagName("ADDRESS_LINE_5")(0).appendChild doc.createTextNode(sADDRESS_LINE_5)
doc.getElementsByTagName("COUNTRY")(0).appendChild doc.createTextNode(sCOUNTRY)
doc.getElementsByTagName("POSTCODE")(0).appendChild doc.createTextNode(sPOSTCODE)
doc.getElementsByTagName("INVOICE_CUSTOMER_NAME")(0).appendChild doc.createTextNode(sINVOICE_CUSTOMER_NAME)
doc.getElementsByTagName("INVOICE_ADDRESS_LINE_1")(0).appendChild doc.createTextNode(sINVOICE_ADDRESS_LINE_1)
doc.getElementsByTagName("INVOICE_ADDRESS_LINE_2")(0).appendChild doc.createTextNode(sINVOICE_ADDRESS_LINE_2)
doc.getElementsByTagName("INVOICE_ADDRESS_LINE_4")(0).appendChild doc.createTextNode(sINVOICE_ADDRESS_LINE_4)
doc.getElementsByTagName("INVOICE_ADDRESS_LINE_5")(0).appendChild doc.createTextNode(sINVOICE_ADDRESS_LINE_5)
doc.getElementsByTagName("INVOICE_COUNTRY")(0).appendChild doc.createTextNode(sINVOICE_COUNTRY)
doc.getElementsByTagName("INVOICE_POSTCODE")(0).appendChild doc.createTextNode(sINVOICE_POSTCODE)
doc.getElementsByTagName("ISBN")(0).appendChild doc.createTextNode(sISBN)
doc.getElementsByTagName("TITLE")(0).appendChild doc.createTextNode(stitle)
doc.getElementsByTagName("QTY")(0).appendChild doc.createTextNode(sQTY)
doc.getElementsByTagName("PRICE")(0).appendChild doc.createTextNode(sPRICE)
doc.getElementsByTagName("PRICE_INCL_VAT")(0).appendChild doc.createTextNode(sPRICE_INCL_VAT)
doc.getElementsByTagName("PRICE_EXCL_VAT")(0).appendChild doc.createTextNode(sPRICE_EXCL_VAT)
doc.Save sFile
Next
End With
End Sub