Re: [MSAccessProfessionals] VBA code to open a excel file with power query etl and 'refresh it all'

Can't you just link all the Excel tables directly from Access and let Access do all the querying jobs so as to modify the Excel tables from Access?

Giorgio

On 09/30/2022 9:59 AM anzus101 <anzus101@gmail.com> wrote:

Thank you for responding Duane.

Ms Excel has become a swiss knife; It has several other tools inside. 

Power Query is an ETL tool that allows you to create Tables. These Tables then go to Power Pivot and from there you create Cubes.

Power Query brings to access the ETL capabilities that was missing, but the excel file does not have the query, it is the Power Query App which then puts it onto the excel file.

Having said this, if an excel file has 20 tables created in Power Query, the excel file needs to be open and then refresh all the underlying connections.

What I am asking here is if anyone of you is using Power Query as an ETL tool for access and to then, come up with a way to refresh all power query connections within ms access.

Please feel free to ask if you need more clarifications about my question.

On Friday, September 30, 2022, Duane Hookom <duanehookom@hotmail.com> wrote:

Hi anzus101,

I'm not sure why you need to refresh. I just 

-created a link to an Excel file

-opened the Excel file

-added a new column

-closed the Excel file

-opened the linked Excel table

-the new column was visible with no extra effort

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of anzus101 <anzus101@gmail.com>
Sent: Thursday, September 29, 2022 6:30 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] VBA code to open a excel file with power query etl and 'refresh it all'

Adding more detail;

I have the excel files as linked tables, so I need a macro that can 'refresh all' the excel file, which includes the underlying power query. I want to add a button in a form.

On Thursday, September 29, 2022, anzus101 via groups.io <anzus101=gmail.com@groups.io> wrote:

Guys, 

I am using Power query to do a lot of ETL to then import into a db. I would like to have a macro that automates this task. Could you please share any code you may have for this regard? Thank you so much in advance.