Importing data from webpages into Microsoft Access

[Haninger]

Microsoft Excel allows users to import external data from a website by
creating a Microsoft Excel Web Query File. Users can then "Refresh" the data
in the worksheet. How can I do the same thing in Access? Or is this not
possible?

 

[Joe Fallon]

This code may help:

Sub LinkHTML()
Dim dbs As Database
Dim tdfHTML As TableDef
Dim rstSales As Recordset

' Open the Microsoft Access database.
Set dbs = CurrentDb

' Create a TableDef object.
Set tdfHTML = dbs.CreateTableDef("CategoriesHTMLTable")

' Set the connection string to specify the source database type and
' the full path to the file that contains the table you want to link.
tdfHTML.Connect = "HTML Import;" _
& "DATABASE=http://home/pnetsql/categories.html"

' Set the SourceTableName property to the name of the table you want to
access.
tdfHTML.SourceTableName = "Categories"

' Append the TableDef object to the TableDefs collection to create a
link.
dbs.TableDefs.Append tdfHTML

' Create a Recordset object from the linked HTML table.
Set rstSales = dbs.OpenRecordset("CategoriesHTMLTable")
End Sub