Why does this VBA code not work?

Hello, I am trying to connect to my ERP system using the Odata feed connection. I have an URL that works fine when I paste it in a web browser, but when I try to connect with VBA i get the message ...

Macros and VBA

JKPieterse

Silver Contributor

Feb 06, 2023

Can you set-up this connection manually, using the legacy Get Data From Web feature?
If so, you can adjust your code to change the connection of the manually created query.

Peter_Hermansson

Copper Contributor

Feb 08, 2023

Thank you for your reply JKPieterse . No, I get the same problem if I try to connect with the legacy Web connector.

This is the setup and the current problem:

- I have a worksheet called "Start". In this worksheet I have two cells named "StartDate" and "EndDate".

- The respective cell is formatted as a text field and contains the values '01-01-22' and '12-31-22' respectivly.

- In my connection string I have added a filter, using the two dates. Power Query Editor: = OData.Feed("https://XXX.XXXXXXXX.XX:7048/NAV2017Test/ODataV4/Company('SMAB')/ChartOfAccounts2?$filter=Date_Filter ge '" & Text.From([Start]StartDate, "MM-dd-YY") & "' and Date_Filter le '" & Text.From([Start]EndDate, "MM-dd-YY") & "'", null, [Implementation="2.0"])

The problem is that Power Query is complaining about a comma is missing, but it is not:
Expression.SyntaxError: Token Comma expected.
0001: OData.Feed("https//:XXX.XXXXXXXX.XX:7048/NAV2017Test/ODataV4/Company('SMAB')/ChartOfAccounts2?$filter=Date_Filter ge '" &Text.From([Start]StartDate, "MM-dd-yy) & "' and Date_Filter le '" & Text.From([Start]EndDate, "MM-dd-yy") & "'", null, [Implementation="2.0"])
The error message point to the first filter.

JKPieterse
Silver Contributor
Feb 08, 2023
Can you post an empty workbook with just that query? I think your M-code to get the dates from the range names is wrong.

Forum Discussion

Why does this VBA code not work?

Resources