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 http://wqweto.wordpress.com/2011/07/12/vb6-using-wininet-to-post-binary-file/