How to run Macro in Excel on File Open

[shcuck]

Hi everyone. I am new to this forum, so I might be asking questions that have already been asked, but here it goes anyway.

I am trying to run a macro with a command button that will open another excel file. But what I need it to do is when the second excel files opens, I need it to run a macro that is within that file. I have included the code I am using:

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
' Button launches Word and opens a specified document
' And runs an optional macro. the macro could print out the word doc and quit

Dim retval As Variant
Dim docName, MacroName As String
' Drive and Full path to name and location of Word document
'docName = "C:\Documents_and_Settings\Owner\Desktop\testfile.xls"

'Optional Macro Name. Note Word Macro name cannot have any spaces in it.
MacroName = "/M" & "test"

'Note full path to Word including Drive and folder
retval = Shell("C:\Program Files\Microsoft Office\Office\EXCEL.EXE C:\testfile.xls" & "" & MacroName, vbNormalFocus)

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub


What I also need it to do is close the newly opened excel file once the macro has run.

I hope I make sence here


Thanks for your help and advise

Shcuck

 

[Loomah]

I'm a little confused by your code comments referring to word but you obviously want to use xl so in essence this is what you'll need

in you main file, on your button or whatever:-

Private Sub cmdOpenOtherFile_Click()
Workbooks.Open Filename:="C:\FullPath\FileName.xls"
' some other stuff??
ActiveWorkbook.Close savechanges:=False
End Sub

In the file "FileName.xls" opened above

Private Sub Workbook_Open()
MsgBox "Hello, I'm open now!!"
' well whatever it is you want to do!
End Sub

This is a quick example.  ActiveWorkbook.Close works here but you will be better off referencing the workbook properly ie
[code]Workbooks("FileName.xls").Close savechanges:=False 'but will probably be true

;-)
If a man says something and there are no women there to hear him, is he still wrong?

 

[shcuck]

Thanks for your assitance. I will try it out and see how it works.

 

[jebry]

Hi Shcuck!

Call the macro in the second file autoexec and it will automatically run whenever the workbook is opened.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[RobBroekhuis]

A word of caution: both of the suggestions above only work if you ALWAYS want to run the macro when the workbook in question opens, not just when you open it using the commandbutton.
Rob