Open Excel file - Simple Question I hope

[Guest]

Hello again,
I think I have used my annual quota of questions in the past several days.
But... here is one more, hopefully simple.

I have an Excel document that is password protected for modifications, so it
pops a message box to the user asking for password or Read Only.

Goal: Open the Excel file from a command button.

Sounds simple enough, but... the Excel message box to the user is behind
the main form. I have to ALT+TAB to locate the Excel icon to get focus on
Excel. How can I open this doc and force the excel prompt to get focus?

Here is the simple hyperlink. I have also tried it with creating excel
objects and open it, but same results.

(within Access VBA)
Application.FollowHyperlink strFile, , True

Any assistance is appreciated.

Thank you,
David

 

[Guest]

Here is one way to open an Excel workbook via VBA:

Dim OpenExcel As Variant
Dim ExcelFile as String
ExcelFile = "C:\YourPath\YourFile.xls"
OpenExcel = Shell("Excel.exe " & ExcelFile , 0)

This simply shells to Excel and opens the requested file. It sounds like you
may be after more than this, though, and I think this may assume that Excel
is not already open.

 

[Guest]

Works very well. Is there a modification I can make if my file name contains
spaces?


THANK YOU!!!!!!

 

[Guest]

Good point. My error. To accomodate a path and/or filename that has spaces,
use this:

Dim OpenExcel As Variant
Dim ExcelFile as String
ExcelFile = "C:\Your Path\Your File.xls"
OpenExcel = Shell("Excel.exe " & """" & ExcelFile & """", 0)

It's the quadruple quotes that does it.

I also just realized that the examplel I gave you used WindowStyle 0 (that's
the 0 just before the closing parenthesis) which hides the Excel window when
it is opened. I just copied my original example from an app that opened an
Excel sheet and ran its Auto_Open macro, which in turn opens, modifies, and
saves another Excel workbook, all hidden from the user's view.

You could have used the constant vbHide instead of the value 0 instead of 0
in my example, like this: OpenExcel = Shell("Excel.exe " & """" & ExcelFile &
"""", vbHide). Here, from the help for the Shell function, are the valid
window styles for the Shell function, in case you want the called Excel
workbook to be visible to the user.

vbHide 0 Window is hidden and focus is passed to the hidden window.
vbNormalFocus 1 Window has focus and is restored to its original size and
position.
vbMinimizedFocus 2 Window is displayed as an icon with focus.
vbMaximizedFocus 3 Window is maximized with focus.
vbNormalNoFocus 4 Window is restored to its most recent size and position.
The currently active window remains active.
vbMinimizedNoFocus 6 Window is displayed as an icon. The currently active
window remains active.

 

[Guest]

Thank you Brian,
just a quick followup question, I can see the instance of Excel fine. It
may be reacting differently because I have it password protected.

If I do not have an instance of Excel open, the doc opens without a file
menu or toolbars. If an instance of Excel is already open, it displays fine.

Is the best way to fix this to open an Excel object and then do this? If
so, and you know easily, what is the lines of code to do this? I'm also
trying a gazillion ways to do this.

 

[Guest]

What happens when you open the Excel workbook directly from Windows without
having Excel open already? If there are no menus/toolbars, then I suspect
something about the setup of the workbook itself, so you might post the
question regarding the disappearing menus/toolbars on an Excel forum

In my testing, it opens correctly with menu & toolbars whether I open it
directly from Windows or via the VBA code and whether it is
password-protected or not. I don't know enough about manipulating Excel
toolbars to know why a particular sheet would open without toolbars or menus.

That said, you could record a macro in a spreadsheet and make the macro open
the toolbars & the other spreadsheet, then save this macro as Auto_Open.
Then, from your VBA code, you could open this spreadsheet with window mode 0,
which will hide it, but the macro will open the other sheet that needs to be
edited or viewed. That all seems like a lot of work just to get the menus
displayed, though.

 

[CanFlightSim]

What happens when you open the Excel workbook directly from Windows without
having Excel open already? If there are no menus/toolbars, then I suspect
something about the setup of the workbook itself, so you might post the
question regarding the disappearing menus/toolbars on an Excel forum

In my testing, it opens correctly with menu & toolbars whether I open it
directly from Windows or via the VBA code and whether it is
password-protected or not. I don't know enough about manipulating Excel
toolbars to know why a particular sheet would open without toolbars or menus.

That said, you could record a macro in a spreadsheet and make the macro open
the toolbars & the other spreadsheet, then save this macro as Auto_Open.
Then, from your VBA code, you could open this spreadsheet with window mode 0,
which will hide it, but the macro will open the other sheet that needs to be
edited or viewed. That all seems like a lot of work just to get the menus
displayed, though.

 

[CanFlightSim]

I have used the code above and Running Access 2002. I have a mapped
drive to another machine to emulate a server envronment.

I am getting a file not found error.

ExcelFile = "S:\Shared\Access\BroInfo.xls"
OpenExcel = Shell("Excel.exe " & ExcelFile, 0)

Could the mapped drive be a problem?

In addition: Can the following command be used in conjuntion with a
open file syntax?

'Shell "C:\Program Files\Microsoft Office\OFFICE11\excel.exe",
vbMaximizedFocus

Thanks ... good subject/
Will

 

[CanFlightSim]

I have used the code above and Running Access 2002. I have a mapped
drive to another machine to emulate a server envronment.

I am getting a file not found error.

ExcelFile = "S:\Shared\Access\BroInfo.xls"
OpenExcel = Shell("Excel.exe " & ExcelFile, 0)

Could the mapped drive be a problem?

In addition: Can the following command be used in conjuntion with a
open file syntax?

'Shell "C:\Program Files\Microsoft Office\OFFICE11\excel.exe",
vbMaximizedFocus

Thanks ... good subject/
Will