Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
stopping an excel application from vb 6.0
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
stopping an excel application from vb 6.0
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 "rmvbgeek" wrote in message ... | 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. | | | *** Sent via Developersdex http://www.developersdex.com *** | Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
stopping an excel application from vb 6.0
rmvbgeek wrote ...
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. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
stopping an excel application from vb 6.0
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
stopping an excel application from vb 6.0
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. -- John johnf202 at hotmail dot com "rmvbgeek" wrote in message ... 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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stopping Zoom feature in Excel | Excel Discussion (Misc queries) | |||
Starting and Stopping excel from command line. | Excel Discussion (Misc queries) | |||
Stopping Excel macro pattern recgonition | New Users to Excel | |||
stopping backup of Excel spreadsheet | Excel Worksheet Functions | |||
macro to close excel application other than application.quit | Excel Programming |