Export To Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
How do I check if Excel is open before I run my code? I have code that
transfer data from Access to Excel. I use Excel.Application object as I have
to do complex manipulation of the data. If the excel application is already
open and modified than I get Error 91.


I would also like to export my data without having to close the open excel
application but I can't figure out how to do that?

Thanks,
MP
 
Here is the proper way to open an instance of Excel depending on whether or
not Excel is already running:

On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.

This is the sub called in the code above

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If


End Sub

As to not closing the Excel application, sorry you really have to;
otherwise, you will leave an instance of Excel running that you don't want.
It will cause problems if you run your code then try to open any other Excel
file in Windows. It will hang up on you. In this case, you will find
Excel.exe listed in the Processes tab of Task Manager.

If you use the code above to open excel, this code will only destroy the
instance of Excel if it had to create a new instance.

xlBook.Close
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
 
Thanks so much. You are a great help.

Klatuu said:
Here is the proper way to open an instance of Excel depending on whether or
not Excel is already running:

On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.

This is the sub called in the code above

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If


End Sub

As to not closing the Excel application, sorry you really have to;
otherwise, you will leave an instance of Excel running that you don't want.
It will cause problems if you run your code then try to open any other Excel
file in Windows. It will hang up on you. In this case, you will find
Excel.exe listed in the Processes tab of Task Manager.

If you use the code above to open excel, this code will only destroy the
instance of Excel if it had to create a new instance.

xlBook.Close
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Click to expand...
 
Back
Top