VBA Help - Extracting text from massive string

[hellfire45]

I am being tasked with extracting various strings from a massive XML feed and putting the extracted text into an excel template.

The challenges:

• This XML feed is 1.5 million characters long. I'm not sure if VBA can handle this. I think the string variable has a cap? I can open the XML file with notepad and it pastes fairly cleanly into excel.

• Having a function that can locate a piece of text in this string and extract it. For example in the bottom line of code, I would need to locate the text between the start and end tags, therefore, "BRIDGE COUNTY TOLL CALL-IN".

#SKUDesc # Bridge Country Toll Call-in (1)# /SKUDesc#

 

[yky]

If it can be pasted into Excel, you should be able to do it. You work cell by cell, not the entire file at a time.

 

[Rick Rothstein]

• Having a function that can locate a piece of text in this string and extract it. For example in the bottom line of code, I would need to locate the text between the start and end tags, therefore, "BRIDGE COUNTY TOLL CALL-IN".

#SKUDesc # Bridge Country Toll Call-in (1)# /SKUDesc#

I am not clear what you are wanting to do here... can you explain what your ultimate goal is in a little more detail?

 

[hellfire45]

I am not clear what you are wanting to do here... can you explain what your ultimate goal is in a little more detail?

Hi thanks!

I think at this point my problem has changed. Let me explain.

If I use the code below (longer code), it pastes the xml file into excel but it does it as an XML table. I don't think this is what I want.

What looks better and easier for me to use is if I open a new notepad file, and open the XML with notepad (which looks like a 1000 pages of just text), then if i SELECT ALL and paste into excel in column A, it looks nice. It's just about 50,000 rows of text all separated out. From here, can use VBA such as the smaller code below to find and extract when I want.

Is there anyway to get the data out of the XML file in the way I described? If more clarification is needed, I will do my best. Thanks for the time.

Set FindRow = .Range("A:A").Find(What:=str_set, LookIn:=xlValues)

Option Explicit

Sub build_template()
Dim strTargetFile As String
Dim wb As Workbook


     Application.ScreenUpdating = False
     Application.DisplayAlerts = False
     strTargetFile = "C:\Users\jjoseph\Desktop\6100109308_401747811_XML.xml"
     Set wb = Workbooks.OpenXML(Filename:=strTargetFile, LoadOption:=xlXmlLoadOpenXml)
     Application.DisplayAlerts = True


     wb.Sheets(1).UsedRange.Copy target.Range("A1")
     wb.Close False
     Application.ScreenUpdating = True


End Sub

 

[yky]

Is there anyway to get the data out of the XML file in the way I described? If more clarification is needed, I will do my best. Thanks for the time.

Change the file extension from .xml to .txt then open it with Excel, fixed width.