f



Excel Crashing when opened from Access

I am using the following code (Office 2000)

Private Sub Storage_Click()

    On Error GoTo Err_Storage_Click

    Dim ExcelApp As Object
    Dim ExcelWasNotRunning As Boolean           ' Flag for final release
    Dim MyDb As Database

    On Error Resume Next
    Set ExcelApp = CreateObject("Excel.Application")
    ExcelApp.Visible = True

    If Err.Number <> 0 Then ExcelWasNotRunning = True
    Err.Clear    ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
    DetectExcel

    ' Set the object variable to reference the file you want to see.
    Set ExcelApp = GetObject("C:\Documents and Settings\Phil\My 
Documents\Access\MDB\WFYC\Storage.XLS")

    ' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the ExcelApp object reference.
    ExcelApp.Application.Visible = True
    ExcelApp.Parent.Windows(1).Visible = True

Exit_Storage_Click:
    Exit Sub

Err_Storage_Click:
    MsgBox Err.Description
    Resume Exit_Storage_Click

End Sub

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

This opens up the Excel application OK (It is using data in the database) If 
I click on pretty much anything in the Excel application it immediately 
crashes. If I click on something in Access immediately after Eccel opens, 
then return to Eccel - I get a message that th ODBC Microsoft Access Driver 
Login Failed and the data fails to update.
If I open Excel with the Access DB closed, everything works perfectly.

Would appreciate advice

Thanks

Phil


0
phil (1005)
6/28/2007 5:47:19 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

4 Replies
942 Views

Similar Articles

[PageSpeed] 57

Hi Phil,

I think your problem is that you are not invoking the Quit method of
Excel and thus leaving several instances of Excel in memory.

Try this:

Make a reference to the Excel Object library in Tools/References.  I
think for Excel 2000 it would be Excel Object Liberary 9.0.  This will
use Early binding - much more efficient and reliable than late binding
(Create Object...)

Then in your code module do this:

Sub DealWithExcel()
Dim xl As Excel.Application, wkbk As Excel.Workbook
Dim sht As Excel.WorkSheet, rng As Excel.Range
Dim DB As DAO.Database, RS As DAO.Recordset
Dim i As Integer, j As Integer
 
Set xl = New Excel.Application
set wkbk = xl.Workbooks.Open("C;\yourworkboook.xls")
set sht = wkbk.Sheets("Sheet1")
Set rng = sht.UsedRange
Set DB = CurrentDB
Set RS = DB.OpenRecordset("tableForExcelRng")
For i = 1 to rng.Rows.Count
  RS.AddNew
  For j = 1 to rng.Columns.Count
    RS(j) = rng(j)
  Next
  RS.Update
Next
RS.Close
set wkbk = Nothing
xl.quit
xl = nothing
End Sub

Note:  you need -- xl.quit -- or else you will have several instances of
Excel open in memory - which will cause Excel to crash

Rich

*** Sent via Developersdex http://www.developersdex.com ***
0
rpng123 (1034)
6/28/2007 9:49:38 PM
"Phil Stanton" <phil@stantonfamily.co.uk> wrote in message
<4683f41f$0$8749$ed2619ec@ptn-nntp-reader02.plus.net>:
> I am using the following code (Office 2000)
>
> Private Sub Storage_Click()
>
>     On Error GoTo Err_Storage_Click
>
>     Dim ExcelApp As Object
>     Dim ExcelWasNotRunning As Boolean           ' Flag for final
> release     Dim MyDb As Database
>
>     On Error Resume Next
>     Set ExcelApp = CreateObject("Excel.Application")
>     ExcelApp.Visible = True
>
>     If Err.Number <> 0 Then ExcelWasNotRunning = True
>     Err.Clear    ' Clear Err object in case error occurred.
>
> ' Check for Microsoft Excel. If Microsoft Excel is running,
> ' enter it into the Running Object table.
>     DetectExcel
>
>     ' Set the object variable to reference the file you want to see.
>     Set ExcelApp = GetObject("C:\Documents and Settings\Phil\My 
> Documents\Access\MDB\WFYC\Storage.XLS")
>
>     ' Show Microsoft Excel through its Application property. Then
> ' show the actual window containing the file using the Windows
> ' collection of the ExcelApp object reference.
>     ExcelApp.Application.Visible = True
>     ExcelApp.Parent.Windows(1).Visible = True
>
> Exit_Storage_Click:
>     Exit Sub
>
> Err_Storage_Click:
>     MsgBox Err.Description
>     Resume Exit_Storage_Click
>
> End Sub
>
> 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
>
> This opens up the Excel application OK (It is using data in the
> database) If  I click on pretty much anything in the Excel
> application it immediately  crashes. If I click on something in
> Access immediately after Eccel opens,  then return to Eccel - I get a
> message that th ODBC Microsoft Access Driver  Login Failed and the
> data fails to update. If I open Excel with the Access DB closed,
> everything works perfectly.
>
> Would appreciate advice
>
> Thanks
>
> Phil

If this is all the code, and if I understand correct, I'd try
shelling in stead of automating.

For instance using ShellExecute (check out the handy wrapper from
http://www.mvps.org/access/api/api0018.htm)

-- 
Roy-Vidar


0
6/29/2007 9:15:42 AM
Thanks Roy. Will try that

Phil
"RoyVidar" <roy_vidarNOSPAM@yahoo.no> wrote in message 
news:mn.eaa37d768781a5de.59509@yahoo.no...
> "Phil Stanton" <phil@stantonfamily.co.uk> wrote in message
> <4683f41f$0$8749$ed2619ec@ptn-nntp-reader02.plus.net>:
>> I am using the following code (Office 2000)
>>
>> Private Sub Storage_Click()
>>
>>     On Error GoTo Err_Storage_Click
>>
>>     Dim ExcelApp As Object
>>     Dim ExcelWasNotRunning As Boolean           ' Flag for final
>> release     Dim MyDb As Database
>>
>>     On Error Resume Next
>>     Set ExcelApp = CreateObject("Excel.Application")
>>     ExcelApp.Visible = True
>>
>>     If Err.Number <> 0 Then ExcelWasNotRunning = True
>>     Err.Clear    ' Clear Err object in case error occurred.
>>
>> ' Check for Microsoft Excel. If Microsoft Excel is running,
>> ' enter it into the Running Object table.
>>     DetectExcel
>>
>>     ' Set the object variable to reference the file you want to see.
>>     Set ExcelApp = GetObject("C:\Documents and Settings\Phil\My
>> Documents\Access\MDB\WFYC\Storage.XLS")
>>
>>     ' Show Microsoft Excel through its Application property. Then
>> ' show the actual window containing the file using the Windows
>> ' collection of the ExcelApp object reference.
>>     ExcelApp.Application.Visible = True
>>     ExcelApp.Parent.Windows(1).Visible = True
>>
>> Exit_Storage_Click:
>>     Exit Sub
>>
>> Err_Storage_Click:
>>     MsgBox Err.Description
>>     Resume Exit_Storage_Click
>>
>> End Sub
>>
>> 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
>>
>> This opens up the Excel application OK (It is using data in the
>> database) If  I click on pretty much anything in the Excel
>> application it immediately  crashes. If I click on something in
>> Access immediately after Eccel opens,  then return to Eccel - I get a
>> message that th ODBC Microsoft Access Driver  Login Failed and the
>> data fails to update. If I open Excel with the Access DB closed,
>> everything works perfectly.
>>
>> Would appreciate advice
>>
>> Thanks
>>
>> Phil
>
> If this is all the code, and if I understand correct, I'd try
> shelling in stead of automating.
>
> For instance using ShellExecute (check out the handy wrapper from
> http://www.mvps.org/access/api/api0018.htm)
>
> -- 
> Roy-Vidar
>
> 


0
phil (1005)
6/29/2007 9:43:20 PM
Thanks for coming back Roy. Trying to open Excel and show the data.

Seems to be OK Now. Must be something to do with re-booting the computer. 
Often cures a variety of ills

Phil

"Rich P" <rpng123@aol.com> wrote in message 
news:46842cf2$0$493$815e3792@news.qwest.net...
> Hi Phil,
>
> I think your problem is that you are not invoking the Quit method of
> Excel and thus leaving several instances of Excel in memory.
>
> Try this:
>
> Make a reference to the Excel Object library in Tools/References.  I
> think for Excel 2000 it would be Excel Object Liberary 9.0.  This will
> use Early binding - much more efficient and reliable than late binding
> (Create Object...)
>
> Then in your code module do this:
>
> Sub DealWithExcel()
> Dim xl As Excel.Application, wkbk As Excel.Workbook
> Dim sht As Excel.WorkSheet, rng As Excel.Range
> Dim DB As DAO.Database, RS As DAO.Recordset
> Dim i As Integer, j As Integer
>
> Set xl = New Excel.Application
> set wkbk = xl.Workbooks.Open("C;\yourworkboook.xls")
> set sht = wkbk.Sheets("Sheet1")
> Set rng = sht.UsedRange
> Set DB = CurrentDB
> Set RS = DB.OpenRecordset("tableForExcelRng")
> For i = 1 to rng.Rows.Count
>  RS.AddNew
>  For j = 1 to rng.Columns.Count
>    RS(j) = rng(j)
>  Next
>  RS.Update
> Next
> RS.Close
> set wkbk = Nothing
> xl.quit
> xl = nothing
> End Sub
>
> Note:  you need -- xl.quit -- or else you will have several instances of
> Excel open in memory - which will cause Excel to crash
>
> Rich
>
> *** Sent via Developersdex http://www.developersdex.com *** 


0
phil (1005)
6/29/2007 9:45:22 PM
Reply: