Web query - how to ignore "<" character in a table?

[ERT123]

Hi,

I have a VBA program that pulls messages out of another system (that posts messages on our intranet within HTML code) and processes them further in an Excel program. We have used this for several months with everything working great, until we figured out that Excel was ignoring text within < > characters.


The data is stored in the HTML code within the PRE and XMP tags. Excel is obviously not respecting the XMP tag any more.
For example:
The data part of the html code goes in as:

User text message
blah....
blah...
but someone eventually uses the < symbol
and everything up to there > symbol ends up getting ignored

But ends up in the Excel spreadsheet as:
User text message
blah....
blah...
but some one eventually uses the symbol ends up getting ignored

Is there any way to have VBA stop looking for tags? I know that the users could input as "&lt ;" but this is not really practical with this group.

Here is the code I am using:

With Worksheets(SourceN).QueryTables.Add(Connection:=IPfull, Destination:=Worksheets(SourceN).Range("A1")) 
        .Name = PrgName & "-RAW"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = 3
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = False
        .WebSingleBlockTextImport = False 
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
  End With

Any ideas would be appreciated.

ERT123




</PRE>

 

[ERT123]

Well, I figured out a work around.

Instead of:
QueryTables.Add(Connection:="URL;http://10. .....

I pulled the html in as a text file:
QueryTables.Add(Connection:="TEXT;http://10. .....


Since my text is formatted in a PRE block, it was easy to parse out.

Please let me know if anyone has a better idea.

ERT123

 

[Firefly2012]

Hi

Never used this myself, so may be going off in the wrong direction, but in your original code try setting:

.WebPreFormattedTextToColumns = True

 

[ERT123]

Firefly2012,

Yes, I thought so too. Especially within the structure of:

  <PRE>
  <XMP> 
  text block
  </XMP>
  </PRE>

I would have thought that this and/or several other settings would have caused the < symbol to be treated as raw text, especially if the text did not form a valid HMTL tag. But my testing shows this is really impossible or obscure to turn off.


ERT123