A Step Further with Excel...and making it go away!

[DTRNTR]

I have reviewed many of the references to openning and using an Excel spreadsheet from VB6 within this forum, but am still not able to fix the following problem:

I am reading between 100 and 35,000 lines from various Excel spreadsheets and am writing the spreadsheet data to a single Access table. For most spreadsheets, the application is working as planned (reading, writing and closing Excel). For larger spreadsheets (approximately 20,000 lines and above), all of the data is being properly read and written, however the closing of the Excel application is not occurring (I can see the Excel process remaining). I cannot seem to make this process go away (programmatically).

If I try to run this application again, it attempts to start a new occurance of Excel and then 'hangs' the application. I must then manually stop the processes for the main application and the two instances of Excel before I can successfully run the application again.

Below is the main portion of my code.

Thanks in advance for any Help you can provide!

Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
'
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application&quot
If Err Then
Err.Clear
Set xlApp = Nothing
Set xlApp = CreateObject("Excel.Application&quot
If Err <> 0 Then
MsgBox &quot;Unable to Read Spreadsheet Data&quot;, & _
vbExclamation & vbOKOnly, _
&quot;Data Import Unavailable&quot;
End
End If
End If
'
Set wb = xlApp.Workbooks.Open(txtFileName.Text)
Set ws = wb.Worksheets(1)
'
For Idx = 1 To LastRow
SSN = ws.Range(A & Idx).Value
LName = UCase(ws.Range(B & Idx).Value)
FName = UCase(ws.Range(C & Idx).Value)
DOB = ws.Range(D & Idx).Value
SqlString = &quot;Insert into DEMOGRAPHICS(SSN,&quot; & _
&quot;LastName,FirstName,BirthDate) &quot; & _
&quot;VALUES (SSN,LName,FName,DOB)&quot;
oConn.Execute (SqlString)
ProgressBar1.Value = Idx
Next Idx
'
' Clean up spreadsheet objects
'
wb.Close
xlApp.Quit
Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing

 

[winniepough]

this worked for me.

http://www.vb2themax.com/Item.asp?PageID=CodeBank&Cat=1400&ID=153

 

[malorey]

Try to set the Saved flag before you use xlApp.Quit: wb.Saved = True

 

[CodeFish]

Hi DTRNT,

You are releasing you references incorrectly. By quitting the Excel Application when you do, an instance of the workbook and worksheet object will still persist.

Should be:-

Set ws = Nothing
Set wb = Nothing
xlApp.Quit
Set xlApp = Nothing

Codefish

 

[muesli]

As you are using the Jet engine, what about attaching the Excel spreadsheet to the table using Access? You can link the table directly from the database window (or through code) then open it in your code as if it were a native table. No need to run any Excel automation code in this case, and it should be faster.

 

[DTRNTR]

Thanks everyone who responded...

I've tried several different ways and orders to releasing my object references but still have the same problem, when a large (over 20,000 line) spreadsheet is used.

I am trying to Kill the Process as Winniepough has recommended via the vb2themax code, but am unsure of how to determine the PID or handle to the object I am using????

 

[CodeFish]

DTRNTR,

Here is a list of the relevant articles on Knowledge Base.
I'm sure you answer is in one of these.

Q219151 - HOWTO: Automate Microsoft Excel from Visual Basic

Q138723 - Code to Access MS Excel Does Not Work in Version 7.0

Q153025 - Microsoft Excel 95 Doesn't Respond Correctly to
GetObject.

Q178510 - Excel Automation Fails Second Time Code Runs

Q199219 - XL2000: Automation Doesn 't Release Excel Object from Memory

Q187745 - BUG: Microsoft Excel Does Not Repaint Properly with Automation

Q134835 - Automation Error Using Excel Object in VB Procedure

Q288902 - GetObject and CreateObject Behaviour of Office
Automation Servers.

Q238610 - GetObject and GetActiveObject Can't Find a Running Office Application

Q193432 - Workbook Created via Automation Opens as Hidden

Q143461 - CreateObject and GetObject Work Differently

Q129252 - Error Creating OLE Automation Object with Microsoft Excel

Q292491 - Office Automation when Multiple Versions of Office Are Installed.

Q179824 - XL: Features and Formatting Lost Saving to Excel 5.0/95

Q210148 - ACC2000: Using Automation to Create and Manipulate an Excel Workbook

Q214388 - XL2000: Running Multiple Versions of Microsoft Excel


Codefish

 

[dsi]

One thing that I noticed is that you are combining early and late binding techniques. Plus, you should open the workbook as read-only since you are not making any modifications.

Using the GetObject and CreateObject methods, you should declare your objects as generic objects.

Dim xlApp As Object
Dim wb As Object
Dim ws As Object

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
...
Set wb = xlApp.Workbooks.Open(txtFileName.Text,False,True)
Set ws = wb.Worksheets(1)
...
wb.Close False   'Do Not Save Changes
xlApp.Quit
[\code]
To use early binding:
[code]
Dim xlApp As Object
Dim wb As Object
Dim ws As Object
...
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open(txtFileName.Text,False,True)
Set ws = wb.Worksheets(1)
...
wb.Close False   'Do Not Save Changes
xlApp.Quit
[\code]
Hope this helps...