Each VBA sub works perfectly separately, but calling subs from another function doesn't work

Question

I wrote 2 subs to automate a daily task.

First sub MatriksFlowUpdate calls 2 other subs RightClick and SingleClick to simulate a right click and then a left click on a certain part of the screen. This is done in order to prompt another program to create an Excel file and save it under C:. This sub works correctly on its own (i.e. it simulates a right click and a left click at the desired locations on the screen, prompting another program to produce an Excel sheet)

Second sub CloseInstance finds the Excel sheet created above, and closes it. This sub also works correctly on its own.

However, when I try to call these 2 subs one after the other in another sub MainSequence, I get an error saying the Excel that should be found and closed by the second sub can't be found. So I get an error on the CloseInstance sub at the location below

    Set xlApp =GetObject("C:\MATRIKS\USER\REPORTS\EXCEL\Temp.xls").Application

I've tried many things to fix this, but I feel like I am going around in circles for the past few days. Any help would be much much appreciated.

P.S. My first time posting a q on stackoverflow so please bear with me with the formatting.

    Public Declare PtrSafe Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
    Public Declare PtrSafe Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
    Public Const MOUSEEVENTF_LEFTDOWN = &H2
    Public Const MOUSEEVENTF_LEFTUP = &H4
    Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
    Public Const MOUSEEVENTF_RIGHTUP As Long = &H10
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

    Sub MainSequence()
        'This sub pieces together MatriksFlowUpdate and CloseInstance
        Call MatriksFlowUpdate                                        
        Sleep 2000
        Call CloseInstance
        End Sub                                                        

    Sub MatriksFlowUpdate()
        'Prompts 3rd party software (Matriks) to produce Excel with latest flow data
        Call RightClick
        Call SingleClick
        End Sub

    Private Sub RightClick()
    'Simulates a mouse right click at desired screen coordinates
    Sleep 1000
    SetCursorPos 1750, 750 'x and y position
    mouse_event MOUSEEVENTF_RIGHTDOWN, 0, 0, 0, 0
    mouse_event MOUSEEVENTF_RIGHTUP, 0, 0, 0, 0
    End Sub

    Private Sub SingleClick()
    'Simulates a mouse left click at desired screen coordinates
    Sleep 1000
    SetCursorPos 1750, 650 'x and y position
    mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
    mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
    End Sub

    Sub CloseInstance()
    'Finds the instance of Excel where Matriks exported its excel and closes that instance of Excel
    Dim xlApp As Excel.Application
    Dim WB As Workbook
    Set xlApp =GetObject("C:\MATRIKS\USER\REPORTS\EXCEL\Temp.xls").Application
    Set WB = xlApp.Workbooks("Temp.xls")
    WB.Close
    End Sub

What is the other program that you are using to create the Excel spreadsheet? And what method is it using to do that? Is there a reason that this other program cannot just terminate whatever Excel process it started in order to build the sheet? — SmrtGrunt, May 30 '19 at 12:58
Hi SmrtGrunt, I am using a program called Matriks, its a local financial program. It has no Excel API or anything of the sort, that is why I need to stimulate mouse clicks on the screen where Matriks is located. Unfortunately this program does not eliminate the Excel process it started, I have to do it myself. Just to clarify, my issue is this: Sub MatriksFlowUpdate works perfectly fine when I run it on its own. But somehow it doesn't work properly when I call it from inside Sub MainSequence. I can see the mouse cursor moving to the desired locations, but Excel is not produced. — MKB, May 30 '19 at 13:19
If you don't have multiple excel instances running, you could use VBA to kill the process via shell script? See [this question](https://stackoverflow.com/questions/1790340/how-to-terminate-a-process-in-vbscript) and [this link](https://www.myonlinetraininghub.com/vba-shell). — SmrtGrunt, May 30 '19 at 13:28
I'm assuming the Matriks application is open on the screen and that you are using the mouse actions to select menu items. Is it possible to get the desired output from Matriks by adding parameters to the Matriks program from the command line? — SmrtGrunt, May 30 '19 at 13:41
No, I can't unfortunately, while a very good program, Matriks is not user friendly like that :( — MKB, May 30 '19 at 13:55
How close are you to pole vaulting over [the XKCD automation line?](https://xkcd.com/1205/) — SmrtGrunt, May 30 '19 at 14:12
:) I just did, and had a good laugh, thank you. The issue is, I need to do this so I can do other stuff, so it is really important to me to get it done, appreciate the humor tho — MKB, May 30 '19 at 20:54
That was more of a sympathy share. I've been there - spending what felt like far too long fixing what felt like it should be simple. I'm sorry that I'm out of ideas for you on this one. — SmrtGrunt, May 31 '19 at 16:14
Hey SmrtGrunt, I just solved it and posted the full answer at the very end of this thread (as a separate answer to the question). Thank yoy, both for the idea and the link :) I guess it pays to be obsessive at the end — MKB, May 31 '19 at 21:14

score 1 · Answer 1 · answered May 31 '19 at 10:16

Thanks to all your help, I was able to solve the problem as below:

as per DisplayName's suggestion, this was an Excel freeze issue when Sleep function was called. When Sleep function was called, Excel froze and blocked the 3rd party program from creating its own Excel instance.

I built on this idea and created a new function called WasteTime and added it to my code. I am using this function instead of Sleep in the code, thereby bypassing the Excel freeze problem.

Full code below now.

Please note that WasteTime sub was found on myonlinetraininghub.com

Public Declare PtrSafe Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare PtrSafe Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
Public Const MOUSEEVENTF_RIGHTUP As Long = &H10
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub MainSequence()
    'This sub pieces together MatriksFlowUpdate and CloseInstance
    Call MatriksFlowUpdate                                        
    WasteTime(2) #This is the code change, it was Sleep 2000 before
    Call CloseInstance
    End Sub                                                        

Sub MatriksFlowUpdate()
    'Prompts 3rd party software (Matriks) to produce Excel with latest flow data
    Call RightClick
    Call SingleClick
    End Sub

Private Sub RightClick()
'Simulates a mouse right click at desired screen coordinates
Sleep 1000
SetCursorPos 1750, 750 'x and y position
mouse_event MOUSEEVENTF_RIGHTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_RIGHTUP, 0, 0, 0, 0
End Sub

Private Sub SingleClick()
'Simulates a mouse left click at desired screen coordinates
Sleep 1000
SetCursorPos 1750, 650 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
End Sub

Sub CloseInstance()
'Finds the instance of Excel where Matriks exported its excel and closes that instance of Excel
Dim xlApp As Excel.Application
Dim WB As Workbook
Set xlApp =GetObject("C:\MATRIKS\USER\REPORTS\EXCEL\Temp.xls").Application
Set WB = xlApp.Workbooks("Temp.xls")
WB.Close
End Sub

Sub WasteTime(Finish As Long) #This is what I use instead of Sleep
Dim NowTick As Long
Dim EndTick As Long

EndTick = GetTickCount + (Finish * 1000)

Do
    NowTick = GetTickCount
    DoEvents
Loop Until NowTick >= EndTick

End Sub

Dmitrij Holkin · Answer 2 · 2019-05-30T13:23:52.890

0

Maybe try something like that

Sub CloseInstance()
    Dim WB As Workbook
    Set WB = Application.Workbooks("Temp.xls")
    If Not WB Is Nothing Then
        WB.Close
    End If
End Sub

Or try this to open

Sub test()
IsWorkBookOpen ("C:\MATRIKS\USER\REPORTS\EXCEL\Temp.xls")
End Sub
Sub IsWorkBookOpen(ByVal fullFileName)
Dim wBook As Workbook
If FileExists(fullFileName) Then
    On Error Resume Next
    'Test to see if a Workbook is open.
    Set wBook = Workbooks(Dir(fullFileName))
        If wBook Is Nothing Then 'Not open
            Workbooks.Open (fullFileName)
            Set wBook = Nothing
            On Error GoTo 0
        Else 'It is open
            MsgBox "Yes it is open", vbInformation, "Founded"
            Set wBook = Nothing
            On Error GoTo 0
        End If
Else
    MsgBox "File does not exists"
End If
End Sub


Function FileExists(ByVal fullFileName) As Boolean
    FileExists = VBA.Len(VBA.Dir(fullFileName)) > 0
End Function

edited May 30 '19 at 13:23

answered May 30 '19 at 13:04

Dmitrij Holkin

1,995
3
39
86

Thank you for your time Dmitrij. The issue is, I need to have that Excel open (i.e. Sub MatriksFlowUpdate needs to work correctly and open the Excel via right click + left click sequence). When I call Sub MainSequence, I can see that Sub MatriksFlowUpdate sends the mouse cursor to the right positioning on the screen, but somehow I don't get the Excel opened. I hope this clarifies my situation. Thank you again for your prompt response – MKB May 30 '19 at 13:08
Look upadated answer – Dmitrij Holkin May 30 '19 at 13:24
Hi Dmitrij, thank you, I think I shud clarify myself: I need to get MatriksFlowUpdate to work properly, so that this Excel is produced and saved. When I call MatriksFlowUpdate from MainSequence, I can see the mouse cursor moving to the desired locations and stimulating the clicks. However, No Excel is produced. When I call MatriksFlowUpdate on its own, I can again see the mouse cursor move to the desired locations, but I also get the Excel output. I don't understand why MatriksFlowUpdate acts differently as explained above when I call it solo vs when I call it from inside another Sub – MKB May 30 '19 at 13:29
just because Sleep is not working in Excel you should use `Application.Wait (Now + TimeValue("0:00:01"))` – Dmitrij Holkin May 30 '19 at 13:37
Just wrote a test sub for my sleep function and it appears to be working correctly. In fact, I am beginning to suspect that Sleep function is working so well that it is somehow freezing Excel and blocking the other program to export its Excel sheet maybe? (pls tell me if I'm starting to sound crazy) :) I've tried the code you posted above as well, it works, but still the same problem – MKB May 30 '19 at 14:12
Dmitrij, what i mean is that both Sleep function and the code you posted above do the exact same thing in my code. They both get Excel to freeze, which I believe prevents the third party program to create its own excel. Is there any way for me to get the code to delay w/o freezing my Excel completely? Thank you – MKB May 30 '19 at 17:48
Which program you trying to automate? – Dmitrij Holkin May 31 '19 at 07:04
Hi Dmitrij, I was trying to automate an external finance program called Matriks (its a local Turkish one). Your idea gave me a good start and I was able to solve my problem and posted the full working code as a separate answer below. Thank you for your time and care – MKB May 31 '19 at 21:18

score 0 · Answer 3 · answered May 30 '19 at 13:28

0

should it be a timing issue you could keep on trying and getting the Excel application until it's found (not tested):

Sub CloseInstance()
    'Finds the instance of Excel where Matriks exported its excel and closes that instance of Excel
    Dim xlApp As Excel.Application

    On Error Resume Next
    Do
        Set xlApp = GetObject("C:\MATRIKS\USER\REPORTS\EXCEL\Temp.xls").Application
        DoEvents
    Loop While xlApp Is Nothing
    xlApp.Workbooks("Temp.xls").Close
End Sub

answered May 30 '19 at 13:28

DisplayName

13,283
2
11
19

Hi, thank you. I agree with you that there is a waiting issue here. I used the code you posted above, I think it is going in the right direction, it allows the excel to be created, however, the loop gets stuck at DoEvents line forever and the code fails to close the excel. Any thoughts why this might be? Thank you – MKB May 30 '19 at 17:45
Hi DisplayName, i built on your core idea and solved my problem and posted it as a new answer at the very bottom, thank you for direction – MKB May 31 '19 at 21:16
you are welcome. you could then consider marking my answer as useful. thank you – DisplayName Jun 14 '19 at 06:02

Each VBA sub works perfectly separately, but calling subs from another function doesn't work

3 Answers3