I've often resented the fact that Excel makes it much easier to import data from the Web than Access does. For the purposes of this article, I've hosted a sample Web page here. As figure 1, at the bottom of this page, illustrates, it's nothing fancy, but it might be the sort of table you'd want to import into your application. If you tried to importing this page you might try selecting File > Get External Data > Import, you'd find one of the choices for import is HTML Document. But try putting in the URL above as the file -- you get an error message. It's only if you download the HTML file that you can use the Import HTML wizard (shown in figure 2)
If that's good enough for you, the following code will allow you to download an HTML page so that you can use the Import HTML Wizard:
Sub SaveWebpage(URL As String)
Dim objWeb As Object
Dim intFile As Integer
Dim strFile As String
Dim strHTML As String
Set objWeb = CreateObject("Microsoft.XMLHTTP")
objWeb.Open "GET", URL, False
objWeb.send
strHTML = objWeb.responseText
strFile = CurrentProject.path & "\Saved.html"
intFile = FreeFile()
Open strFile For Output As #intFile
Print #intFile, strHTML
Close #intFile
End Sub
But why must it be that difficult? In this article I'll show you how to import tabular data from a page over the Internet. It's not my intention to build a complete wizard to allow you to import data from any web page. However, I'll show you enough that you'll be able to apply it to your specific situation.
Figure 1: A sample webpage to import -- The data of interest is presented in a table.
Figure 2: The Access Import HTML wizard -- Once the file is accessible, Access does a reasonably good job of importing the data, although you do have to provide a lot of information.