Ok, so you have written a shiny new macro to solve all the problems. The macro, solveWorldProblemsAndMakeSomeCoffee() sits nicely in your personalmacros.xlam file somewhere in C drive. You have also installed the macro as an add-in so that it is always available.
But wait!!!
How do you run your sWPAMSC everyday in the morning?
(ok, wake up now!!!, that is short for solveWorldProblemsAndMakeSomeCoffee())
One way is to,
- Right click on sheet name
- Select View Code
- Navigate to the VBA Project corresponding to your personalmacros.xlam file
- Yawn!
- Open the module with sWPAMSC
- Run the macro
But, shouldn’t this be faster and smarter than that?
Well, it is. You can add your macro to Quick Access Toolbar so that you can run it with just a click (or by pressing a shortcut).
Here is how you can add macros to Quick Access Toolbar (Excel 2007 Version):
- First write your macro and save the workbook as an excel add-in.
- Now, install the add-in by going to Office Button > Excel Options > Add-ins
- Now, right click on QAT and select Customize
- Select Macros from “choose commands…” option.
- Now, select the macro you want to add to QAT and then press Add button
- This will add your macro to QAT with default icon. You can change the icon using Modify button.
- That is all.
Here is how you can add macros to toolbars in Excel 2003:
- First write your macro and save the workbook as an excel add-in.
- Go to Tools > Customize
- Now, click on New button to create a new toolbar.
- Give it a name. Now your new toolbar will show up in Excel 2003 UI.
- Go to Commands tab and select Macros from left. Now drag the smiley icon from right to your new empty toolbar.
- You have added a new button to your toolbar. Now click on it.
- Excel will prompt you to assign a macro to that button. Select the macro from the list shown (it includes the macros in your add-in file).
- That is all.
Now go solveWorldProblemsAndGetSomeCoffee()
How do you customize your QAT / Toolbars ?
Customizing quick access toolbar can be a very productive thing to do. I used to have a bunch of macros added to QAT for quickly accessing them when I was working.
What about you? How do you customize QAT or toolbars? Do you add macros? Share your experience using comments.
7 Responses to “Extract data from PDF to Excel – Step by Step Tutorial”
Dear Chandoo,
Thank you very much for this and it is very helpful.
However, all the Credit Card Statements are now password protected.
Please advise how can we have a workaround for that
Hello sir,
How to check two names are present in the same column ?
Thanks and Regards
Hi, Thank you for the great tip. One problem, when I click on get data >> from file, I don't see the PDF source option. How can I add it?
I tried to add it from Quick Access toolbar >>> Data Tab, but again the PDF option is not listed there.
I am using Office 365
Hi, Thank you for your video. I see you used the composite table, but I when I load my pdf, it does not load any composite table. It has 20 tables and 4 pages for one bank statement. I have about 30 bank statements that I want to combine. Your video would work except that I can't get the composite table and each of the tables I do get or the pages does not have all the info. what to do?
Dear Chandoo,
How do we select multiple amount of tables/pages in one PDF and repeat the same for rest of the PDF;s in the same folder and then extract that data only on power query.
Thank you
Hi, Thank you for your video. I see you used the composite table, but I when I load my pdf, it does not load any composite table. It has 20 tables and 4 pages for one bank statement. I have about 30 bank statements that I want to combine. nice share
One bank statement takes up 20 tables and four pages in this document. I need to consolidate roughly thirty different bank statements that I have. Your video would be useful if I could only get the composite table, which I can't for some reason, and each of the tables or pages that I can get is missing some information.