VBA Power Query connection

[Kabous07]

Good day

Almost weekend

My experience with VBA is not that good but will try my best. I have done some basics in the past. Im looking to use tables to keep records of projects information and summarize it all on one table. The problem is however, we would have a new table with each new project and i would like to avoid some steps by just using a button linked to a macro. The macro will basically append all the tables to a summary, including the new ones that get daily added.

I have on one sheet a "Summary"("Summary sheet") table and I have another sheet("Template sheet") a "Template" table. We copy the template sheet and rename it as a project number(many different numbers, normally called "PT####"). We then fill in the new project details. Afterwards we would like to add this new project to the "summary table" in the summary sheet. I was hoping to use something like Power Query and VBA or would take any suggestions please. Reason I am looking at PQ is because the append function is very nice in terms of updating project information, when going back to a certain project.

I hope my explanation makes sense.

Kind Regards
Kabous

 

[sandy666]

Hi,
With PQ you can do that via From File - From Worksheet
Important is to save workbook each time you fill new PT with data (then save!) then go to Summary and Refresh Query Table
here is a very simple example

testfromfile.xlsx

Microsoft Excel Workbook

1drv.ms

note: you'll need to change path to the file to your own (Advanced Editor)

 

[Kabous07]

Hi,
With PQ you can do that via From File - From Worksheet
Important is to save workbook each time you fill new PT with data (then save!) then go to Summary and Refresh Query Table
here is a very simple example

testfromfile.xlsx

Microsoft Excel Workbook

1drv.ms

note: you'll need to change path to the file to your own (Advanced Editor)

Hi. Thank you for comment. You are correct, PQ by itself works very well. The problem however is explaining this to many employees and how they should create queries and append accordingly. I would like to have a automated function where they push a button "Summarize" and then the query is created with the append to the summary. I have tried using the macro recording method, but my issue then is the table names. Each time we create a copy for the PT sheet, the table name would also change. I was trying to use the ActiveWorkbook.Queries, but im not sure how to get the table name with respect to the PT sheet.

The dynamic file path i should come right with.

 

[sandy666]

Table name doesn't matter, in this case it works with sheet names
with file path you can do that via defined path in any cell via CELL() function or get users to create the same folder everywhere, eg. D: \ test \ name_of_file

eg. =CELL("filename",A1)

 

[Kabous07]

Table name doesn't matter, in this case it works with sheet names
with file path you can do that via defined path in any cell via CELL() function or get users to create the same folder everywhere, eg. D: \ test \ name_of_file

eg. =CELL("filename",A1)

There is probably something i do wrong then with PQ. When i append the new table to the summary table, PQ would want to know which table it should go "fetch" and insert and update. For example, if i make a new PT sheet and the new table is PT785, then add to connections so that i can append, then excel asks which table is the main and which table is the one that must be appended, therefore requiring table names. Do you think this can be automated or am i asking to much of excel with to little skill from my side?

 

[sandy666]

You did't read my posts. As I said it works with tab names not table names. Did you try on my example?

 

[sandy666]

Could you post here link to your shared excel file via onedrive, googledrive, dropbox or any similar service?

 

[Kabous07]

You did't read my posts. As I said it works with tab names not table names. Did you try on my example?

Apologies, must be my missunderstanding. I did try the example and there was no VBA just a basic PQ append. This i have managed before, for example please refer to dropbox link below.

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

 

[sandy666]

Tables CANNOT contain the same names in headers

s666-PQv2-Project Data.xlsm

Microsoft Excel Workbook

1drv.ms

hope I didn't forget about something
additionally there is a filepath so don't delete it by mistake

 

[sandy666]

save file
refresh all
save again
then use