stopping an excel application from vb 6.0

[rmvbgeek]

Help!!!!!
I have an application that loads an excel spreadsheet and transfers the
data from it to a sql server database. This part of the program works
wonderfully.
The problem is that the excel spreadsheet does not close even after the
application is shut down.
It continues to run in the background and keeps the worksheet open.
Here is the code I use to load the excel spreadsheet, it is activated by
a click event from a button.

Dim EXELAPT As excel.Application
USERFILE = "F:\SPECS\PRODUCTION\" & intfindno & ".XLS"
'USERFILE = "F:\SPECS\PRODUCTION\" & Me!FINDNO.Value & ".XLS"
ROW = "B179"

Set EXELAPT = CreateObject("EXCEL.APPLICATION")

Set MYBOOK = Workbooks.Open(FileName:=USERFILE)
EXELAPT.Workbooks.Open USERFILE
EXELAPT.Visible = True
EXELAPT.Worksheets("shop spec").Select

EXELAPT.Range(ROW).Select
EXELAPT.ActiveCell.FormulaR1C1 = "=NUMBER"
If IsError(ActiveCell) = True Then GoTo 2
adosecondaryrs.Fields("spec_list_job") = Range(ROW)

After all of the data is transfered I update the recordset and then
close the excel application. This works for all the next spreadsheets
that I open but not for the first one.

Here is the code I use to close the excel application:

Set EXELAPT = Nothing
Set MYBOOK = Nothing
2770 EXELAPT.Application.Quit
EXELAPT.Application.Kill
excel.Application.Quit
excel.Application.Kill

Set EXELAPT = Nothing
Set MYBOOK = Nothing

I had the same problem in an Access aplication I wrote using vba but
resolved the problem by inserting an "end" statement.
I don't want to do that here, because I do not want to terminate the vb
application.

Also is there any way to load an excel spreadsheet as a file and not as
a spreadsheet? This might fix the problem out right.

Thank you for your time, it is much appreciated.

 

[Jim Rech]

How can you set a variable to nothing and then use it?:

Set EXELAPT = Nothing
Set MYBOOK = Nothing
2770 EXELAPT.Application.Quit
EXELAPT.Application.Kill
excel.Application.Quit

Where is "excel" defined? "Kill" is a VB function that deletes files not a
method of the Excel object model.

I would think this is be sufficient:

For Each WB in EXELAPT.Workbooks
WB.Close False
Next
EXELAPT.Quit
Set EXELAPT = Nothing
Set MYBOOK = Nothing

--
Jim Rech
Excel MVP
| Help!!!!!
| I have an application that loads an excel spreadsheet and transfers the
| data from it to a sql server database. This part of the program works
| wonderfully.
| The problem is that the excel spreadsheet does not close even after the
| application is shut down.
| It continues to run in the background and keeps the worksheet open.
| Here is the code I use to load the excel spreadsheet, it is activated by
| a click event from a button.
|
| Dim EXELAPT As excel.Application
| USERFILE = "F:\SPECS\PRODUCTION\" & intfindno & ".XLS"
| 'USERFILE = "F:\SPECS\PRODUCTION\" & Me!FINDNO.Value & ".XLS"
| ROW = "B179"
|
| Set EXELAPT = CreateObject("EXCEL.APPLICATION")
|
| Set MYBOOK = Workbooks.Open(FileName:=USERFILE)
| EXELAPT.Workbooks.Open USERFILE
| EXELAPT.Visible = True
| EXELAPT.Worksheets("shop spec").Select
|
| EXELAPT.Range(ROW).Select
| EXELAPT.ActiveCell.FormulaR1C1 = "=NUMBER"
| If IsError(ActiveCell) = True Then GoTo 2
| adosecondaryrs.Fields("spec_list_job") = Range(ROW)
|
| After all of the data is transfered I update the recordset and then
| close the excel application. This works for all the next spreadsheets
| that I open but not for the first one.
|
| Here is the code I use to close the excel application:
|
| Set EXELAPT = Nothing
| Set MYBOOK = Nothing
| 2770 EXELAPT.Application.Quit
| EXELAPT.Application.Kill
| excel.Application.Quit
| excel.Application.Kill
|
| Set EXELAPT = Nothing
| Set MYBOOK = Nothing
|
| I had the same problem in an Access aplication I wrote using vba but
| resolved the problem by inserting an "end" statement.
| I don't want to do that here, because I do not want to terminate the vb
| application.
|
| Also is there any way to load an excel spreadsheet as a file and not as
| a spreadsheet? This might fix the problem out right.
|
| Thank you for your time, it is much appreciated.
|
|
|
| Don't just participate in USENET...get rewarded for it!

 

[Jamie Collins]

I have an application that loads an excel spreadsheet and transfers the
data from it to a sql server database. This part of the program works
wonderfully.
The problem is that the excel spreadsheet does not close even after the
application is shut down.

This can be done without the overhead of automating Excel by instead
using Jet's INSERT INTO..SELECT syntax.

Establish a connection to a Jet source (a .mdb., .xls, etc) using the
database access technology of you choice (ADO, MS Query, etc) and an
appropriate driver/provider (e.g. OLEDB Provider for Jet 4.0). Use sql
syntax similar to the following:

INSERT INTO
[odbc;Driver={SQL Server};Server=MYSERVER;Database=MYDATABASE;User
ID=MYLOGON;pwd=MYPASSWORD;].MyTable
(MyTextCol, MyIntCol)
SELECT
MyCol1 AS MyTextCol,
MyCol2 AS MyIntCol
FROM
[Excel 8.0;HDR=YES;Database=C:\MyWorkbook.xls;].[MyWorksheet$]
WHERE
MyCol2 > 55
;

If my connection was to MyWorkbook.xls itself, I could omit the
connection string in the query. However, as you are querying multiple
workbooks, you would benefit from connecting to another Jet source
e.g. you would only need to open one connection. If you are updating
the same table each time, you may benefit from using UNION to create
one data set to pass to SQL Server e.g. to eliminate duplicates.

Jamie.

--

 

[rmvbgeek]

Thank you for your time,
I tried what you said and it does release the excel spreadsheets that I
import the second and third time, though it does not stop excel from
running in the background and still holds on to the first spreadsheet
that I import.
Thanks, it is much appreciated.

 

[jaf]

Hi,
Try changing...
adosecondaryrs.Fields("spec_list_job") = Range(ROW)
to...
adosecondaryrs.Fields("spec_list_job") = exelap.Range(ROW)

Your also using "ROW" as a VB variable. "Row" is an Excel public object and
your going to confuse Excel & VB. Use MyRow or something like it.