Importing HTML table results into a table

[Harris@Z]

Hi all,

I hope that someone can help this novice programmer with vba to import the following table.
The data arrives in an email table, and we copy and paste into a text box.
The code needs to extract the data and import into an Access table.
I appreciate that the table will have 5 fields which reflect the 5 fields to be imported.
However I am not sure how to loop through and import.

Importantly, the number of samples (rows) can vary, i.e., 1 to 10, 14, or more

Any help will be greatly appreciated, thank you.

 

[MsAccessNL]

Interesting challenge, do you have an example of the email or html text? Otherwise you can send the email to info@msaccess.nl

 

[Harris@Z]

Thanks for your potential help! I have forwarded the example to your email address.
Thank you
Harris

 

[Pat Hartman]

Thanks for your potential help! I have forwarded the example to your email address.

OK, but none of the rest of us can help you.

 

[cheekybuddha]

How will you handle the '#' column?

Is it important to store this?

I ask, since when you next come to perform an import, you will likely already have the number in your table.

My guess is that it's meaningless except in the case of a single html table.

 

[Gasman]

I am thinking you grab the .HTMLBody into a variable, then parse it for the tags <td> and </td>
Might have to put into a memo field in a table if a lot of data?
Lots of trial and error.

 

[Harris@Z]

Thanks, the '#' column is not necessary to capture
Using .HTMLBody is also an interesting idea!
You are correct, lots of trial and error! At the moment, more like error

 

[Gasman]

Thanks, the '#' column is not necessary to capture
Using .HTMLBody is also an interesting idea!
You are correct, lots of trial and error! At the moment, more like error

If you grab the html body and paste into notepad, save as a htm file, anything so as to be able to view the html, you should be able to see a pattern.

FWIW I used the reverse to send emails a good few years back.

See if you can see how in this code. Sorry it is so lengthy, but it is the whole sub.

 

[cheekybuddha]

The data arrives in an email table, and we copy and paste into a text box.

Probably easier to use MSHTML.HTMLDocument which you will be able to iterate more easily.

If you have just the simple table HTML as a string you can parse it easily like this:

Function ParseHTMLTbl(strHTML As String) As Boolean

  Dim doc As Object, rows As Object, row As Object, fld As Object, iRow As Integer, iFld As Integer
 
  Set doc = CreateObject("htmlfile")
  With doc
    .body.innerHTML = strHTML
    Set rows = .getElementsByTagName("tr")
    For Each row In rows
      For iFld = 0 To row.Children.Length - 1
        Set fld = row.Children(iFld)
        Debug.Print iRow, fld.innerText
      Next iFld
      iRow = iRow + 1
    Next row
  End With
  Set fld = Nothing
  Set rows = Nothing
  Set doc = Nothing
  ParseHTMLTbl = Err = 0
 
End Function

You can test the above function easily from the Immediate Window (Ctrl+G) using:

?ParseHTMLTbl("<table><thead><tr><td>Fld1</td><td>Fld2</td></tr></thead><tbody><tr><td>Hello</td><td>World</td></tr></tbody></table>")

 

[Harris@Z]

Thanks very much for the potential solutions!
And particularly for the code, much appreciated.
I would not have considered these possibilities at all.

 

[CJ_London]

If you grab the html body and paste into notepad, save as a htm file,

Not at my computer at the moment but If you’ve done that, pretty sure access provides a means to import an html file under the external data tab

 

[Gasman]

Not at my computer at the moment but If you’ve done that, pretty sure access provides a means to import an html file under the external data tab

Yes, a HTML Document is one of them.

 

[MsAccessNL]

You can drag en drop your email into the form. You need to use outlook for this. The email I received only contained the table, other text can "confuse" the code.

 

[KitaYama]

You can drag en drop your email into the form. You need to use outlook for this. The email I received only contained the table, other text can "confuse" the code.

@MsAccessNL
A little off topic, but I hope you can help.
It seems that you've used a ListView ActiveX control in your form. If I want to add another ListView ActiveX control, I can't find it in my ActiveX controls. Is it because of the versions of Access? How can I add a new ListView?

thanks
Office 365 latest version.

 

[arnelgp]

you can also use Excel's Power Query to import the data and save it to a worksheet.
later you use Access to import the excel sheet.

 

[DickyP]

A lot of useful ideas and advice but no one appears to have asked the obvious question.

The data is sent in a tabular format so almost certainly originates in a tabular format. Is there any good reason why the originator can't be asked to forward it in a suitable format for import? CSV, Excel etc. Can still be by e-mail as a suitable attachment.

 

[MsAccessNL]

@MsAccessNL
A little off topic, but I hope you can help.
It seems that you've used a ListView ActiveX control in your form. If I want to add another ListView ActiveX control, I can't find it in my ActiveX controls. Is it because of the versions of Access? How can I add a new ListView?

thanks
Office 365 latest version.

I work with an older version of access. I think it's Microsoft Listview 6.0 ,you can also use a commandbutton to start the import of the selected email. Did you try to copy/paste the listview control?

 

[KitaYama]

Did you try to copy/paste the listview control?

Yes, I can copy/paste and reuse your listview control, But I noticed the problem a while ago.
While I was waiting for your reply, a research showed that I have to download and register MSCOMCTL.OCX .

Thanks for your time.

 

[MsAccessNL]

I work with an older version of access. I think it's Microsoft Listview 6.0 ,you can also use a commandbutton to start the import of the selected email. Did you try to copy/paste the listview control?

Did the table import work?

 

[Gasman]

A lot of useful ideas and advice but no one appears to have asked the obvious question.

The data is sent in a tabular format so almost certainly originates in a tabular format. Is there any good reason why the originator can't be asked to forward it in a suitable format for import? CSV, Excel etc. Can still be by e-mail as a suitable attachment.

You have obviously not worked with suppliers who do not want to do any extra work on their end to assist you.