This boils down to a simple string concatenation issue. It is a bit more complicated because of the long string with embedded double quote characters.
When dealing with quote characters like that, one can use the double quotes method and/or the q method as shown. I used both here.
Notice that I also broke down the long string to something more manageable. Note how I also used Debug.Print to show the built string in the VBE Immediate Window after the run. This is a handy way to see if the string was built right.
Code:
Sub Main()
Dim q$, f$
q = """"
'ActiveWorkbook.Queries.Add Name:="x (2)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""C:\Users\lenovo1\Dropbox\_Excel\CSV\41469-changing-absolute-path-relative-macro\x.csv""),[Delimiter="","", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers" & _
""",{{""Lot_No"", Int64.Type}, {""Unit"", type text}, {""Cont_UE"", Int64.Type}, {""Int_UE"", Int64.Type}, {""Owners_Name"", type text}, {""Owners_Address"", type text}, {""Owners_Phone"", type text}, {""Owners_Email"", type text}, {""Committee_Member"", type text}, {""Balance"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
'q & "C:\ken\x.csv" & q & "),"
f = "let" & vbCrLf & "Source = Csv.Document(File.Contents(" & _
q & ThisWorkbook.Path & "\x.csv" & _
"[Delimiter="","", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.None])," & _
vbCrLf & "#""Promoted Headers"" = Table.PromoteHeaders(" & _
"Source, [PromoteAllScalars=true])," & vbCrLf & _
"#""Changed Type"" = Table.TransformColumnTypes(" & _
"#""Promoted Headers" & _
""",{{""Lot_No"", Int64.Type}, {""Unit"", type text}, " & _
"{""Cont_UE"", Int64.Type}, {""Int_UE"", Int64.Type}, " & _
"{""Owners_Name"", type text}, {""Owners_Address"", type text}, " & _
"{""Owners_Phone"", type text}, {""Owners_Email"", type text}, " & _
"{""Committee_Member"", type text}, {""Balance"", type text}})" & vbCrLf & _
"in" & vbCrLf & "#""Changed Type"""
Debug.Print f
ActiveWorkbook.Queries.Add , "x", f
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""x"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [x]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "x"
.Refresh BackgroundQuery:=False
End With
End Sub