http download, and import

[smig]

is it possible to create a button that will download an Excel file from the WEB (http address) and import it into Access ?

thanks,
Tal

 

[MarkK]

I don't see any reason why this wouldn't be possible. I think it might not ever be really really reliable, but that's another matter.
Cheers,

 

[smig]

Thanks,
and How do I do it ?
how do I dl the file, and how do I make sure it was downloaded before trying to import ?

 

[LPurvis]

With a function such as fHTTPDownload below.
If it fails (returns False) the download didn't work - you can explicitly check for the file afterwards too of course with a simple Dir function check. (See the VBA Help for more on that if need be).

Importing could also use a built in method (note the could ;-). For example
DoCmd.TransferSpreadsheet
(Also in the Help file).

Function fHTTPDownload(strTarget As String, strSaveAs As String, _
                            Optional strUN As String, Optional strPW As String) As Boolean
On Error GoTo errHere
    Dim xmlHTTP As Object
    Dim strRespText As String
 
    fHTTPDownload = True
 
    Set xmlHTTP = CreateObject("Microsoft.XMLHTTP")
 
    With xmlHTTP
        .Open "GET", strTarget, False, strUN, strPW
        .setRequestHeader "cache-control", "no-cache,must revalidate"
        .Send
        SaveFile strSaveAs, .responseBody
    End With
 
exitHere:
    Set xmlHTTP = Nothing
    Exit Function
 
errHere:
    fHTTPDownload = False
    'Your error handling here
    Resume exitHere
End Function
 
Private Sub SaveFile(strFilePath, bytArray)
On Error GoTo HandleErr
 
    Dim objStream  As Object 'New ADODB.Stream
    Set objStream = CreateObject("ADODB.Stream")
    
    With objStream
        .Type = 1 'adTypeBinary
        .Open
        .Write bytArray
        .SaveToFile strFilePath, 2 'adSaveCreateOverWrite
    End With
 

exitHere:
    Exit Sub
HandleErr:
    'Error Message goes here
    Resume exitHere
End Sub

 

[smig]

thank you
will the function hold all other jobs till the file was downloaded ?

 

[LPurvis]

Yes, exactly. VBA is single threaded and to perform asyncrhonous downloads in the XML OM is more hit and miss in my experience (though I've played with it too).

 

[smig]

Thanks,
I prefer it to work as it is, so I can start the import routine after the file DL end.
I will let the user know that this task can take some time.

 

[smig]

it work

what the optional variables are for ?