Run-Time Error 438 - Object doesn't support this property or method

[thapco4]

I set this up a while back and everything worked fine, but now it just dies on the very first line (red text). I created it by doing a macro and then putting it in the vba. Goal is to convert the data in the columns to text, before performing the query create, in order to prevent this type of error if I remember my thinking correctly.Here is the code...

Sub create_queries()

' Sheets("Sheet1").Select
' Range("A2:H2").Select
ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""SO No"", Int64.Type}, {""Date"", type date}, {""PO No (Customer)"", type text}, {""Ship By"", type date}, {""Status"", type text}, {""Customer ID"", type text}, {""Customer"", type text}, {""Amount"", Currency.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
ActiveWorkbook.Connections.Add2 "Query - Table1", _
"Connection to the 'Table1' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1;Extended Properties=" _
, """Table1""", 6, True, False
Sheets("Sheet2").Select
ActiveWorkbook.Queries.Add Name:="Table2", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""Table2""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""SO/Proposal No."", Int64.Type}, {""Item ID"", type text}, {""Item Description"", type text}, {""Item Type"", type text}, {""Order Qty"", type number}, {""Qty on Order"", type number}, {""Amt on Order"", Currency.Type}, {""Qty on Hand" & _
""", Int64.Type}, {""Qty on PO's"", Int64.Type}, {""Qty Available"", Int64.Type}, {""Ready to Ship"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
ActiveWorkbook.Connections.Add2 "Query - Table2", _
"Connection to the 'Table2' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table2;Extended Properties=" _
, """Table2""", 6, True, False
Sheets("Sheet3").Select
ActiveWorkbook.Queries.Add Name:="Table3", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""Table3""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""PO No"", type text}, {""PO Date"", type date}, {""Vendor ID"", Int64.Type}, {""Vendor Name"", type text}, {""PO State"", type text}, {""Item ID"", type text}, {""Line Description"", type text}, {""U/M ID"", type text}, {""Qty Ordered" & _
""", Int64.Type}, {""Qty Received"", Int64.Type}, {""Qty Remaining"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
ActiveWorkbook.Connections.Add2 "Query - Table3", _
"Connection to the 'Table3' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table3;Extended Properties=" _
, """Table3""", 6, True, False


End Sub

Here is a sample of the table it is working on...

SO No	Date	PO No (Customer)	Ship By	Status	Customer ID	Customer	Amount
436030	1/8/20	999067261	1/8/20	Open	DDPCSF	xxxxxxxxxxx	5,477.94
436076	1/23/20	127559	1/23/20	Open	DXACUI	rrrrrrrrrrr	1,045.09
436075	1/23/20	127558	1/23/20	Open	DXACUI	rrrrrrrrrrrrrrr	119.23
435424	5/14/19	HJ23265pg-11-190514	5/14/19	Open	DXAUDA	hhhhhhhhhhhhh	79.80

 

[Legacy 456129]

Where's the first = in the formula which should be in quotes. Anything that is meant to be text has to be in double quotes, so what exactly would is .Formula = "let" & supposed to do?

A properly formatted example

.Formula =  "=IF([@ModTm],IFERROR(OFFSET([@Change],-1,,1,1)+1,[@Offset]+1),0)"

If you only need the outcome being placed in the sheet each time you run the script look at the X = Evaluate("Your Excel Formula") VBA solution. In that case = is not required ;O)

 

[thapco4]

It is multi-line code (ie carriage return). The "first =" is on the first red line "...,Formula =_". The "_" character tells the vba to continue on next line. Is that what you are asking?

 

[Legacy 456129]

Yes, and still it would need at least the = sign after the first quote, so .Formula = "=let" & et cetera , at least it's how it works in my book. The other maybe is VBA could be interpreting "let" as needing ""Let"", double quotes i.e. text, but doubt that's the issue.

OT\ "let" is a serious throwback to the days of Basic on DOS 2.0 /OT. Not used it in decades.

Let statement (VBA)

Office VBA reference topic

docs.microsoft.com

 

[thapco4]

Well, I checked that... it didn't help. I appreciate the help. I think the problem may be in the part about the TableTransform or the Queries.Add itself.
My VBA isn't that extensive.

 

[thapco4]

Actually, I just thought of this. I think Microsoft "upgraded" me from 2016 to 365 a few months back. That may be when it quit but I have no way of knowing. Anyone have experience with this as the possibility, especially if "let" has been deprecated?

 

[Legacy 456129]

VBA development is on close to standstill. Saw something that suggests them pushing Office Script, which is really really a DOS throwback from what I saw.