How to Detect when third part application releases my WorkBook
 

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.

How to Detect when third part application releases my WorkBook
 

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" wrote in message
...
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.

How to Detect when third part application releases my WorkBook
 

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" wrote:
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" wrote in message
...
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.

How to Detect when third part application releases my WorkBook
 

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" wrote in message
...
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" wrote:
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" wrote in message
...
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.

How to Detect when third part application releases my WorkBook
 

Could you give me some example code?
Thanks.


"RB Smissaert" wrote:

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" wrote in message
...
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" wrote:
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" wrote in message
...
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.

How to Detect when third part application releases my WorkBook
 

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" wrote in message
...
Could you give me some example code?
Thanks.


"RB Smissaert" wrote:

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" wrote in message
...
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" wrote:
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" wrote in message
...
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.

How to Detect when third part application releases my WorkBook
 

Thanks man!


"RB Smissaert" wrote:

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" wrote in message
...
Could you give me some example code?
Thanks.


"RB Smissaert" wrote:

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" wrote in message
...
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" wrote:
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" wrote in message
...
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.

How to Detect when third part application releases my WorkBook
 

Did it work OK?

RBS


"Aldo" wrote in message
...
Thanks man!


"RB Smissaert" wrote:

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" wrote in message
...
Could you give me some example code?
Thanks.


"RB Smissaert" wrote:

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" wrote in message
...
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" wrote:
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" wrote in message
...
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.

How to Detect when third part application releases my WorkBook
 

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" wrote:

Did it work OK?

RBS


"Aldo" wrote in message
...
Thanks man!


"RB Smissaert" wrote:

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" wrote in message
...
Could you give me some example code?
Thanks.


"RB Smissaert" wrote:

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" wrote in message
...
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" wrote:
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" wrote in message
...
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.

How to Detect when third part application releases my WorkBook
 

OK, nice and simple, well done.

RBS


"Aldo" wrote in message
...
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" wrote:

Did it work OK?

RBS


"Aldo" wrote in message
...
Thanks man!


"RB Smissaert" wrote:

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" wrote in message
...
Could you give me some example code?
Thanks.


"RB Smissaert" wrote:

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" wrote in message
...
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" wrote:
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" wrote in message
...
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.