Automatically download MS Forms Data via VBA

541 views Asked by At

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.

0

There are 0 answers