CODEWITHSUNDEEP
xmlvbaexcel
Barney G
Barney G

Reputation: 109

Convert XML file to String variable in VBA

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?

Upvotes: 4

Views: 9191

Answers (2)

messed-up
messed-up

Reputation: 533

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/

Upvotes: 1

Jean-François Corbett
Jean-François Corbett

Reputation: 38540

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

Upvotes: 5

Related Questions