Forum Discussion
Linking Microsoft Forms to Microsoft Access
Elhanxyz The Excel files. Are they created daily and downloaded? Where are they stored, physically?
"...MS Forms hosts the Excel output on the website link to the Form..."
Unfortunately, I'm not sure I understand what that involves. What is that "website link"?
Are you getting .xlsx or .csv files?
In theory, at least, if these files are stored where you can import them (or link to them) from your Access accdb on a local computer, the import process can be automated. The key is being able to connect to the location where they are stored. It sounds like you are doing that now, in fact.
Using VBA, if you can get to these Excel files, you can automate the process of importing the data from them. Two factors make that easier or harder. One is naming conventions. If each Excel file has a new, unique name and is created daily, then the task is harder. The other factor is whether you can connect to the location of the files directly. It sounds as if this part is true, so it's down, I think, to being able to identify the file(s) you need automatically.
The frequency it's updated with is ad hoc - it's whenever the Form receives a new response - we check it twice weekly.
I would be very surprised to learn that there is NOT a database behind that website, from which the MS Forms are retrieving the data for download to Excel. If I'm right, then you should be able to get a connection directly to that database (maybe SQL Server, but also possibly MySQL or something else).
If you can do that, then neither MS Forms nor Excel need to be involved.
That said, you will need to find out a) if the data is available in a database on the server hosting the website and b) if so, can you get a connection to it.