A really noddy request for you gurus.
When my auto_open macro contained in a personal.xls is triggered by the opening of a "real" workbook how does it discover the name of the "trigger file" and hence start to work on THAT file?
All the things I have tried such as "thisworkbook.name" either refer to personal.xls OR fail with "91" or "1004" errors due to objects not being valid?
Mike Bush,
Welcome to the forum!
Try using ActiveWorkbook instead of ThisWorkbook
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Sorry - no - that causes
Run-time error 91
Object Variable or With block variable not set
Mike Bush,
In that case I'd need to see the code before I can comment further.
? There isn't any code yet - I am trying to get something to work !
All I have in the SUB is 1 line :-
msgbox("File=" + thisworkbook.name) is OK but shows "personal.xls"
msgbox("File=" + activeworkbook.name) throws the 91 error
I'm sure I'm missing something fundamental - but not sure what that is[COLOR="Silver"]
Mike Bush,
Alright, I created an Auto_open macro in my own personal.xlsb file and used the following code:
Please Login or Register to view this content.
It only returns displays "Personal.xlsb". It looks like the personal workbook is opened first, its auto_open routine runs, and then Excel opens the workbook you wanted to open (whether you double-clicked it, or opened a new instance of excel). Using File -> Open to open a new Excel file doesn't cause the personal workbook's Auto_open code to trigger, because the personal workbook is already open.
In short, I'm not sure you can use the auto_open routine in the personal workbook to do anything with the activeworkbook.
By default, any macro with unqualified range references refers to the active worksheet, which is in the active workbook. You don't have to do anything to get that behavior.
ThisWorkbook always refers to the worbook containing the code.
Entia non sunt multiplicanda sine necessitate
Ok guys - thanks for your help. So - if I step back to my "requirement" you might have some other ideas.
I wanted my "boss" to have an easy way of "pre-processing" a whole series of XLS arriving by email which are columns of figures where there should be a TOTALS row across the bottom but this is often forgotten (becuse the figures are cut and pasted from another system). I wanted HIM to have an auto_open macro which would look at these files, and, if the line is missing it would be created. I can't get the macro into the source files. Any thoughts?
You need an application Event handler, this worked for me in ThisWorkbook module, I've never used personal.xls so I'm not sure if the syntax would change (as it does for an add-in). Just run the Workbook_Open() when your add-in/personal.xls opens
Please Login or Register to view this content.
The syntax for that should be the same anywhere.
Everyone who confuses correlation and causation ends up dead.
Ta romper, fair enough, never really ever had the need for add-ins or personal.xls
Last edited by Kyle123; 08-01-2012 at 04:42 AM.
Thanks everyone - I now have an ADD-IN rather than a PERSONALXLS - IT declares the class variable APP and sets up the eventhandler so that I can detect any Workbook open and do my stuff.
Thanks for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks