wait for user to close the excel

pnpfriend

I wrote a program (VB.Net) that takes the exiting file name, opens it, formats it and allowing the user to change/format the file and wait for the user to close the excel applilcation but my WaitForExcelToClose function is not working.. Can someone please help me. Dim wBooks As Excel.Workbooks Dim wBook As Excel.Workbook Dim sheets As Excel.Sheets excelApp = CreateObject("Excel.Application") wBooks = excelApp.Workbooks() wBook = wBooks.Open(filename) sheets = wBook.Worksheets ' some other functions sheets.PrintPreview() WaitForExcelToClose() excelApp.Quit() Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long Private Declare Function GetLastError Lib "kernel32" () As Long Private Declare Sub SetLastError Lib "kernel32" (ByVal dwErrCode As Long) Private Declare Function FormatMessage Lib "kernel32" Alias "FormatMessageA" (ByVal dwFlags As Long, ByVal lpSource As Integer, ByVal dwMessageId As Long, ByVal dwLanguageId As Long, ByVal lpBuffer As String, ByVal nSize As Long, ByVal Arguments As Long) As Long Private Declare Function GetWindowThreadProcessId Lib "user32" Alias "GetWindowThreadProcessId" (ByVal hwnd As Long, ByVal lpdwProcessId As Long) As Long Private Declare Function OpenProcess Lib "Kernel32.dll" (ByVal dwDesiredAccessas As Long, ByVal bInheritHandle As Long, ByVal dwProcId As Long) As Long Private Declare Function WaitForSingleObject Lib "kernel32" Alias "WaitForSingleObject" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long Private Declare Function CloseHandle Lib "kernel32" Alias "CloseHandle" (ByVal hObject As Long) As Long Public Sub WaitForExcelToClose() Dim hWnd As Long Dim buf As String hWnd = FindWindow("XLMain", excelApp.Caption) If hWnd = 0 Then buf = Space(1024) FormatMessage(FORMAT_MESSAGE_FROM_SYSTEM, 0&, GetLastError, LANG_NEUTRAL, buf, 200, 0&) Else Dim pid As Long = 0 Dim lngThread As Long Dim lngReason As Long lngThread = GetWindowThreadProcessId(hWnd, pid) Dim lngProcess As Long lngProcess = OpenProcess(SYNCHRONIZE Or PROCESS_ALL_ACCESS, True, pid) lngReason = WaitForSingleObject(lngProcess, INFINITE) CloseHandle(lngProcess) End If End Sub tha

Dave Kreskowiak

Seems like alot of work for waiting for Excel to close. Could you declare the excelApp object WithEvents and then handle the WorkbookBeforeClose event? RageInTheMachine9532

pnpfriend

how?

Dave Kreskowiak

Possibly something like this:

Dim WithEvents appExcel As Microsoft.Office.Interop.Excel.Application
Private Sub appExcel\_WorkbookBeforeClose(ByVal Wb As Microsoft.Office.Interop.Excel.Workbook, ByRef Cancel As Boolean) Handles appExcel.WorkbookBeforeClose
    ' do something here!
End Sub

I don't know if this is going to work the way you want, but... RageInTheMachine9532

pnpfriend

no but thank you for you help