Retrieving Data from Website through Access VBA

Question

I am attempting to download an excel file from a specific website to then be passed to my Access Database. I would like to automate this task with VB in Access 2016 and attach it to an On Open event. To be more detailed, I need to 1) Open a specific website 2) Enter text in a field 3) Select "Apply" button 4) Select dropdown 5) Select option from dropdown and 6) save downloaded file with a specific name. I've seen some examples of how to do something similar to this in excel, but have not found anything for Access. I assume since they are both written in visual basic, they should have the same capabilities. Is this possible in Access?

Answer 1

For some reason the Submit button does nor react anymore. What is wrong?

Imb.

PS. It works with the above simple text, but not with my "full" reply.

I found the problem: the Submit button did not function when I included a quote to the original text.

Hi JBFresh,

I use the function URLDownloadToFile to download almost any internet page that I am interested in. The downloaded HTML-file needs some after-processing to extract the data that you are looking for.

This process can be automated completely, but in most cases this data is displayed first in a (dynamical) form in Access for further processing or checking. An "Apply" button finally is used to store the accepted data.

The declaration of URLDownloadToFile can be placed in a general module:

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

Imb.

Answer 2

While I agree with Tom and Daniel ("trying it out" is the best discovery method ever tried), I'm curious about the site and the file you are retrieving. Is this a publicly accessible website that any of us could visit? Is it part of your internal infrastructure? Is this downloaded Excel file the only way to get the data? I am pretty sure you can do this with VBA, but maybe there's a more direct way to obtain the data.

Answer 3

Most probably the Excel code will work, maybe need some minor modifications, but what you are talking about is application independent so the bulk of the code you found should run just fine.  As Tom stated, please post what you found.

Answer 4

Post the link to the Excel solution, and we can check if it would work in Access.

Of course you could check it yourself.