using Shell to open an MS Excel file and run a macro

[Paul]

I'm trying to use the Shell command to open an MS Excel .xlm file, and also
run a macro as soon as that file opens. The file I'm trying to open is

M:\Leasing-And-Design\Masters\Programs\Program3.xlm

The macro I'd like to run as soon as that file opens is:

Open.Program__o

In an effort to accomplish this, I've tried the following:

Shell "Excel.exe "
'M:\Leasing-And-Design\Masters\Programs\Program3.xlm'!Open.Program__o",
vbMaximizedFocus

That line above does manage to open Excel, but it doesn't work. Instead,
it displays an error message saying that Excel can't find the file. I'm
reasonably sure the problem is with the syntax in the quote marks. Can
anyone tell me how I can modify the Shell command so that it will open file
and run the macro as soon as it opens?

Thanks in advance,

Paul

 

[Stefan Hoffmann]

hi Paul,

I'm trying to use the Shell command to open an MS Excel .xlm file, and also
run a macro as soon as that file opens. The file I'm trying to open is

Use the method shown here:

http://www.mvps.org/access/api/api0018.htm


Use as start parameter only the file name including the whole path. This
should work as .xlm is a registered extension.


mfG
--> stefan <--

 

[Paul]

Thanks for the reply, Stefan. However, I'm trying to do more than just open
the file. I'm also trying to get the macro named "Open.Program__o" to run
automatically when the file opens. The macro name is somewhat misleading,
because it doesn't open the program - it does other things. And what I'm
trying to do is to use the Shell statement to first open the Excel
spreadsheet, and then cause that macro to run as soon as the spreadsheet
opens:

Shell "Excel.exe
"'M:\Leasing-And-Design\Masters\Programs\Program3.xlm'!Open.Program__o",
vbMaximizedFocus

I think the only reason it isn't working is that I have a problem with the
quotation mark syntax. I've tried many different combinations, but none has
worked so far.

Can you tell where I might be going wrong with those quotation marks in the
Shell statement above?

Paul

 

[Stefan Hoffmann]

hi Paul,

Thanks for the reply, Stefan. However, I'm trying to do more than just open
the file. I'm also trying to get the macro named "Open.Program__o" to run
automatically when the file opens.

Sorry, didn't read your post carefully enough.

Shell "Excel.exe
"'M:\Leasing-And-Design\Masters\Programs\Program3.xlm'!Open.Program__o",
vbMaximizedFocus

I think the only reason it isn't working is that I have a problem with the
quotation mark syntax. I've tried many different combinations, but none has
worked so far.

This should work:

Dim Document As String
Dim Excel As String
Dim Macro As string

Document = "M:\Leasing-And-Design\Masters\Programs\Program3.xlm"
Excel = "C:\Program Files\Micorosoft Office\Office 11\Excel.exe"
Macro = "Open.Program__o"

Shell """" & Excel & """ """ & Document & """ /m " & Macro


mfG
--> stefan <--

 

[Paul]

Well, I tried the following code which launches Excel and opens the .xlm
file:

Dim Document As String
Dim Excel As String
Dim Macro As string

Document = "M:\Leasing-And-Design\Masters\Programs\Program3.xlm"
Excel = "C:\Program Files\Microsoft Office\Office11\Excel.exe"
Macro = "Open.Program__o"

Shell """" & Excel & """ """ & Document & """ /m " & Macro

So there's no problem with the application, path or file name, but for some
reason it can't find the macro, and it displays this message:

'Open.Program__o' could not be found. Check the spelling of the filename,
and verify that the file location is correct. etc.

I verified all components of the path, file name and macro name, character
by character, including the double underscore in the macro name, and I'm
sure there are no typos in those strings.

Please let me know if you have any other thoughts. In any event, I
appreciate your taking the time to help me with this, Stefan.

 

[Stefan Hoffmann]

hi Paul,

So there's no problem with the application, path or file name, but for some
reason it can't find the macro, and it displays this message:
'Open.Program__o' could not be found. Check the spelling of the filename,
and verify that the file location is correct. etc.

Try to call the macro without the Open prefix.

mfG
--> stefan <--

 

[Paul]

Stefan,

I finally realized why I was getting the error message. The macro
"Open.Program__o" wasn't in a VBA module. It was written directly in a
spreadsheet, and the macro name was the same as a range name. The Excel
application was created years ago, and while the approach may be outdated,
it does what it is supposed to do. In addition, the worksheet that contains
that macro was hidden. So I decided to try using Excel automation from
Access VBA. Here's what I finally used to get it to work:

Dim Document As String
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
On Error Resume Next

Document = "M:\Leasing-And-Design\Masters\Programs\Program3.xlm"
Set appExcel = CreateObject("Excel.Application")
Set wbk = appExcel.Workbooks.Open(Document)

With appExcel
.Visible = True
.Windows(1).Visible = True
.Run ("Open.Program__o")
End With

I'm going to save the syntax you suggested in the Shell command to use it in
other situations.

Thanks for the suggestions.

Best regards,

Paul