'Automatically download MS Forms Data via VBA

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

  1. Open the Forms webpage
  2. Click "Export Data in Excel" (somthing alike - I use it in german)
  3. 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.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source