Forum Discussion
Create Pareto chart from multiple Excel workbooks
A bit of background - I work as a data analyst for a manufacturing company. We are starting daily safety inspection checklists (things like PPE use, proper chemical storage, etc) for each area of a p...
If you're going the PowerQuery route:
https://support.microsoft.com/en-us/office/import-data-from-a-folder-with-multiple-files-power-query-94b8023c-2e66-4f6b-8c78-6a00041c90e4
I think vba might be the better solution for this situation, A macro could be written to open selected workbooks in a folder, fetch the needed data and return it to your workbook for the charts. PowerQuery is going to have some overhead with those queries.
https://support.microsoft.com/en-us/office/import-data-from-a-folder-with-multiple-files-power-query-94b8023c-2e66-4f6b-8c78-6a00041c90e4
I think vba might be the better solution for this situation, A macro could be written to open selected workbooks in a folder, fetch the needed data and return it to your workbook for the charts. PowerQuery is going to have some overhead with those queries.
- Interesting, I'll check out the link.
I've never done much with macros or VBA which is why I'm a little hesitant to go that route (although I do other kinds of programming sometimes). Where would I start with that?- The way I see it, you'd have two subroutines:
1. Open multiple workbooks from a given folder. The code simply opens each workbook, performs the actions in macro #2, then closes the workbook.
2. Locate the data to be extracted from each sheet of each workbook and then return it to your active workbook. This step can be fairly straightforward if there's consistency in where the data resides in each workbook.- On the sixth worksheet there are two columns: a column of inspection category descriptions and a column of cells which contain formulas that add up the tallies from the Monday-Friday sheets, which are also very uniform. Beside those is the Pareto chart which just visualizes the two columns. Theoretically, unless the person filling out the sheet changes something, each workbook should be setup exactly the same besides a few cells at the top for the plant and department and staff information.
That said, would you be able to point me to a good VBA tutorial? I know there are dozens on YouTube and around the internet, but I'd like to get this working ASAP and don't want to waste time on tutorials that don't cover relevant information. Thanks for your patience!
