VBA code to edit power query data source settings

[Draszor]

hi All,

I loaded and transformed the .txt file to excel using power query. all works.
Now I would like to write a macro that would allow me to choose the .txt file I want to load. this is easy, I use the code:
vFile = Application.GetOpenFilename("text-files,*.txt", _
1, "Select One File To Open", , False)
and then use vFile as source file for my Power Query conversion steps. this needs the powerquery connection to be changed into vFile - but how???

I was trying to edit somehow the PQ --> Data Source Settings --> change source --> browse, with VBA but I am getting error each time.

What I want to achieve - to have a button "load data" with linked macro, that will load the txt file I choose and then transform it as per my poser query steps. as a result I get the refreshed table in excel.
I would be grateful for any help

BR
Draszor

 

[MoshiM]

Try this.

Sub PQS_EDit()

Dim  Formula_AR() As Variant, PQT As WorkbookQuery,vfile as string

vfile =Application.GetOpenFilename("text-files,*.txt", 1, "Select One File To Open", , False)

Set PQT = ThisWorkbook.Queries("Query Name goes here")

Formula_AR = Split(PQT.Formula, Chr(34), 3) 'Split Workbook Query formula by quotation marks....limit to 3 sub strings

Formula_AR(1) = vfile '2nd object in array needs to be replaced
 
PQT.Formula = Join(Formula_AR, Chr(34)) 'Join string back together and overwrite

End Sub

 

[Draszor]

Try this.

Sub PQS_EDit()

Dim  Formula_AR() As Variant, PQT As WorkbookQuery,vfile as string

vfile =Application.GetOpenFilename("text-files,*.txt", 1, "Select One File To Open", , False)

Set PQT = ThisWorkbook.Queries("Query Name goes here")

Formula_AR = Split(PQT.Formula, Chr(34), 3) 'Split Workbook Query formula by quotation marks....limit to 3 sub strings

Formula_AR(1) = vfile '2nd object in array needs to be replaced

PQT.Formula = Join(Formula_AR, Chr(34)) 'Join string back together and overwrite

End Sub

hi,
thanks a lot for your answer.
is it possible you attach excel file with some power query data using a simple 1 column .txt file as source, so that I coul;d see why the macro does not work in my case?
I get run time error 13 when executing Formula_AR = Split(PQT.Formula, Chr(34), 3) 'Split Workbook Query formula by quotation marks....limit to 3 sub strings line of the code. I got stuck there and do noit know what might be cousing this one..

 

[MoshiM]

hi,
thanks a lot for your answer.
is it possible you attach excel file with some power query data using a simple 1 column .txt file as source, so that I coul;d see why the macro does not work in my case?
I get run time error 13 when executing Formula_AR = Split(PQT.Formula, Chr(34), 3) 'Split Workbook Query formula by quotation marks....limit to 3 sub strings line of the code. I got stuck there and do noit know what might be cousing this one..

Try removing the () from Dim Formula_AR() at the top

 

[Draszor]

Try removing the () from Dim Formula_AR() at the top

WOW WORKS NOW.

great thanks. now my users won't even know that PowerQuery does the work in the background - great simplification for them.
appreciate your solution