Link to home
Start Free TrialLog in
Avatar of etech0
etech0Flag for United States of America

asked on

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

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.
Avatar of ScriptAddict
ScriptAddict
Flag of United States of America image

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

Avatar of etech0

ASKER

Thanks! Where should I put this code?
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.

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.

Avatar of etech0

ASKER

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 https://www.experts-exchange.com/questions/27419126/Excel-file-on-server-make-trusted-document.html which has yet to receive a feasible solution.)
etech0,

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

Regards,
Brian.
Start-App-Event.xlsm
Avatar of etech0

ASKER

Can I put this into my personal.xlsm?
etech0,

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

Regards,
Brian.
Avatar of etech0

ASKER

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

User type defined not defined
etecho,

Did you include...
Public WithEvents App As Application

Open in new window

Regards,
Brian.
Avatar of etech0

ASKER

no....
Where should I put it?
etech0,

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

Regards,
Brian.
Avatar of etech0

ASKER

Hi.
It turned red like an error:

 User generated image
etho,

My mistake, that should be in the class module.

Regards,
Brian.
Avatar of etech0

ASKER

Where is the class module?
eth0,

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

Regards,
Brian.
User generated image
Avatar of etech0

ASKER

Hi!
I put it in a class module, but the code does not seem to run when I open a workbook!
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.
Avatar of etech0

ASKER

I'm trying to upload it, but I keep getting an error!
What error?
Avatar of etech0

ASKER

That the file extension is not valid
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?
Avatar of etech0

ASKER

No.
The file is xlsb. Does that make a difference?
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.
Avatar of etech0

ASKER

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!
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.
Avatar of etech0

ASKER

Still no go - I tried the txt, the zipped xlsm, and the zipped txt.
HELP!
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.
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.
Avatar of etech0

ASKER

"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!
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.
Avatar of etech0

ASKER

Whew! Got it.

Copy-of-PERSONAL.xls
ASKER CERTIFIED SOLUTION
Avatar of redmondb
redmondb
Flag of Afghanistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of etech0

ASKER

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

Debug.Print "App_WorkbookOpen Started."

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.
Avatar of etech0

ASKER

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.
Thanks, etech0. I saw the Word question and I've posted a possible answer - no surprise, it's almost identical to the Excel one.
Avatar of etech0

ASKER

Thanks!