We help IT Professionals succeed at work.

Excel "Personal macro workbook" macro when specific file is opened

etech0
etech0 asked
on
Medium Priority
413 Views
Last Modified: 2012-05-12
Is there a way to have a macro in my Personal Macro workbook run every time a specific excel file is opened? For security reasons, I cannot store the macro in that file.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Absolutely.  This should get you started

Try this:

Sub RunOnWorkbookOpen()
Dim wbk as Workbook
Application.ScreenUpdating = False
Dim fName As String
Dim fPath As String:    fPath = ThisWorkbook.Path
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = fPath
        .AllowMultiSelect = False
        .Filters.Add "All Files", "*.*"        'everything
        .Filters.Add "Excel Files", "*.xlsx", 1  'default
        .Show
        If .SelectedItems.Count > 0 Then
            fName = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
Set wbk = Application.Workbooks.Open (fName)
Application.Run "'" & wbk.name & "'!Makeitso"
Application.ScreenUpdating = True
end sub

Open in new window

Author

Commented:
Thanks! Where should I put this code?
CERTIFIED EXPERT

Commented:
Hi, etech0.

An alternative would be to put a simple macro in the file in question - it would call the actual macro in the Personal file.

Regards,
Brian.

CERTIFIED EXPERT

Commented:
Ya that's going to be easier ^^^  because you always have your personal workbook open.  You could just have something like:

Private Sub Workbook_Open()
Application.Run "'PERSONAL.XLSX'!Makeitso" ' where Makeitso is the name of the macro in your personal workbook you want to run.  
end sub

Open in new window


Then Just put that code in the afteropen sub of your workbook you want to run the macro.

Author

Commented:
Can't do that - that file is on the server, and files with macros in them lead to issues on the server.

(See my open question http://www.experts-exchange.com/Microsoft/Applications/Q_27419126.html which has yet to receive a feasible solution.)
CERTIFIED EXPERT

Commented:
etech0,

Please see the attached. As it stands, it simply displays a message for each file that opens.

Regards,
Brian.
Start-App-Event.xlsm

Author

Commented:
Can I put this into my personal.xlsm?
CERTIFIED EXPERT

Commented:
etech0,

Sure! Don't forget the "EventClass" class module.

Regards,
Brian.

Author

Commented:
I put in it my personal.xlsm
When I open excel I get an error:

User type defined not defined
CERTIFIED EXPERT

Commented:
etecho,

Did you include...
Public WithEvents App As Application

Open in new window

Regards,
Brian.

Author

Commented:
no....
Where should I put it?
CERTIFIED EXPERT

Commented:
etech0,

In any of the modules - probably best in the one that has your Auto_Open (as I did above).

Regards,
Brian.

Author

Commented:
Hi.
It turned red like an error:

 error
CERTIFIED EXPERT

Commented:
etho,

My mistake, that should be in the class module.

Regards,
Brian.

Author

Commented:
Where is the class module?
CERTIFIED EXPERT

Commented:
eth0,

Please see attached. The easiest way to do this is simply to drag the class module from my file to yours.

Regards,
Brian.
Start App Event

Author

Commented:
Hi!
I put it in a class module, but the code does not seem to run when I open a workbook!
CERTIFIED EXPERT

Commented:
etech0,

Did you include the Auto_Open module? This is not a Class module, just an ordinary one.

If your picture above was from the Personal.xlsm then it does look to be in the correct place. Please post your Personal.xlsm.

Thanks,
Brian.

Author

Commented:
I'm trying to upload it, but I keep getting an error!
CERTIFIED EXPERT

Commented:
What error?

Author

Commented:
That the file extension is not valid
CERTIFIED EXPERT

Commented:
OK, EE only allows specific file types to be uploaded, but XLSM is definitely on the list! Is it possible you're trying to upload a shortcut to the Personal.xlsm rather than the file itself?

Author

Commented:
No.
The file is xlsb. Does that make a difference?
CERTIFIED EXPERT

Commented:
Yes! For some reason EE doesn't accept those. Probably the easiest option is to simply save your XLSB as an XLSM and upload that.

Author

Commented:
I get the following error:
The extension of one or more files in the archive is not in the list of allowed extensions: _rels/.rels

But it's xlsm!
CERTIFIED EXPERT

Commented:
etech0,

Sounds like EE doesn't like one of its components. I'm not sure what's going on so let's try an alternative. Zip up the XLSM and upload the ZIP. (If that fails, try renaming the XLSM to TXT and zip and upload that ZIP.)

Regards,
Brian.

Author

Commented:
Still no go - I tried the txt, the zipped xlsm, and the zipped txt.
HELP!
CERTIFIED EXPERT

Commented:
etech0,

I've no idea what's going on, so let's try another route...

In 37066211 above I included a screen shot. Could you post two similar ones, please. One showing the Auto_Open module and the other the Class module. Make sure that the Project Explorer part (i.e.e the left hand pane) is expanded so that the file name and modules are visible. If the declaration that goes with the Auto_Open is not visible please take a screenshot of it also.

Thanks,
Brian.
CERTIFIED EXPERT

Commented:
etech0,

Silly me, you could try saving it as an XLS. That's not 100% but it would at least let me see you code.

Thanks,
Brian.

Author

Commented:
"The extension of one or more files in the archive is not in the list of allowed extensions: _rels/.rels"

It still didn't work - I guess it wasn't silly of you!
CERTIFIED EXPERT

Commented:
etech0,

That means that EE is identifying the file as an XLSM. Did you select "Excel 97-2003 Workbook (*.xls)" in the "Save As" dialog?

Thanks,
Brian.

Author

Commented:
Whew! Got it.

Copy-of-PERSONAL.xls
CERTIFIED EXPERT
Commented:
etech0,

Well done, thanks!

I've set this up exactly the same way as Start-App-Event.xlsm, so it's working fine.

Regards,
Brian.
Copy-of-PERSONAL-V2.xls

Author

Commented:
WOW!!!!!
one more question: what does this line do?

Debug.Print "App_WorkbookOpen Started."

CERTIFIED EXPERT

Commented:
etech0,

It's completely unnecessary. The Debug.Print command is like MsgBox, except that it writes its output to the Immediate Window instead of a message box. (The Immediate Window is in the same place where you edit your macros - if it's not displayed hit CTRL-G.  Its main function is to allow you to enter and execute VBA commands outside a macro.)

Regards,
Brian.

Author

Commented:
Thanks for all your help!

I'd like to know how to do the same thing in Word, but I'm going to begin a new thread for that one.
CERTIFIED EXPERT

Commented:
Thanks, etech0. I saw the Word question and I've posted a possible answer - no surprise, it's almost identical to the Excel one.

Author

Commented:
Thanks!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.