Hi Excellers, welcome again to some more #macromonday fun. Do you need to share your Excel workbooks?. Do you need to hide the personal information attached to those workbooks.? Well, it is easy to remove personal information from Excel workbooks with this simple Excel macro.
Excel automatically stores some personal information by default when you create a new workbook. Excel stores this information within the workbook itself or the document properties (metadata). So, if you plan to share your Excel workbook, it is probably a good idea to remove this personal information from it.
So, in this week’s Macro Monday blog post, let’s look at how to write and run a simple Excel VBA macro. This will to remove any personal information from our workbooks.
Starting The Macro.
When you decide to write an Excel macro, you first need to choose where to store it. You have two choices.
- Store it in the current Excel workbook you are working on creating.
- Save the macro in your Personal Macro workbook.
So, what is the difference? It is simple. If the code you are writing is specific to that workbook, then store it within the workbook. If you can reuse a type of macro, keep it in your Personal Macro Workbook.
In this example, I could reuse this macro repeatedly to remove personal information. I, therefore, will save it in my Personal Macro workbook.
First, open the Visual Basic Editor. There are two ways to do this.
- Either by hitting ALT +F11 or
- Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
How To Remove Personal Information From Workbooks.
We need to start the process by inserting a new module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Next, type Sub, then the name of your macro. In this example, I have called the macro Remove_Information. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We need to enter the rest of the code between these two lines. This macro is so easy it only really contains one line of code. The full code is below.
Sub Remove_Information
ActiveWorkbook.RemovePersonalInformation = True
End Sub
- You can now run the code – Developer Tab – Macros – Select Remove-Information – Hit Ok
- OR you can attach a command button to the Macro if you want to
- OR you can run the macro from the Immediate Window
Next time your workbook is opened, your personal information will be gone.
I like to remove any personal information before I share or make public any spreadsheets, but it is your choice. It really doesn’t take much to remove this data. I find once it becomes part of your routine, you won’t even think about it.
Don’t forget to sign up for the Excel at Excel Newsletter for three free tips every month.
