I use a MS Form to collect Data and use an xlsm.file where I copy the new data and then further work with the data. In order to update the data, I have to
- Open the Forms webpage
- Click "Export Data in Excel" (somthing alike - I use it in german)
- In my xlsm.file: click the Button "Import Data" (-> custom macro) and then select the downloaded file
I want to automize this so that whenever I open the xlsm the data is automatically downloaded from the MS Forms Document and integrated in the xlsm file via the custom macro.
I do however struggle with automatically working with MS-Forms-URls.
This works:
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.navigate "google.com"
Sleep (1000)
Dim HTMLDoc As Object
Set HTMLDoc = IE.document
Set IE = Nothing
This does not work:
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.navigate "https://forms.office.com"
Sleep (1000)
Dim HTMLDoc As Object
Set HTMLDoc = IE.document
Set IE = Nothing
It does navigate to the webpage but for "Set HTMLDoc = IE.document" it says: The remote server machine does not exist or is unavailable (Error 462).
Any help is highly appreciated.