Fix a VBA Variable URL in Excel Power Query Web.Contents
Question details
The user needs to insert a URL from a worksheet cell into a VBA-generated Power Query expression.

- 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.
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.
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.
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.
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.
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.
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.

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.

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.




