Excel macro runs fine when run through excel but error appears when run from access module

[TobyMace]

Hi All,

I have a module run from access that does a few things then opens an excel file and runs a macro on that excel spreadsheet. Running the excel macro from both excel and access worked perfectly fine. I realised that one of the excel files that will be opened as part of the excel macro may already be open by the user (it's only one user that will have the file open so don't need to worry about others having it open) when the code is run. So I tried to add in a bit of code which would close that workbook at the beginning of the excel macro and the rest will run after that workbook has closed.
I went for this method:

Workbooks("Invoice Register COPY.xls").Activate
ActiveWorkbook.Close

This works perfectly fine when I just open the excel file and run the macro manually through that. But as soon as I go to run the macro through the access module it will give me "Subscript out of range" on the first line of the above code.
The file is definitely spelt correctly as I copied the name when the file is referenced later in the code (and works fine). The file is definitely open when I try to run the code.
I've tried the whole file path and file name with and without the file extension and tried just the filename with and without extension but no luck.
I have also tried closing the workbook in the access module but was getting the same error message no matter which method I used to try and accomplish it.
Any suggestions would be appreciated and many thanks in advance.

 

[Gasman]

I think you would need to qualify the excel object? when code in Access

 

[TobyMace]

The below is the code in my access module. It opens the excel file containing the macro perfectly fine.

Dim XL As Object
    Set XL = CreateObject("Excel.Application")
    
    With XL

        .Visible = False
        .displayalerts = False
 
        .Workbooks.Open path
        
        .Visible = True

        .Run "Invoice" 'Code stops here!
    End With

Where path has been defined above and is not the same as the file I am trying to close. "invoice" is the name of the excel macro.

 

[arnelgp]

Dim XL As Object
Dim WB As Object
    Set XL = CreateObject("Excel.Application")
  
    With XL

        .Visible = False
        .displayalerts = False

        Set WB=.Workbooks.Open(path)
      
        .Visible = True

        .Run WB.Name & "!Module1.Invoice" 'Code stops here!
        WB.Close False
    End With

 

[Gasman]

Code works fine for me, though my macro is a simple MSGBOX statement?

Sub ShowMSG()
Dim XL As Object
Dim Path As String

Path = "c:\Users\Paul\documents\gcd\gcd.xlsm"

    Set XL = CreateObject("Excel.Application")
   
    With XL

        .Visible = False
        .DisplayAlerts = False

        .Workbooks.Open Path
       
        .Visible = True

        .Run "ShowMSG" 'Code does not stop here!
    End With
End Sub

 

[TobyMace]

Code works fine for me, though my macro is a simple MSGBOX statement?

Sub ShowMSG()
Dim XL As Object
Dim Path As String

Path = "c:\Users\Paul\documents\gcd\gcd.xlsm"

    Set XL = CreateObject("Excel.Application")
  
    With XL

        .Visible = False
        .DisplayAlerts = False

        .Workbooks.Open Path
      
        .Visible = True

        .Run "ShowMSG" 'Code does not stop here!
    End With
End Sub

That's what's strange! It's quite an extensive excel macro with a number of tasks being run and goes without a problem. It even opens and closes other excel files without a problem later in the code. It is purely when I try to close a specific file (that will already be open at the time of running the code) at the beginning of the macro is stops working but only when run from access. Running from excel makes it run as expected.

 

[Gasman]

Perhaps that macro needs to be fully qualified?

Why not put that code into access and step through it? That should find the problem, surely?
Have you tried arnlegp's solution?

Edit: I *think* it is because that file is not part of your excel session.? It belongs to another session?
If you open it within your session, then it is available to close, else not known?

 

[TobyMace]

Dim XL As Object
Dim WB As Object
    Set XL = CreateObject("Excel.Application")
 
    With XL

        .Visible = False
        .displayalerts = False

        Set WB=.Workbooks.Open(path)
     
        .Visible = True

        .Run WB.Name & "!Module1.Invoice" 'Code stops here!
        WB.Close False
    End With

I might be being dumb here... it's now saying it can't find that macro as it thinks the file name is part of the macro name.

 

[Isaac]

i All,

I have a module run from access that does a few things then opens an excel file and runs a macro on that excel spreadsheet. Running the excel macro from both excel and access worked perfectly fine. I realised that one of the excel files that will be opened as part of the excel macro may already be open by the user (it's only one user that will have the file open so don't need to worry about others having it open) when the code is run. So I tried to add in a bit of code which would close that workbook at the beginning of the excel macro and the rest will run after that workbook has closed.
I went for this method:

Workbooks("Invoice Register COPY.xls").Activate
ActiveWorkbook.Close

This works perfectly fine when I just open the excel file and run the macro manually through that. But as soon as I go to run the macro through the access module it will give me "Subscript out of range" on the first line of the above code.
The file is definitely spelt correctly as I copied the name when the file is referenced later in the code (and works fine). The file is definitely open when I try to run the code.
I've tried the whole file path and file name with and without the file extension and tried just the filename with and without extension but no luck.
I have also tried closing the workbook in the access module but was getting the same error message no matter which method I used to try and accomplish it.
Any suggestions would be appreciated and many thanks in advance.

This whole thing happened because of using Select, Selection, Active* or .Activate in Excel VBA, which should never be done - period.

Google the topic, many articles written.

You're not alone, I spent the first year of my Excel development career doing that kind of stuff, and when it finally blows up, it's reeeeeally hard to troubleshoot. Because the errors will be all kinds of things that make no sense and often can't be reproduced by the developer, who is running the code in a very neat, tidy environment (unlike your users, who are doing all kinds of things and have other workbooks open).

To be successful over any considerable length of time and variety of projects you will need to learn how to fully declare every "level" that's involved - Workbook, Worksheet, and Range.....and add Application to that if you're automating Excel from somewhere else, like Access.....every time.

You might find a way to temporarily 'solve' the problem you have now without taking this advice, but I promise you, doing these things is the reason why it's happening, regardless. And not doing those things is the long-term solution

PS -
1. If you need to automate Excel from Access, I recommend almost always creating a new Excel application object, and do everything from there.
2. To check if a user has the Excel file open, just check for the existence of the ~ temp file that occurs when any excel file is open. If it does not exist, you're good. If it does exist, try to delete it. If you can, you're good. If you can't, the user definitely has the file open - and you should NOT attempt to do anything to it at that point, just notify them and exit.
3. Once you take this approach, you'll almost always Set workbook variables by using the ApplicationVariable.Workbooks.Open(path) method.

 

[Gasman]

@Isaac
But would any open files in other excel sessions, be visible/accessible to the automated session?

 

[Isaac]

@Isaac
But would any open files in other excel sessions, be visible/accessible to the automated session?

They shouldn't, all else being equal.

I just tried opening an Excel workbook, then running some access code that opened a new instance of excel and debug.printed the Name of every item in the newly opened excel application's Workbooks collection. Nothing printed.

OP also might try removing the Application.DisplayAlerts=False
Often this is needed, but, it also means Excel might be absolutely NEEDING to display a question - try running without that and see if there is a popup asking the user for some intervention.

There are times when Excel absolutely MUST ask the user a question or display a modal warning. Like if you try to Workbook.Close while the cursor is in the editing (f2) of a cell. In that scenario, (for example), all the Application.DisplayAlerts=False's in the world will not close that workbook.
Just a random example.

The far better method here is to simply check if the file is open (using the ~ temp file's existence) and just stop if it is.

But with all this select and activate stuff, this will be just one in a long string of problems anyway

 

[arnelgp]

I might be being dumb here... it's now saying it can't find that macro as it thinks the file name is part of the macro name.

which Module did you write/put the macro?

it can be in Module2 or ThisWorkbook?

 

[Gasman]

I just tried opening an Excel workbook, then running some access code that opened a new instance of excel and debug.printed the Name of every item in the newly opened excel application's Workbooks collection. Nothing printed.

I did the same and got the same error the O/P got.

Perhaps using the method posted here might work?

 

[The_Doc_Man]

They shouldn't, all else being equal.

Open files will be visible through their file locks, which will interfere when a user attempts to open the already-opened file, depending on how the files are opened. IF opened through VBA "OPEN X AS FILE N" syntax, then the ALLOW/DENY clauses to allow or deny READ or WRITE will govern how the attempted sharing works.

If the files are opened by Excel through some other method, the odds are that Excel opened them with an effective "DENY ALL" option, which will pretty well bollix up the works. In that case, open files will be visible through their locks.

 

[TobyMace]

This whole thing happened because of using Select, Selection, Active* or .Activate in Excel VBA, which should never be done - period.

Google the topic, many articles written.

You're not alone, I spent the first year of my Excel development career doing that kind of stuff, and when it finally blows up, it's reeeeeally hard to troubleshoot. Because the errors will be all kinds of things that make no sense and often can't be reproduced by the developer, who is running the code in a very neat, tidy environment (unlike your users, who are doing all kinds of things and have other workbooks open).

To be successful over any considerable length of time and variety of projects you will need to learn how to fully declare every "level" that's involved - Workbook, Worksheet, and Range.....and add Application to that if you're automating Excel from somewhere else, like Access.....every time.

You might find a way to temporarily 'solve' the problem you have now without taking this advice, but I promise you, doing these things is the reason why it's happening, regardless. And not doing those things is the long-term solution

PS -
1. If you need to automate Excel from Access, I recommend almost always creating a new Excel application object, and do everything from there.
2. To check if a user has the Excel file open, just check for the existence of the ~ temp file that occurs when any excel file is open. If it does not exist, you're good. If it does exist, try to delete it. If you can, you're good. If you can't, the user definitely has the file open - and you should NOT attempt to do anything to it at that point, just notify them and exit.
3. Once you take this approach, you'll almost always Set workbook variables by using the ApplicationVariable.Workbooks.Open(path) method.

Hi Isaac,

Thanks very much for your feedback and apologies for the late reply. I'll certainly look into this. At face value, getting rid of select/activate etc. seems quite daunting but I bow down to superior knowledge on the subject and I'll have a gander.

Thanks again.

 

[TobyMace]

which Module did you write/put the macro?

it can be in Module2 or ThisWorkbook?

It's in Module1.
Sub Invoice()