Run VBA Code when opening Excel file aka Workbook Open - For all files PERSONAL.XLSB

[Adis_1102]

Hello community,

I have a code that I would like to run when an excel file is being opened. To stress it out - I would like to run the code when any excel file is run, not a specific one (nevermind what the code does)

So, I need to do two main things:

1.: Insert the code in Private Sub Workbook_Open() - to run it on the opening worksheet event
2.: Insert the code in PERSONAL.XLBS - so that it will run automatically when opening any excel file

Firstly, I put the following code in one specific excel file. Works as intented - when I open this file and activate content, the code is run - all good

Private Sub Workbook_Open()
Dim i As Integer
Dim Default As Object
Set Default = ActiveWorkbook.BuiltinDocumentProperties
Dim Name As Variant
Dim Value As Variant

On Error GoTo BadValue

Range("A1") = "Built in Document Properties"

For i = 1 To Default.Count
    Name = Default.Item(i).Name & ": "
    Value = Default.Item(i).Value
    Range("A" & i + 1) = Name
    Range("B" & i + 1) = Value
Next  

BadValue:
Value = "Bad Value"
Resume Next

End Sub

Secondly, I put this code in the PERSONAL.XLBS.
When I open any excel file - I get the runtime error 91: Object variable not set (Error 91). This happens on the following codeline:

Set Default = ActiveWorkbook.BuiltinDocumentProperties

I do not understand why it works on a specific file but not on all files, i.e. in PERSONAL.XLBS...

 

[Joe4]

Welcome to the Board!

Where exactly are you putting this code in the "PERSONAL.XLBS" file?
If you are putting it in the Workbook_Open event, like in the other files, it will only pertain to the opening of the "PERSONAL.XLBS" files, and not the other files being opened.

If you are opening the other files straight from Excel or from Windows Explorer, I do not know that you can get the code from the PERSONAL.XLBS file to automatically run against those files.
To run it automatically on those files, I believe you would need to have it in the Workbook_Open event of each of those files.

What might be a better option is to open up some file that has the code that you want, and add a button in that same file that they can use to browse and open up other Excel files. Then, you control that process, and have code run against it after opening the file.

 

[Adis_1102]

Welcome to the Board!

Where exactly are you putting this code in the "PERSONAL.XLBS" file?
If you are putting it in the Workbook_Open event, like in the other files, it will only pertain to the opening of the "PERSONAL.XLBS" files, and not the other files being opened.

If you are opening the other files straight from Excel or from Windows Explorer, I do not know that you can get the code from the PERSONAL.XLBS file to automatically run against those files.
To run it automatically on those files, I believe you would need to have it in the Workbook_Open event of each of those files.

What might be a better option is to open up some file that has the code that you want, and add a button in that same file that they can use to browse and open up other Excel files. Then, you control that process, and have code run against it after opening the file.

Thanks for the hints!

Yeah I do have it on Workbook_Open Event...I think I will need to create an Add-In for it...

 

[Joe4]

You are welcome. Hope it works out for you.
Not sure how to make sure how you make sure that the code will run automatically (from the Add-In), if the code is not in the files you are opening, and you are not opening the file from within a file that has the code you need.

If you get it to work, please post back. I am very curious to see how this would work (and it might be helpful to others who want to try doing the same thing).
We like this forum to be used as a Reference tool, so that people can search old questions to see if their question is similar to any old ones already asked and answered.