hello,
I am trying to convert lines of data to XML format and save them however I keep getting the error : 'Run-time error '-2147024773 (8007007b)': The filename, directory name, or volume label syntax is incorrect.'
The 'doc.Save sFile' gets highlighted when i click debug. I ma very new to VBA so if anyone could pose a solutuon. I am trying to bring in large amounts of data however only 45 lines come in out of 260. They are random lines of data each time.
This is the code in my VBA:
Sub CreateXML()
sTemplateXML = _
"<?xml version='1.0' encoding='UTF-16'?>" + vbNewLine + _
"<Email>" + vbNewLine + _
" <From>" + vbNewLine + _
" </From>" + vbNewLine + _
" <To>" + vbNewLine + _
" </To>" + vbNewLine + _
" <CC>" + vbNewLine + _
" </CC>" + vbNewLine + _
" <BCC>" + vbNewLine + _
" </BCC>" + vbNewLine + _
" <Subject>" + vbNewLine + _
" </Subject>" + vbNewLine + _
" <Message>" + vbNewLine + _
" </Message>" + vbNewLine + _
"</Email>" + vbNewLine
Set doc = CreateObject("MSXML2.DOMDocument")
doc.async = False
doc.validateOnParse = False
doc.resolveExternals = False
lLastRow = ActiveWorkbook.Worksheets(1).UsedRange.Rows.count
For lRow = 2 To lLastRow
sFile = Cells(lRow, 16).Value
sFrom = Cells(lRow, 9).Value
sTo = Cells(lRow, 12).Value
sCC = Cells(lRow, 13).Value
sBCC = Cells(lRow, 14).Value
sSubject = Cells(lRow, 11).Value
sMessage = Cells(lRow, 15).Value
doc.LoadXML sTemplateXML
doc.getElementsByTagName("From")(0).appendChild doc.createTextNode(sTo)
doc.getElementsByTagName("To")(0).appendChild doc.createTextNode(sTo)
doc.getElementsByTagName("CC")(0).appendChild doc.createTextNode(sCC)
doc.getElementsByTagName("BCC")(0).appendChild doc.createTextNode(sBCC)
doc.getElementsByTagName("Subject")(0).appendChild doc.createTextNode(sSubject)
doc.getElementsByTagName("Message")(0).appendChild doc.createTextNode(sMessage)
doc.Save sFile
Next
End Sub
I am trying to convert lines of data to XML format and save them however I keep getting the error : 'Run-time error '-2147024773 (8007007b)': The filename, directory name, or volume label syntax is incorrect.'
The 'doc.Save sFile' gets highlighted when i click debug. I ma very new to VBA so if anyone could pose a solutuon. I am trying to bring in large amounts of data however only 45 lines come in out of 260. They are random lines of data each time.
This is the code in my VBA:
Sub CreateXML()
sTemplateXML = _
"<?xml version='1.0' encoding='UTF-16'?>" + vbNewLine + _
"<Email>" + vbNewLine + _
" <From>" + vbNewLine + _
" </From>" + vbNewLine + _
" <To>" + vbNewLine + _
" </To>" + vbNewLine + _
" <CC>" + vbNewLine + _
" </CC>" + vbNewLine + _
" <BCC>" + vbNewLine + _
" </BCC>" + vbNewLine + _
" <Subject>" + vbNewLine + _
" </Subject>" + vbNewLine + _
" <Message>" + vbNewLine + _
" </Message>" + vbNewLine + _
"</Email>" + vbNewLine
Set doc = CreateObject("MSXML2.DOMDocument")
doc.async = False
doc.validateOnParse = False
doc.resolveExternals = False
lLastRow = ActiveWorkbook.Worksheets(1).UsedRange.Rows.count
For lRow = 2 To lLastRow
sFile = Cells(lRow, 16).Value
sFrom = Cells(lRow, 9).Value
sTo = Cells(lRow, 12).Value
sCC = Cells(lRow, 13).Value
sBCC = Cells(lRow, 14).Value
sSubject = Cells(lRow, 11).Value
sMessage = Cells(lRow, 15).Value
doc.LoadXML sTemplateXML
doc.getElementsByTagName("From")(0).appendChild doc.createTextNode(sTo)
doc.getElementsByTagName("To")(0).appendChild doc.createTextNode(sTo)
doc.getElementsByTagName("CC")(0).appendChild doc.createTextNode(sCC)
doc.getElementsByTagName("BCC")(0).appendChild doc.createTextNode(sBCC)
doc.getElementsByTagName("Subject")(0).appendChild doc.createTextNode(sSubject)
doc.getElementsByTagName("Message")(0).appendChild doc.createTextNode(sMessage)
doc.Save sFile
Next
End Sub