Import help

[scubasteve.biz]

Is it possible to import data from a URL that provides and XML, Tab, or Comma
Delimited feed. I have a vendor that provides a feed via a https: url
address. I'm trying to figure out how I automate the import of the feed.
Preferably XML and Tab Delimited formats. Any direction is appreciated.

Thanks

 

[Douglas J. Steele]

It's possible, but it's not built into Access.

You can use the following code to download the page into a file:

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

or you can use the URLDownloadToFile API function:

Declare Function URLDownloadToFile _
Lib "urlmon" _
Alias "URLDownloadToFileA" ( _
ByVal pCaller As Long, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserved As Long, _
ByVal lpfnCB As Long _
) As Long

Private Function DownloadFile( _
URL As String, _
LocalFilename As String _
) As Boolean
On Error GoTo Err_DownloadFile

Dim lngRetVal As Long

lngRetVal = _
URLDownloadToFile(0, URL, LocalFilename, 0, 0)
DownloadFile = (lngRetVal = 0)

End Function

Once you've done that, you can use TransferText to import the data.

It's actually possible to do it without requiring that you download the file
first, but that's a little too involved to answer in a newsgroup post.