Power Query (1 Viewer)

[zezo2021]

Friends
in the attached file
one contains data that come to me weekly

I want to upload the datasheet to Power query Excel by Vba Excel

I prepare everything to get the path of the Excel file

Just I need code to:
Append the Excel path to Power query Excel

 

[arnelgp]

create a Macro that will Load and Transform your data.
modify the macro that will suit your need.

 

[zezo2021]

create a Macro that will Load and Transform your data.
modify the macro that will suit your need.

good solution

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveWorkbook.Queries.Add Name:="Data", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents("" & strFileName & ""), null, true)," & Chr(13) & "" & Chr(10) & " Data_Sheet = Source{[Item=""Data"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date of Service"", type dat" & _
"e}, {""Timestamp"", type datetime}, {""Client"", type text}, {""Clinician"", type text}, {""Billing Code"", Int64.Type}, {""Modifier"", Int64.Type}, {""Rate per Unit"", Int64.Type}, {""Units"", Int64.Type}, {""Total Fee"", Int64.Type}, {""Progress Note Status"", type text}, {""Client Payment Status"", type text}, {""Charge"", Int64.Type}, {""Uninvoiced"", Int64.Type" & _
"}, {""Paid"", Int64.Type}, {""Unpaid"", Int64.Type}, {""Insurance Payment Status"", type text}, {""Charge_1"", Int64.Type}, {""Paid_2"", Int64.Type}, {""Unpaid_3"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
Workbooks("Upload To Power Query.xlsm").Connections.Add2 "Query - Data", _
"Connection to the 'Data' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Data;Extended Properties=""""" _
, "SELECT * FROM [Data]", 2
End Sub




I face an issue putting variable string not work instead of the path


can this code append to exist power query

 

[arnelgp]

maybe add more double-qoute:

...& " Source = Excel.Workbook(File.Contents(""" & strFileName & """), null, true)," ...

 

[arnelgp]

can this code append to exist power query

you need to "right click" the query and "append" it to an existing ListObject (table).

 

[zezo2021]

Testing

Thank you for all your help