Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OLE Automation and Mutliple Office Releases | Excel Programming | |||
How to detect another application | Excel Programming | |||
Procedure to detect if an application is running | Excel Programming | |||
Procedure to detect a running application and to register it. | Excel Programming | |||
How to hide a workbook and to detect a hidden workbook in visual basic | Excel Programming |