How to link HTML document with several tables to MS Access database

Question

My purpose is to link *.html document to MS Access database and export it to *.XLS file. The problem is my *.html file contains more than 50 similar tables.

If I'm using "Link HTML Wizard" it looks like

Here I can choose the particular table from html document which I want to link. But each time I have different html files with different amount of tables form 50 to 100. The structure of tables is the same. And I'm not sure that the best way is to link 100 tables to database by manually. Also, I can see the path of my linked table at "Linked Table Manager":

I wrote some VBA code to connect each of table to database:

Set MyDB = CurrentDb()
i = 0
For i = 1 To 100

Set MyTable = MyDB.TableDefs("ZVPL")

MyTable.Connect = ";Database=" & MYc_Path_HTML & "\" & "ZVPL.htm" & "\" & "Report output" & i
MyTable.RefreshLink
Call ExportToXLS("ZVPL") 'function that allows to export my table to XLS
i = i+1
Next

But it doesn't work. The error is "Could not find file ...\Report output1". Also I've checked, the path is correct. What is the best way to extract all the tables from *.html document?

Answer 1

Solve the problem by adding connection string like:

Set MyDB = CurrentDb()

i = 0
For i = 1 To 100
  Set MyTable = MyDB.CreateTableDef("ZVPL")
  MyTable.SourceTableName = "Report output" & i
  MyTable.Connect = "HTML Import;DSN=ZVPL Link Specification1;HDR=NO;IMEX=3;CharacterSet=65001;Database=" & MYc_Path_ZVPLHTM & "ZVPL.htm"
  MyDB.TableDefs.Append MyTable
  MyDB.TableDefs.Refresh
  Call ExportToXLS("ZVPL") 'Export to Excel
  DoCmd.SetWarnings False
  MyDB.Execute "DROP TABLE " & "ZVPL"
  DoCmd.SetWarnings True
Next