Forum Discussion
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 "run time error 1004 Project or object defined error"
Private Sub SubmitBtn_Click()
strCompany = Range("E3").Value
strStartDate = Range("A3").Value
strStopDate = Range("C3").Value
strURLCompany = "http://SERVER:PORT/DATABASE/ODataV4/Company('" & strCompany & "')"
strURLDate = "/ChartOfAccounts2?$filter=Date_Filter%20ge%20%27" & strStartDate & "%27%20and%20Date_Filter%20le%20%27" & strStopDate & "%27"
strURL = strURLCompany & strURLDate
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Blad1")
ws.Cells.ClearContents
With ws.QueryTables.Add(Connection:=strURL, Destination:=ws.Range("A1"))
.Refresh
End With
. Any help would be deeply appreciated.
- 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.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.- 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.
