donload table from Web site.

Hi,

pls try

Sub SaveSqlTypesTable()
    Dim rawHtml As String, tableChunk As String, tempFile As String
     Dim tmpAt As Long, tableStart As Long, tableEnd As Long
     
    rawHtml = GetPage("http://www.myaaabbbccc.table.aspx")
     
     ' Search forward until we're just before the table we want
     tmpAt = InStr(1, rawHtml, "Price is Low")
     tmpAt = InStr(tmpAt, rawHtml, "</table")
     
     ' Get the index of the start of the opening <table> tag
     tableStart = InStr(tmpAt, rawHtml, "<table")
     
     ' Get the index of the end of the closing </table> tag
     tmpAt = InStr(tableStart, rawHtml, "</table")
     tableEnd = InStr(tmpAt, rawHtml, ">")
     
     ' Extract the table
     tableChunk = Mid(rawHtml, tableStart, tableEnd - tableStart + 1)
     
     ' Use native VBA file I/O
     tempFile = "D:\tempTable.html"
     Open tempFile For Output As #1
     Print #1, tableChunk
     Close #1
     
     ' Import the file to a table
     DoCmd.TransferText acImportHTML, , "T_SQLTYPES", tempFile, True
     ' Delete the temp file
     Kill tempFile
  End Sub

Select allOpen in new window

Regards

I tryed it, got the same error.

could you send a dummy of the temp file

I'd also be curious whether your "tablechunk" variable contains a valid HTML chunk. I'm not sure what Access expects for the acImportHTML portion, but it might be worth a shot to save that "tablechunk" data as a file, and then try to open it with your browser. If your browser won't open it - or munges it when opening - then you may have malformed XML.

Is it possible you have sub-tables? You're getting the first <table> string, and then the next </table> string as the end - would it be possible that the first </table> you run across is NOT the right one?

I can find and open tempTable.html and I can see table and data in my browser. It looks ok.

still can not get around this error

I have no idea, but if I had to bet on this, I'd say there was some sort of incompatibility with the incoming format. If you get the same error when running an Excel import, for example, it's almost always because the Excel file is malformed in some way. You can still open it in Excel, but for some reason you're not able to do so with Access.

Can you use the builtin ribbon commands to do this? If you cannot, then there is definitely something wrong with your file.

How you use builtin ribbon command to do this. Can you explain it in more detail I am not surre what it means.

When I tried to open html file from access through External Data>> More>>HTML document
I got message : “File ‘D:\tempTable.html’ does not contain data , or the file is to large.
Table is not big 10 columns and 20 -30 rows.
I can open file in IE and see table without problem.

Not sure is problem extension of file it is .html not xml.

Next thing I would try is to export one of your tables as HTML, and compare the structure of that export with the one you have. You may find that your HTML structure of your downloaded document is incorrect.

For example, I created a simple table with a couple of fields, and added a few rows. I then exported the table at HTML, and then Imported it using the ribbon methods. I was able to successfully import the table, after walking through the wizard.

You may have to create a spec file, much like you sometimes have to do with Excel, in order to import the HTML file. The "spec" file is a document that tells Access how the columns/rows are formatted, the datatype, etc. You create the spec file by using the wizard in Access, and then saving that spec file during the last step of the process. After that, you can use the spec file when importing via the ribbon, or via VBA.

I've attached the file here for you to test. If you can import that file into your database, then the fault lies with your HTML format.
Table1.html

I got the same error with your file.

I got message : “File ‘D:\Table1.html’ does not contain data , or the file is to large.

what referrences you have checked?

When I open your file Table1.html I can see ony tags and code as in IE code view. When I open my I can see table with data.

The export was from a brand new 2013 database, with only default references.

I imported into the same database, and also a different database using Access 2013. I also imported into a new database using Access 2010. In every case, the HTML file imported to a new table. I did have to go through the import wizard, and make a few selections. This is where you could create the spec, and then use that spec when automating your import.

When I open your file Table1.html I can see ony tags and code as in IE code view. When I open my I can see table with data.

When I open the file in Chrome or Firefox, I see a table of data. The first image is the one without formatting, the second with formatting:


My file does contain data, but it also contains formatting and such. For example, in the lines below I've highlighted the data:

<TD BORDERCOLOR=#eeece1  ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>1</FONT></TD>
<TD BORDERCOLOR=#eeece1 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>eeee</FONT></TD>
<TD BORDERCOLOR=#eeece1  ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>8</FONT></TD>

FWIW, I've attached the same exported HTML file without formatting - but as I mentioned, I was able to import the one with formatting as well.

Could you possibly attach your HTML file here, after obfuscating any sensitive data of course? I'd be glad to try and import it to a database.
Table11.html

To be sure you're getting the correct file, I've created a Zip that includes the two HTML files, and attached it here. The EE system used to have some issues with attached HTML files.
AccessHTMLTables.zip

Hi Scott I sent you message.

Got your message, and replied. Please include attachments here, so all Experts can see them. As stated, there's really nothing in them that is sensitive or proprietary.

Here is my access code and html file saved as txt file.
MS_Code_File.txt
TempTable.txt

The trouble with the file is that you've got embedded tables, and Access cannot handle those.

If you review the file, you'll notice there are two "<table" headers there (and only a single </table> end tag). If you properly form the HTML, and include a single table, then Access can import it.

The attached file shows a portion of that.

Ultimately, you'll have to parse your downloaded file to be compliant with the Access import process. In this case, I was able to strip off everything down above the actual <table> tab (the one that actually contained the data), and Access would import it.

If you don't think the format will change (and I wouldn't bet on that, given the nature of web-based data), then you can simply call this line twice, using the "tableStart" variable as shown in the second call:

 tableStart = InStr(tmpAt, rawHtml, "<table")
 tableStart = InStr(tableStart, rawHtml, "<table")

This locates the first call to <table (which is the "top level" table), and then calls the line again, with the starting point specified as the location of that first "<table" found earlier. This should (hopefully) get you to the location of that embedded table that contains your data. If not, you'll have to play around with the code until you have something similar to the attached file.

After doing that, you then need to import the file one time with the wizard, and walk through the wizard until the import is complete, and SAVE THE SPECIFICATION. After doing that, you can use the name of that spec when importing via VBA.

I understand what you saying about embedded  table .But  to find where  first table ends and  second table(table of my interest) begin this part of code is present:
 “All Pricing”
 This part finds point close to where first table is finishing and another after that will start:
' Search forward until we're just before the table we want
    tmpAt = InStr(1, rawHtml, "All Pricing")
    tmpAt = InStr(tmpAt, rawHtml, "</table")

After that is this part:
' Get the index of the start of the opening <table> tag
    tableStart = InStr(tmpAt, rawHtml, "<table")

Why we need to  have two times.
    tableStart = InStr(tmpAt, rawHtml, "<table")
    tableStart = InStr(tmpAt, rawHtml, "<table")
Does it mean that upper part :
“Search forward until we're just before the table we want “ is not doing what is supposed to do???

Sorry I see now what you mean I made mistake, (second--- tableStart = InStr(tableStart, rawHtml, "<table") is not same as first one.
Let me try you suggestion.

Right - the second statement finds the "next" table start. After that, you'll also need to find the </table that's associated, so be sure to run the code that locates the "next" </table tag after you find the correct <table tag.

Scott, I tried it got the same error. It must be something relating to your suggestion of embedded tables.?

Hi Scott it is ok you suggestion I just adjusted it
in this part.

     
    ' Get the index of the start of the opening <table> tag
    tableStart = InStr(tmpAt, rawHtml, "<table")
    tableStart = InStr(tableStart + 1, rawHtml, "<table")

Thanks a lot.