Linking/importing to a table http://

[Guest]

Hello,
I have a excel file that in on an http://east.com/Census/Master.xls but when
I try to link or import I get... just an FYI I have VPN

The wizard is unable to access the file
'http://east.com/Census/Master.xls'. Please check that the file exists and
is in the correct format.

Can anyone help?
Thanks,
Lime

 

[Douglas J. Steele]

Access doesn't recognize either http nor ftp protocols, so you cannot link
to the file that way.

The following code should be able to download the file for you, though, so
that you can link to it normally:

Private 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

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_DownloadFile:
Exit Function

Err_DownloadFile:
MsgBox Err.Number & ": " & Err.Description & vbCrLf & _
"Occurred in DownloadFile"
Resume End_DownloadFile

End Function

You pass the URL and the full path to the location where you want to the
file stored, and the function returns True if successful or False otherwise.

 

[Guest]

Thanks...but I do not understand... where do I paste the URL?

 

[Douglas J. Steele]

You call that function, giving it the URL and the name of the file you want
to save it as.

Something like:

If DownloadFile("http://east.com/Census/Master.xls", "C:\Folder\Master.xls")
= True
' the file has been successfully download:
' you can now use TransferSpreadsheet to import form C:\Folder\Master.xls
Else
MsgBox "Something went wrong downloading the file."
End If

 

[Guest]

If downloadfile ??? I don't get it I do not see that statement in your code?
I really new to this stuff. I do understand calling the function, but the
code has got me all goofed up??

Thanks for your help
Lime

 

[Douglas J. Steele]

I gave you code for the function DownloadFile. You have to take all of the
code I gave you in that post, copy it into a new module (not a class module,
nor a module associated with a form), and make sure you don't name the
module DownloadFile when you save it (modules cannot have the same name as
routines contained in modules)