Convert XML file to String variable in VBA

By | February 12, 2018

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?


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 


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

Leave a Reply

Your email address will not be published. Required fields are marked *