Opening a Hidden Excel Window With VBA (Inconsistent Behavior)

Stang70Fastback

Registered User.
Local time
Today, 01:30
Joined
Dec 24, 2012
Messages
132
Hey guys. I have a question. I need Access to open an Excel file prior to pulling information from it, because the Excel file itself only updates when it is opened. I was able to achieve this with the code below:

'Opens a hidden Excel window.

Dim app As New Excel.Application
app.Visible = False
Dim book As Excel.Workbook
Set book = app.Workbooks.Open("S:\Operations\Assistant\Databases\BUSES.xlsx")

...blah, blah, blah, doing stuff...

'Closes the Excel window.

book.Close SaveChanges:=False
app.Quit
Set app = Nothing

This works fine if NO Excel window is already open, but if the user happens to have some file open in Excel already, my code doesn't seem to work right. The file does not open in a hidden window. Instead, it opens in the window already open, and additionally, the file is not closed afterward, which leaves the user wondering where the hell this random file came from, and they have to close it. Does anyone know how I can resolve this issue? I need this Excel file to open, but I want it to be completely hidden from the user.
 
Idea:
Create a macro in Excel workbook.
Use the open event in order to minimize the workbook (or to make it not visible) then refresh then close.
Eventually you can check from this macro if the workbook is opened from your Access DB. If not, open the workbook normally.
 
Thanks. This was a simple solution that seemed to work perfectly!

If I need to edit that workbook, is there a way to stop the Workbook_Open event from running if I want to open the workbook to edit it? I thought holding down the Shift key was supposed to do that (as it does in Access) but it doesn't seem to work!
 
It is why I wrote this:
Eventually you can check from this macro if the workbook is opened from your Access DB. If not, open the workbook normally.
How to do this ?
I haven't experience with data exchange between applications but I am pretty sure that you can check from Excel the value for a certain variable in a Access DB (maybe a value in a small table used only for this purpose).
Or you can edit, from Access, an external file (from your temp directory), Excel will read that file and, if that file will say to Excel: The DB Ask you to open the workbook then Excel will hide the workbook.
Or you can put, from Access, something in the RAM then, when open, Excel will check the RAM.

I can't explain very well because my English but I think that you understand what I mean.

Good luck !
 
Okay. It seems I am back to square 1 now. I can't use a Macro in the workbook, as it turns out, because other users do not have the appropriate permissions to run Macros in Excel. Therefore the macro does not run and the file does not hide and/or exit on its own.
I have therefore gone back to using the code shown in my OP as it does not require a macro in the Excel workbook. Is there perhaps an alternate method of opening a file that will ensure it opens in its OWN hidden window rather than in an already existing visible window?
 

Users who are viewing this thread

Back
Top Bottom