VBA code to edit power query data source settings

Draszor

New Member
Joined
Sep 28, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
370
Office Version
  1. 2016
Platform
  1. Windows
Try this.
VBA Code:
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

New Member
Joined
Sep 28, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Try this.
VBA Code:
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

Active Member
Joined
Jan 31, 2018
Messages
370
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Sep 28, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,958
Members
414,352
Latest member
macquarie_jchan58

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top