Re: Excel Crashing when opened from Access

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.Work***, 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 ***
.

Quantcast