Convert XML file to String variable in VBA

Question

I'm looking to convert the contents of an XML file into a String variable in excel VBA so that I can search the file for a specific string.

However I don't know how to do the initial conversion from XML file to String variable. All I've been able to do so far is load the XML document, and from there I'm stuck.

Public Function DetermineSpecifiedChange(ByVal vstrInputGBOMPath As String, ByVal vstrInputPDPPath As String)


Dim strPDPString As String
Dim strGBOMString As String

Dim xmlGBOM As New DOMDocument60

Dim xmlPDP As New DOMDocument60

strPDPString = xmlPDP.Load(vstrInputPDPPath)

End Function

So far all this returns is "True", signifying that the file is being loaded.

How would I go about converting the XML file into a string?

You've got a structured XML file... Are you sure an unstructured text search is really what you want to do? — Jean-François Corbett
– Jean-François Corbett, Commented Mar 10, 2014 at 10:28
@Jean-FrançoisCorbett I'm not entirely sure at the moment. What I'm trying to do is search for data in the xml document without knowing the node it's in beforehand, and this is the first thing I wanted to try. Do you have any recommendations? — Barney G
– Barney G, Commented Mar 10, 2014 at 10:30
Sure, look at the questions involving VBA and XPath. Using the `\` selector will allow you to find a node anywhere in the tree. — Jean-François Corbett
– Jean-François Corbett, Commented Mar 10, 2014 at 10:43

Jean-François Corbett · Accepted Answer · 2014-03-10 10:27:59Z

5

Here's a way to do what you ask:

Dim FSO As Object : Set FSO = CreateObject("Scripting.FileSystemObject")    
Dim strXml As String
strXml = FSO.OpenTextFile("C:\myfile.xml").ReadAll

answered Mar 10, 2014 at 10:27

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

messed-up · Accepted Answer · 2015-01-13 14:52:13Z

1

Here is a function that i'm currently using in one of my old application to convert file to string.

Private Function FileToText(fullFilePath as String) as string
Dim nFile           As Integer
Dim baBuffer()      As Byte
Dim sPostData       As String

nFile = FreeFile
Open fullFilePath For Binary Access Read As nFile
If LOF(nFile) > 0 Then
    ReDim baBuffer(0 To LOF(nFile) - 1) As Byte
    Get nFile, , baBuffer
    sPostData = StrConv(baBuffer, vbUnicode)
End If
Close nFile FileToText = sPostData
FileToText = sPostData

This is a part of code from http://wqweto.wordpress.com/2011/07/12/vb6-using-wininet-to-post-binary-file/

answered Jan 13, 2015 at 14:52

messed-up

5335 silver badges12 bronze badges

Collectives™ on Stack Overflow

Convert XML file to String variable in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related