As I've already made clear, I spend a lot of time using Screaming Frog. I love the copy & paste functionality that it offers, which allows me to move data directly from the crawler into Excel, but sometimes I'd rather have Excel build my technical reports for me... With that in mind, I wanted to share a macro that has made my life a little easier. Hopefully it will help you, too!
Before we dive in, there are a couple of things that you will want to do prior to saving any macros. If you already know the basics, skip right down to the juicy stuff.
Also, sorry in advance, Mac users, if this doesn't work for you!
1. Enable developer toolbar
Here are the directions from Microsoft:
2. Create your personal workbook
If you've never saved a macro in your personal workbook, I would recommend doing so. This will allow you to access your macros globally from any workbook in the future, so that you don't have to recreate your macros each time that you'd like to use them.
In order to create your personal workbook, you'll need to record a macro. You can do this by clicking on 'Record Macro' in the 'Developer' tab and selecting 'Personal Macro Workbook' in the drop-down.
Click 'OK', then click 'Stop Recording'.
Once you have recorded your empty sample macro, your personal workbook will be created, and will be accessible in this folder on your computer:
C:Usersyour-usernameAppDataRoamingMicrosoftExcelXLSTART
(Your hidden files must be visible.)
In order to make changes to your personal workbook, you will need to unhide it by going into the 'View' tab and selecting 'Unhide'. From here, you can add your first macro.
Now that you're done with the boring stuff, it's time for some macro magic!
>> Import Multiple CSVs as Worksheets in a Single Workbook
I use this macro a lot to compile various exports from Screaming Frog, but you can use it for any report that requires multiple CSVs, even if they are from different sources. All that you have to do to get started is save all of your CSVs into one designated folder.
Follow these steps to add the macro to your personal workbook:
1. Unhide your personal workbook
2. Click on 'Visual Basic' in the Developer Tab. A new window will open.
3. In the the Visual Basic window, right-click on your personal workbook, select 'Insert' and 'Module'. A blank module will appear.
4. Copy and paste the following macro into the module.
5. You will need to update the file path with your username and your folder name for where the files are located. Be sure not to remove the trailing slash at the end of the file path.
6. If you are combining CSVs from a source other than Screaming Frog, you will also want to skip the step where we remove the first row from each sheet. You can do this by adding an apostrophe at the beginning of the line shown below:
7. The macro will still create table formatting, so be sure that row 1 of each CSV contains your table headers, otherwise your tables will look a little funky. If you already know that your CSVs don't contain header data in row 1, you can go ahead and turn off the table formatting by adding an apostrophe on the following lines, as shown below:
8. Once the macro is filled in with your file path and edited to fit your needs, click on the 'Save' icon and close the Visual Basic Window.
9. Re-hide your personal workbook by clicking 'Hide' in the 'View' tab.
10. To run your macro, open a new workbook and select 'Macros' in the 'Developer' tab. Choose the desired macro from the list and click 'Run'.
In a matter of seconds, you will have the beginnings of a report formatted with tabs and tables that can be sent to clients or coworkers for further review.
H/T to Jerry Beaucaire, JS411, and Dave Hawley who provided the foundation for this mashup of a macro.
Know any other useful macros for marketers? Let me know in the comments below, or connect with me on Twitter @aichleebushnell.
