0

I have the xml file below;

<?xml version="1.0" encoding="UTF-8"?>
<response><XXX><id>1</id></XXX></response>

I want to remove the XML header <?xml version="1.0" encoding="UTF-8"?> from the file such that the xml file will look like this;

<response><XXX><id>1</id></XXX></response>

How can this be done using Excel VBA?

The purpose of doing this is so that I can load the header-less string into MSXML2.DOMDocument object through LoadXML() function.

Thank you.

Community
  • 1
  • 1
guagay_wk
  • 26,337
  • 54
  • 186
  • 295
  • 2
    `LoadXML()` has no problem with the XML header. And if the XML comes from a file, there is also `Load()` for that. – GSerg Jun 05 '14 at 06:50
  • What I did was copy the xml content from a file and paste it into a cell on Excel. From there, I use LoadXML(). If the xml header is there, the load fails. After I manually remove the header, the load succeeds. – guagay_wk Jun 05 '14 at 06:51
  • 1
    Then you must have damaged the header while copying. I can't reproduce the problem with MSXML v4, v5, or v6. – GSerg Jun 05 '14 at 07:31
  • Thanks for trying. I will check what mistake I made. Thanks a lot. – guagay_wk Jun 05 '14 at 07:33

1 Answers1

0

You can use the Replace function...

Dim DoubleQuote : DoubleQuote = Chr(34)
Dim strHeader : strHeader = "<?xml version=" & DoubleQuote & "1.0" & DoubleQuote & " encoding=" & DoubleQuote & "UTF-8" & DoubleQuote & "?>"
Result = replace(strHeader,strHeader,"")
If Result = "" then
  MsgBox "Header stripped!"
Else
  MsgBox "Failed to strip header."
End If

Hope that helps!

Hickory420
  • 131
  • 10
  • Thanks. I am confident it will work. Upvoted. But I was thinking of using a method which uses some XML library function so that any form of xml declaration can be removed. – guagay_wk Jun 05 '14 at 07:05