Power Query Problems

Fix a VBA Variable URL in Excel Power Query Web.Contents

John WilsonJohn Wilson Nov 22, 2026 868 views

Question details

The user needs to insert a URL from a worksheet cell into a VBA-generated Power Query expression.

Fix a VBA Variable URL in Excel Power Query Web.Contents
Product
Microsoft Excel Power Query and VBA
Device & OS
not provided
Scenario
A VBA macro reads a website address from a worksheet cell and builds a query using Web.Contents.
Observed behavior
The query works with a fixed website address but fails when the URL variable is concatenated into the expression without the required quotation marks.
Before you start

Check that the worksheet cell contains a complete, accessible URL, including https:// or http://. Open the VBA editor and locate the code that constructs the Power Query formula.

Solution 1Recommended

Quote the URL variable in the generated Power Query formula

Use escaped VBA quotation marks so the generated M expression passes the URL to Web.Contents as text.

Power Query M requires a text URL to be enclosed in quotation marks. In a VBA string, doubled quotation marks produce a literal quotation mark in the generated formula.

1
Read the URL from the cell

In the VBA editor, assign the cell value to a string variable. For example: VA = CStr(Worksheets("Sheet1").Range("A1").Value). Replace Sheet1 and A1 with the actual sheet and cell.

2
Build the quoted expression

Escape any quotation marks within the value, then construct the M formula: VA = Replace(VA, Chr(34), Chr(34) & Chr(34)) queryM = "let Source = Web.Page(Web.Contents(""" & VA & """)) in Source". Declare VA and queryM as String variables if they are not already declared.

3
Add or update the query

Pass queryM to the existing query-creation code. To create a new query, for example, use ThisWorkbook.Queries.Add Name:="WebsiteQuery", Formula:=queryM. Choose a name that is not already in use.

4
Check and refresh

In Excel, open Data > Queries & Connections, open the query, and check its formula in Advanced Editor. The URL should appear inside quotation marks in Web.Contents. Refresh the query and provide website credentials if Excel prompts for them.

Quote the URL variable in the generated Power Query formula
Example generated formula: For a cell containing https://example.com, the generated M expression is: let Source = Web.Page(Web.Contents("https://example.com")) in Source.
Free Microsoft Office alternative

Work with spreadsheet data in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday spreadsheet work. It does not repair Microsoft Excel Power Query or guarantee compatibility with every VBA macro.

Open and edit spreadsheet data in a familiar interface.Compatible with common Microsoft Excel formats, including XLSX and XLS.Use a free, lightweight office suite for everyday spreadsheet tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the fixed URL work but the VBA variable fail?

The fixed URL is quoted as text in the Power Query M expression. If VBA inserts the variable value without quotation marks, M may interpret it as an invalid expression rather than a URL string.

Where can I check the formula generated by my macro?

In Excel, open Data > Queries & Connections, open the query, and select Advanced Editor. Check that Web.Contents contains the URL enclosed in quotation marks.

What if the query still fails after I add the quotation marks?

Check the URL in the worksheet cell and test whether the website is accessible. In Excel, review Data > Get Data > Data Source Settings for the site's permissions and credentials.

Will changing the cell automatically update the query URL?

Not with this VBA approach alone. The cell value is inserted when the macro builds the formula, so rerun the code or update the query formula after changing the cell.