Run-time error '-2147024773 (8007007b)': The filename, directory name, or volume label syntax is incorrect

smann23

New Member
Joined
Jan 9, 2018
Messages
1
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top