View Single Post
 
Old 01-24-2019, 08:16 PM
Kenneth Hobson Kenneth Hobson is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: Jun 2018
Posts: 37
Kenneth Hobson is on a distinguished road
Default

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
Reply With Quote