-
May 25th, 2017, 11:14 PM
#1
Thread Starter
Member
[RESOLVED] closing excel problem
I have a vb6 program that opens a csv file, extracts data from it and then adds that data to a spreadsheet and updates a chart with the added data.
The essence of the program is:
Code:
Sub main()
Dim XL As Excel.Application
'start excel
Set XL = CreateObject("Excel.Application")
' open csv file
Workbooks.Open FileName:=xlFilename
sheetName = ActiveSheet.Name
Worksheets(sheetName).Activate
' extract data
' close csv file
ActiveWorkbook.Close savechanges:=True
XL.Quit
'start excel again
Set XL = CreateObject("Excel.Application")
' open xlsm
Workbooks.Open FileName:=xlFilename
sheetName = ActiveSheet.Name
' write data to xlms
'.......
'update a chart
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Values = "=Summary!$D$2:$D$" & lastrow + 1
ActiveChart.SeriesCollection(2).Values = "=Summary!$B$2:$B$" & lastrow + 1
ActiveChart.SeriesCollection(2).XValues = "=Summary!$A$2:$A$" & lastrow + 1
' close xlsm and save changes
ActiveWorkbook.Close savechanges:=True
End Sub
The problem I have is that, although the program does exactly what I want, when the program closes, sometimes an instance of excel is left running. I can only see this instance in Task Manager and I have to end this process before I can run the program again.
I admit that the way I open and close the excel application could probably be done much more effectively but I have tried all sorts of variations and cannot get the right way. This version works most of the time but as I said only sometimes it does not close excel on exit. I have noticed that if I delete the 4 lines re the chart, the program ends properly (ie no instance of excel left running) more often than when I update the chart.
Can anyone offer a reason why the program acts differently on exit sometimes?
Also, if there are better ways to open and close excel than I am doing, I would appreciate knowing them.
Thanks
-
May 26th, 2017, 04:47 AM
#2
Re: closing excel problem
That is a common issue and has been asked and resolved many times at this forum. In the upper right hand corner is a search field. Enter "close excel" and you will get a number of hits with the solution.
This is also very helpful:
http://www.vbforums.com/showthread.p...6-(or-VB5-VBA)
Please remember next time...elections matter!
-
May 26th, 2017, 05:27 AM
#3
Re: closing excel problem
'start excel again
Set XL = CreateObject("Excel.Application")
there is no need to start a second instance of excel, just use the original instance again, instead of quitting it, also you are not quitting the second instance
avoid working with active sheets, charts or books, use fully qualified ranges
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 26th, 2017, 09:44 AM
#4
Re: closing excel problem
Hi hunter55 ,
I do pretty much exactly what you do all the time. However, there's one extra step that you're skipping that I always do. It's actually close the Excel application. Here's the code to do that:
Or, said differently, here's a somewhat complete set of open/close code for doing Excel automation:
Code:
Sub Main()
Dim xls As Object
Dim wbk As Object
Dim wsh As Object
Dim sFileSpec As String
sFileSpec = "c:\MyWorkbook.xls"
Set xls = CreateObject("Excel.Application")
Set wbk = xls.workbooks.Open(sFileSpec)
Set wsh = wbk.Worksheets("Sheet1")
' Do my work ....
ExcelSaveAs wbk, sFileSpec
wbk.Close False
xls.Quit
Set wsh = Nothing
Set wbk = Nothing
Set xls = Nothing
End Sub
Sub ExcelSaveAs(wbk As Object, sFileSpec As String, Optional bForceOldFormat As Boolean = True)
Const xlExcel8 = 56&
Const xlOpenXmlWorkbookMacroEnabled = 52&
'
If bForceOldFormat Then
If Int(wbk.Application.Version) > 11 Then ' It's Office 2007 or greater.
wbk.CheckCompatibility = False
wbk.SaveAs sFileSpec, xlExcel8
Else
wbk.SaveAs sFileSpec
End If
Else
If UCase$(Right$(sFileSpec, 4)) = "XLSM" Then
wbk.SaveAs sFileSpec, xlOpenXmlWorkbookMacroEnabled
Else
wbk.SaveAs sFileSpec
End If
End If
End Sub
You don't absolutely need the Set ? = Nothing statements, but if the code isn't immediately going out of scope for those object variables, it's a good idea. Also, I just declared them all as "Object" because I do Automation with late-bound variables. If you have an Excel reference, you can certainly do it with early-binding.
Again, you missed the "xls.Quit" step is why Excel stayed loaded.
Good Luck,
Elroy
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
May 27th, 2017, 08:01 PM
#5
Thread Starter
Member
Re: closing excel problem
Thanks for your replies - I am giving them a try. I still haven't solved my problem but I will work thru the tutorial and hopefully I can work out a solution.
How do I mark this as resolved?
-
May 27th, 2017, 08:02 PM
#6
Thread Starter
Member
Re: [RESOLVED] closing excel problem
Re resolved, I have just found where to do this!
-
May 28th, 2017, 08:08 AM
#7
Re: closing excel problem
Originally Posted by hunter55
Thanks for your replies - I am giving them a try. I still haven't solved my problem but I will work thru the tutorial and hopefully I can work out a solution.
How do I mark this as resolved?
If your query is not resolved then you do not need to mark it as resolved
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 28th, 2017, 08:17 AM
#8
Re: [RESOLVED] closing excel problem
I have not tested this but try this. This will not leave an instance.
Code:
Sub main()
Dim oXLApp As Object, oXLWB As Object, oXLSht As Object
Dim QuitIt As Boolean
'~~> Establish an EXCEL application object
'~~> Check if an excel application is open
'~~> If not, then create an instance
On Error Resume Next
Set oXLApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set oXLApp = CreateObject("Excel.Application")
QuitIt = True
End If
Err.Clear
On Error GoTo 0
'~~> Open csv file
Set oXLWB = Workbooks.Open(Filename:=xlFilename)
Set oXLSht = oXLWB.Sheets(1)
With oXLSht
'
'~~> Extract Data
'
End With
'~~> Close csv file
oXLWB.Close savechanges:=False
'~~> Open xlsm
Set oXLWB = Workbooks.Open(Filename:=xlFilename)
Set oXLSht = oXLWB.Sheets(1)
'~~> write data to xlms
'.......
'~~>update a chart
With oXLSht
.ChartObjects("Chart 1").Activate
.SeriesCollection(1).Values = "=Summary!$D$2:$D$" & lastrow + 1
.SeriesCollection(2).Values = "=Summary!$B$2:$B$" & lastrow + 1
.SeriesCollection(2).XValues = "=Summary!$A$2:$A$" & lastrow + 1
End With
'~~> Close xlsm and save changes
oXLWB.Close savechanges:=True
'~~> Quit Excel if you created the instance and flush the toilet
If QuitIt = True Then
oXLApp.Quit
Set oXLSht = Nothing
Set oXLWB = Nothing
Set oXLApp = Nothing
End If
End Sub
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 29th, 2017, 07:38 PM
#9
Thread Starter
Member
Re: [RESOLVED] closing excel problem
Thanks for that Siddarth, I had come to the same sort of solution too. However, I found that if I make excel visible after opening, (oXLApp.Visible = True) then a instance of excel remained when the program finished. Setting oXLApp.Visible = False before closing excel, still left excel running. If I remove oXLApp.Visible = True from the code, everything is OK!
-
May 29th, 2017, 10:27 PM
#10
Fanatic Member
Re: [RESOLVED] closing excel problem
FWIW: This is what I determined:
'If User Closes Excel other than from this App
'Excel is still running in the background, hence
'oxlApp object reference is still valid
'SETTING EXCEL NOT VISIBLE PRIOR TO QUIT
'IS THE KEY TO THE LEFT OVER INSTANCE PROBLEM
oxlApp.Visible = False
oxlApp.Quit
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|