VBA to run a second Excel session

[taigovinda]

Hi,

I am trying to automate a process, and the problem I have is that an add-in (SAP Bex 7) has a memory leak or something like that... I want to have Excel run a few Bex 7 queries and export the results. However, when I do this either manually or via code, it is fine for the first one, or maybe two or three, queries, and then bogs down quite a bit and/or fails. (Fine = less than 3 minutes, bogs down quit a bit = 45 minutes.) I am able to get around this by shutting out of Excel *completely* and then re-opening, then it performs like normal for the first query or two. I don't think I can do anything about the performance of the add-in, so I'd rather find a way to automate the work-around of opening a session, letting the code run, closing the session, opening another session, etc.

So my question: is there a way that I can automatically have Excel:

1. Open a particular book in a *new session*
2. Wait while all the code executes pursuant to that book's Open event
3. Close the new session completely
4. Loops steps 1 thru 3

Thanks for the help!

Tai

 

[Jaafar Tribak]

Hi,

I am trying to automate a process, and the problem I have is that an add-in (SAP Bex 7) has a memory leak or something like that... I want to have Excel run a few Bex 7 queries and export the results. However, when I do this either manually or via code, it is fine for the first one, or maybe two or three, queries, and then bogs down quite a bit and/or fails. (Fine = less than 3 minutes, bogs down quit a bit = 45 minutes.) I am able to get around this by shutting out of Excel *completely* and then re-opening, then it performs like normal for the first query or two. I don't think I can do anything about the performance of the add-in, so I'd rather find a way to automate the work-around of opening a session, letting the code run, closing the session, opening another session, etc.

So my question: is there a way that I can automatically have Excel:

1. Open a particular book in a *new session*
2. Wait while all the code executes pursuant to that book's Open event
3. Close the new session completely
4. Loops steps 1 thru 3

Thanks for the help!

Tai

It depends on the code you have in the Open event. How do you know when the open event code is finished ?

 

[taigovinda]

Jaafar, thanks for the response. I could have it pop up a message box at the end for me to see on the screen, but I don't know the first thing about working between sessions in Excel... The Open event will run a Bex 7 query (within Excel), then export the results to a closed .mdb file.

When the Open event is done doing what I need it to do, what do you advise in order to let the other file know that it's done? I don't know if there is a "ready state" or something that can be returned to the other session, or write something to a file, or what... Thanks again for providing your expertise on this.

Tai

 

[Jaafar Tribak]

this worked for me.

I added the following code to the target workbook open event which is basically a loop that runs for 4 seconds.

Public OpenEventCodeTerminated As Boolean

Private Sub Workbook_Open()

    Dim t As Single
    
    t = Timer
    Do
        DoEvents
    Loop Until Timer - t >= 4
    
    OpenEventCodeTerminated = True

End Sub

In the current workbook I have :

Sub OpenWorkbook()

    Dim oApp As Application
    Dim oWb As Object
    
    On Error Resume Next
    
    Set oApp = CreateObject("excel.application")
    oApp.Visible = True
    Set oWb = oApp.Workbooks.Open(ThisWorkbook.Path & "\Target.xlsm")
    
    'wait until the open event code is finished.
    Do
        DoEvents
    Loop Until oWb.OpenEventCodeTerminated
    
    MsgBox "done."
    
    oWb.Close False
    oApp.Quit

End Sub

Basically, the code wiats until the public OpenEventCodeTerminated boolean flag is set which indicates that the open event code is terminated.

See if a similar approach works for you.

 

[taigovinda]

Jaafar,

Very cool - it works perfectly!! Many thanks.

Tai

 

[taigovinda]

One more question though!! What if I have a macro, say, MyMacro, that is in the other workbook (variable oWb in your code) and is in Module1. How do I run that macro instead of having to use the open event for everything?

 

[taigovinda]

...and is there a way to make my PERSONAL.xls not open in the second session, or at least ignore the read-only message?

Sorry for so many posts, it was just so exciting that it worked so easily with your code :D

 

[Jaafar Tribak]

One more question though!! What if I have a macro, say, MyMacro, that is in the other workbook (variable oWb in your code) and is in Module1. How do I run that macro instead of having to use the open event for everything?

You can use the Run Method as follows :

1- Target Workbook in a Standard module :

Sub Module1Macro()

    MsgBox "Module1 Macro runing."

End Sub

2- Calling workbook :

Sub CallStandardModuleMacro()

    Dim oApp As Application
    Dim oWb As Object
    
    Set oApp = CreateObject("excel.application")
    Set oWb = oApp.Workbooks.Open(ThisWorkbook.Path & "\Target.xlsm")
    oApp.Run oWb.Name & "!Module1Macro"
    oApp.Quit
    Set oApp = Nothing
    Set oWb = Nothing

End Sub

As for the Personal.xls not opening in the second excel instance to avoid the Read Only prompt I don't think you can do that.

The only thing that you can do is to move all the code you have in the Personal.xls to an Addin. Addins are not loaded when using the CreateObject Method.

 

[Jaafar Tribak]

One more question though!! What if I have a macro, say, MyMacro, that is in the other workbook (variable oWb in your code) and is in Module1. How do I run that macro instead of having to use the open event for everything?

You can use the Run Method as follows :

1- Target Workbook in a Standard module :

Sub Module1Macro()

    MsgBox "Module1 Macro runing."

End Sub

2- Calling workbook :

Sub CallStandardModuleMacro()

    Dim oApp As Application
    Dim oWb As Object
    
    Set oApp = CreateObject("excel.application")
    Set oWb = oApp.Workbooks.Open(ThisWorkbook.Path & "\Target.xlsm")
    oApp.Run oWb.Name & "!Module1Macro"
    oApp.Quit
    Set oApp = Nothing
    Set oWb = Nothing

End Sub

As for the Personal.xls not opening in the second excel instance to avoid the Read Only prompt I don't think you can do that.

The only thing that you can do is to move all the code you have in the Personal.xls to an Addin. Addins are not loaded when using the CreateObject Method.

 

[taigovinda]

Jaafar,

Thanks again... it was causing so much fuss to have to manually close out of Excel and re-start the process after every ten minutes. Now I can run all the queries I want in a row!

Tai