Power query source VBA (Application.GetOpenFilename)

[salim9696]

Hi, i have a problem with the second part of my code. I wana upload to power query data from choosen document. I could choose a document but excel doesnt wana upload this data. The issue is "the name source wasnt recognized. make sure its spelled correctly" Run time error 1004. Pls help me cause i am new guy in vba and i am still learning. Thank u and this is my code :

Sub tes21()
Dim sfile As Variant
Dim ws As Worksheet
Dim wb As Workbook


sfile = Application.GetOpenFilename

If sfile <> False Then
Workbooks.OpenText sfile
ThisWorkbook.Activate
ActiveWorkbook.Queries.Add Name:="Transactions (2)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(" & sfile & "),[Delimiter="";"", Columns=18, Encoding=1250, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""transactionfield9" & _
""", type text}, {""a_numberid"", Int64.Type}, {""b_numberid"", Int64.Type}, {""Description"", type text}, {""Datestart"", type text}, {""TransactionCode"", type text}, {""Reference"", type text}, {""Charge"", type text}, {""DBCR"", type text}, {""TransactionField6"", type text}, {""MappedTransactionCode"", type text}, {""B_Number"", type text}, {""Name_B"", type tex" & _
"t}, {""A_Number"", type text}, {""Name_A"", type text}, {""RefDisplay"", type text}, {""Product_Type"", type text}, {""Product_Number"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Transactions (2)"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Transactions (2)]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Transactions__2"
.Refresh BackgroundQuery:=False
End With

End If

End Sub

 

[Alex Blakenburg]

Try changing the sfile part to this:

File.Contents(" & """" & sfile & """" & ")

 

[salim9696]

Try changing the sfile part to this:

File.Contents(" & """" & sfile & """" & ")

Thanks, buddy its working.

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.