Html <tr> that must go away!

[lonesoac0]

Hello all,

I have a report that is generated in HTML and I must get rid of the first <tr></tr>. I have discussed things with the vendor and that first row is hard coded and will not come out through the reporting program. As a result, I would like a macro that removes the first row. Below is the general gist what I want gone. I have reviewed other reports generated and they have the same 0, 137, 108, 214, and 176 values. The line number values are 90 to 96 consistently across reports too.

<table cellpadding="0" cellspacing="0" border="0" style="border-width:0px;empty-cells:show;width:635px;height:44px;">
    <tr>
        <td style="width:0px;height:0px;"></td>
        <td style="height:0px;width:137px;"></td>
        <td style="height:0px;width:108px;"></td>
        <td style="height:0px;width:214px;"></td>
        <td style="height:0px;width:176px;"></td>
    </tr>
    <tr style="vertical-align:top;">
        <td style="width:0px;height:22px;"></td>
        <td class="csD4A184AD" style="width:133px;height:22px;text-align:left;vertical-align:top;"><nobr style="line-height:1.2">TicketsClosedYesterday</nobr></td>
        <td class="csD4A184AD" style="width:104px;height:22px;text-align:left;vertical-align:top;"><nobr style="line-height:1.2">TicketsOpenedOn</nobr></td>
        <td class="csD4A184AD" style="width:210px;height:22px;text-align:left;vertical-align:top;"><nobr style="line-height:1.2">TicketsClosedYesterdaySLAViolations</nobr></td>
        <td class="csD4A184AD" style="width:172px;height:22px;text-align:left;vertical-align:top;"><nobr style="line-height:1.2">Dates</nobr></td>
    </tr>
    <tr style="vertical-align:top;">
        <td style="width:0px;height:22px;"></td>
        <td class="csD4A184AD" style="width:133px;height:22px;text-align:left;vertical-align:top;"><nobr style="line-height:1.2">646</nobr></td>
        <td class="csD4A184AD" style="width:104px;height:22px;text-align:left;vertical-align:top;"><nobr style="line-height:1.2">606</nobr></td>
        <td class="csD4A184AD" style="width:210px;height:22px;text-align:left;vertical-align:top;"><nobr style="line-height:1.2">141</nobr></td>
        <td class="csD4A184AD" style="width:172px;height:22px;text-align:left;vertical-align:top;"><nobr style="line-height:1.2">9/13/2016&nbsp;12:00:00&nbsp;AM</nobr></td>
    </tr>
</table>

[CJ_London]

you refer to removing the first row, the first two <tr> and various numbers I can see in the first few rows and then refer to a whole bunch of code as the general gist of what you want gone.

Can you be more specific - which is it?

[lonesoac0]

I only want the following gone.

<tr>
<td style="width:0px;height:0px;"></td>
<td style="height:0px;width:137px;"></td>
<td style="height:0px;width:108px;"></td>
<td style="height:0px;width:214px;"></td>
<td style="height:0px;width:176px;"></td>
</tr>

[CJ_London]

assuming your data is in a memo field, you should be able to use the replace function

https://nortonsafe.search.ask.com/we...=en_GB&tpr=111

[Micron]

A little confusing for sure. You want ALL of what you posted gone? The first row of what you posted? The first table row? It would be dicey IMHO to rely on doing anything with the values you posted since (perhaps) other table cells that you want to keep could also become one of those values in the future, or it could be real data.
Try this in a standard module to remove the first table row. You'll need to feed the function the html string and call it from your form (or whatever). You might have a variable there, such as strNewHtml, so you'd call it like:

strNewHtml = ParseTheHtml (string variable holding the original html goes here)

Function ParseTheHtml(strHtml1 As String) As String
Dim strHtml2 As String, strTarget As String
Dim lngPos1 As Long, lngPos2 As Long

lngPos1 = InStr(strHtml1, "<tr>")
lngPos2 = InStr(strHtml1, "</tr>")
strTarget = Mid(strHtml1, lngPos1, lngPos2 - lngPos1 + 5)
strHtml2 = Replace(strHtml1, strTarget, "", 1)
'Debug.Print strHtml2
ParseTheHtml = strHtml2

End Function

EDIT:
Took long enough to write this that I didn't see the new posts until I finished.

[CJ_London]

an alternative to Micron's suggestion


newHTMLstr=left(oldHTMLstr,instr("<tr>")-1) & mid(oldHTMLstr,instr("<\tr>")+6)

[ItsMe]

How is the report being generated? Is Access exporting the report as HTML?

[lonesoac0]

Sorry for the confusion. A HTML report is being generated by a system called xtraction (http://www.xtractionsolutions.com/). I have attached a few HTML files that xtraction generates. I am trying to import the data into an access database. I have attached what is going on when I try to import the html data. It seems to be seeing one row that I do not see in my report designer by xtraction.

[ItsMe]

I am not familiar with that wizard. It looks similar to the older Import Wizard. Is there an option to Go Advanced? For instance, when importing a text file you have an option to exclude columns.

Having said that, the wizard may be misinterpreting the HTML elements and improperly defining them. An alternative would be to use Access to automate Internet Explorer and parse the HTML tables via VBA.

[rpeare]

.htm documents can be opened in a text editor, it should be pretty simple to remove the unwanted portions of your file using filesystemobject commands.

If the formatting of the document is always the same the section you want to remove immediately follows the

<table cell padding...

line, then all you'd have to do is search for the first instance tr after that heading and re-write the file to a new file name excluding the unwanted rows. All that being said I think I'd be more inclined to do what itsme suggested and parse the actual table contents out of the HTML.

[lonesoac0]

The first column is not the problem, because I can skip that in the advanced settings of the import. My issue is the first record. As you will note in the other screenshot below, when I mark the first row of headers the titles are not right. Access Marks them as just fields1,2,3 and 4, but I want them to be labeled: TicketsClosedYesterday, TicketsOpenedOn, TicketsClosedYesterdaySLAViolations, and Dates.

[rpeare]

Did you try either of the suggestions made by Itsme or me?

1. Parsing the HTML with filesystemobject and pulling out the records
2. Use filesystemobject to remove the section of HTML you don't want

[lonesoac0]

This sounds like a winning suggestion. How can I parse the actual table contents out of the HTML with VBA?

[lonesoac0]

You are replying too fast! lol I will try the filesystemobject method.

[rpeare]

Dim fsDim sFileName
Dim fIn
Dim fOut


Set fs = CreateObject("Scripting.filesystemobject")
sFileName = "YesterdaysTicketStatsGeneratedToday - 201609140700.htm"
Set fIn = fs.opentextfile(CurrentProject.Path & "\" & sFileName)
Set fOut = fs.createtextfile(CurrentProject.Path & "\" & Replace(sFileName, ".htm", "_converted.htm"))


Do While fIn.atendofstream <> True
    sline = fIn.readline
    fOut.writeline sline
    If InStr(sline, "<table cellpadd") > 0 Then
        Do Until InStr(sline, "</tr>") > 0
            sline = fIn.readline
        Loop
    End If
Loop

This code removes the section you want assuming the .htm files always follow the same format.

If you want to parse the file and add records independently this is the method you'd use to read the file line by line, you'd just need to add code to recognize the lines that have data and pull them out based on positioning.