0

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.

2
  • check whether your browser is .busy and it's .readystate Commented May 6, 2022 at 11:19
  • Ana, I would defenitely use Power Query to do this, not VBA. Power Query is embedded in Excel and Power BI (both uses the same "module" to extract data). Therefore, if you find on YouTube how to do it in Power BI, it will be the same code in Excel. Commented May 6, 2022 at 12:28

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.