How to Detect when third part application releases my WorkBook

[Aldo]

Hi there!
We have an application that export data to an MS Excel Workbook.
The workbook is based on a template I created.
When that application finish exporting the data, it pop ups a message and
releases the workbook.

What I need is to find a way (event or other way) to detect the moment that
the third part application releases the workbook, then activating a macro of
my self.

Thanks in advance for any help!
Aldo.

 

[RB Smissaert]

One thing you could do is run a function in a loop that checks if the file
is still
open:

Function FileIsOpen(strFile As String) As Boolean

Dim hFile As Long

On Error GoTo OpenError

hFile = FreeFile

Open strFile For Input Lock Read As #hFile
Close #hFile

Exit Function
OpenError:

FileIsOpen = Err.Number = 70

End Function

So you could run code like this:

'waiting loop that will keep running while file is open
Do While FileIsOpen("C:\Test.xls")

Loop

'now run your own code here


If that third party app has an API that you can access then there might be a
more efficient way.


RBS

 

[Aldo]

Hi man,
Thanks for answering.

First of all, there is no way I can insert some code into the third-part
application.
After exporting the data to the new wb, the application lives the workbook
open...
So checking if the file is open or closed won't help me...

Thanks,
Aldo.

 

[RB Smissaert]

Does that third party application have an API, so does it show in the VBE
under Tools, References?
Another way would be to look out for that popup window with the Windows API,
again in a loop.

RBS

 

[Aldo]

Could you give me some example code?
Thanks.

Does that third party application have an API, so does it show in the VBE
under Tools, References?
Another way would be to look out for that popup window with the Windows API,
again in a loop.

RBS

 

[RB Smissaert]

Something like this:

Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Sub Test()

Do While FindWindow(vbNullString, "caption of the popup window") = 0
DoEvents
Loop

'run your code here

End Sub


RBS

 

[Aldo]

Thanks man!

Something like this:

Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Sub Test()

Do While FindWindow(vbNullString, "caption of the popup window") = 0
DoEvents
Loop

'run your code here

End Sub


RBS

 

[RB Smissaert]

Did it work OK?

RBS

Thanks man!

 

[Aldo]

Yeap! ) Its working.
Below the code:
Private Sub Workbook_Open()
Dim VBEHwnd As Long
Dim workDone As Boolean

' MsgBox ("Report sent to Excel")
VBEHwnd = FindWindow(vbNullString, "Report sent to Excel")
workDone = False

'Loop untill finds Third part application pop-up message.
Do While Not workDone And VBEHwnd = 0
'Do my stuff
Sheets("DataSheet").Tab.Color = 5287936
Sheets("DataSheet").Visible = True
Sheets("DataSheet").Select
Call PTSetups: workDone = True
Loop
End Sub

Regards,
Aldo.

 

[RB Smissaert]

OK, nice and simple, well done.

RBS

Yeap! ) Its working.
Below the code:
Private Sub Workbook_Open()
Dim VBEHwnd As Long
Dim workDone As Boolean

' MsgBox ("Report sent to Excel")
VBEHwnd = FindWindow(vbNullString, "Report sent to Excel")
workDone = False

'Loop untill finds Third part application pop-up message.
Do While Not workDone And VBEHwnd = 0
'Do my stuff
Sheets("DataSheet").Tab.Color = 5287936
Sheets("DataSheet").Visible = True
Sheets("DataSheet").Select
Call PTSetups: workDone = True
Loop
End Sub

Regards,
Aldo.