donload table from Web site.

I am running VBA in MS Access 2010.


I got error  Run-time error  ‘3274’
External table is not in the expected format????

I am using this procedure to download table from site.

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
    Write #1, tableChunk
    Close #1
   
    ' Import the file to a table
    DoCmd.TransferText acImportHTML, , "T_SQLTYPES", tempFile, True << error line

    ' Delete the temp file
    Kill tempFile
 End Sub
TarasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
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

Open in new window

Regards
0
TarasAuthor Commented:
I tryed it, got the same error.
0
Rgonzo1971Commented:
could you send a dummy of the temp file
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
TarasAuthor Commented:
I can find and open tempTable.html and I can see table and data in my browser. It looks ok.
0
TarasAuthor Commented:
still can not get around this error
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
TarasAuthor Commented:
How you use builtin ribbon command to do this. Can you explain it in more detail I am not surre what it means.
0
TarasAuthor Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
TarasAuthor Commented:
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.
0
TarasAuthor Commented:
what referrences you have checked?
0
TarasAuthor Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:

Tabletable 2
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
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
TarasAuthor Commented:
Hi Scott I sent you message.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
TarasAuthor Commented:
Here is my access code and html file saved as txt file.
MS_Code_File.txt
TempTable.txt
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
TarasAuthor Commented:
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???
0
TarasAuthor Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
TarasAuthor Commented:
Scott, I tried it got the same error. It must be something relating to your suggestion of embedded tables.?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As mentioned previously, you're going to have to work with the parsing code you originally posted to get the "import" file to the point where it will import. This may mean you have to go down 2 levels of tables, or 10 levels, or whatever ... we've never seen the raw file, so we don't have any way to advise you of that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TarasAuthor Commented:
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")
0
TarasAuthor Commented:
Thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.