Forum Discussion
Linking Microsoft Forms to Microsoft Access
Currently the process is set up to download the Excel file with the responses from MS Forms, and then import this into Access. My understanding is you can create a linked Excel spreadsheet with a file stored on a LAN, but as MS Forms hosts the Excel output on the website link to the Form, is there any way you can directly specify this online output it creates as the source of the data, or do you have to download it to a hard drive?
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.
- It's a .xlsx file, which MS Forms offers as an option on its Responses tab. It both allows you to hit the "View Results" tab to see the responses as individual records while on the same webpage, or to "Open in Excel", which will download them as a .xlsx attachment. So I suppose my question is whether Access is capable of importing those results directly from that results webpage, saving the process of downloading the file, or whether I'd be better off just automating the import process while storing the file locally.
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.