Excel Still running

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Hi

I have an SQL database that I ame retrieving records from,
witrh an access ADP front-end. I am trying to write
recordset results to an excel spreadsheet. I need to to
this for approx 50 recordsets and then close and quit
excel. I can get t to work if I include all the code in 1
function, but due to the number of recordsets it would be
much neater to do each recordset in it's own function, and
call each in the order required.

After I quit and set excel object to nothing, it remains
running per the task manager. Is it possible to close
excel by passing between functions etc. and qit excel, and
if so, what am I doing wrong.

Please find code below

thanks in advance
Brian

Sub ESWL_excelvalidation()

Dim objXL As Excel.Application

Dim objWKB As Excel.Workbook
Dim objSHT As Excel.Worksheet
Dim objxlname As String
Dim filesave As String
Dim batch As Integer

batch = 834
filesave = "c:\brian\testit.xls"

objxlname = "C:\Brian\Development\ESWL validation
TEMPLATE.xls"
Set objXL = New Excel.Application





objXL.Visible = True


With objXL
Set objWKB = .Workbooks.Open(objxlname)
objWKB.Application.DisplayAlerts = False
objWKB.SaveAs filename:=filesave, _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False,
CreateBackup:=False

objWKB.Sheets("cover").Select
Call ESWL_ValidationSteps(batch)
objWKB.Application.DisplayAlerts = False



objWKB.Save
.Workbooks.Close
.Application.Quit
objXL.Application.Quit



End With

Set objSHT = Nothing
Set objWKB = Nothing
Set objXL = Nothing



End Sub


Sub ESWL_ValidationSteps(batch)
Call ESWL_recordcnt(batch)
End Sub


Option Compare Database
Dim strsql As String
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Function ESWL_recordcnt(batch)

Set conn = CurrentProject.Connection

strsql = "select errorcount from tbl_eswlerrors where
errornr = '00' and batnbr = " & batch

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = conn
.Open strsql, conn, adOpenDynamic,
adLockBatchOptimistic
.MoveFirst
Worksheets("cover").Cells(4, 4) = rst
("errorcount").Value
'Range("D4").Select
'ActiveCell.FormulaR1C1 = rst
("errorcount").Value

End With

rst.Close

Set rst = Nothing
Set conn = Nothing

End Function
..
 
Hi Brian,

In my experience one has to take great care to close everything and
release all sheet,workbook and other objects before closing and
releasing the Excel application object.

In place of

[With objXL...]
objWKB.Save
.Workbooks.Close
.Application.Quit
objXL.Application.Quit
End With

Set objSHT = Nothing
Set objWKB = Nothing
Set objXL = Nothing
Click to expand...

try something like

[With objXL...]
objWKB.Save
Set objSHT = Nothing 'You don't seem to use this anywhere
objWKB.Close
set objWKB = Nothing
Do While .Workbooks.Count > 0
.Workbooks(1).Close False 'close w/o saving
Loop
.Quit
End WIth
Set objXL = Nothing



I can't understand how this line in your last procedure works:
Worksheets("cover").Cells(4, 4) = rst("errorcount").Value
Click to expand...

It looks as if you've cut some code to keep your post short. If this
procedure is creating an Excel.Workbook object, you must be sure to run
code that closes the workbook - otherwise Excel won't go away.
 
Hi john

Figured it out.

My closing code was fine. You were right wasn't using the
objsht.

yes, I did give you a cut down version of the code, as it
amounts to about 7000 lines of vba. There are about 80
functions required.


My problem was that the functions call were just using
implicit references to the excel object. eg. range.select
and "with selection". In order for this to work I had to
explicitly reference the objecject. ie. objxl.worksheets...

Thanks for your help though, got me thinking.

Cheers
-----Original Message-----
Hi Brian,

In my experience one has to take great care to close everything and
release all sheet,workbook and other objects before closing and
releasing the Excel application object.

In place of

[With objXL...]
objWKB.Save
.Workbooks.Close
.Application.Quit
objXL.Application.Quit
End With

Set objSHT = Nothing
Set objWKB = Nothing
Set objXL = Nothing
Click to expand...

try something like

[With objXL...]
objWKB.Save
Set objSHT = Nothing 'You don't seem to use this anywhere
objWKB.Close
set objWKB = Nothing
Do While .Workbooks.Count > 0
.Workbooks(1).Close False 'close w/o saving
Loop
.Quit
End WIth
Set objXL = Nothing



I can't understand how this line in your last procedure works:
Worksheets("cover").Cells(4, 4) = rst
Click to expand...
("errorcount").Value

It looks as if you've cut some code to keep your post short. If this
procedure is creating an Excel.Workbook object, you must be sure to run
code that closes the workbook - otherwise Excel won't go away.
Hi

I have an SQL database that I ame retrieving records from,
witrh an access ADP front-end. I am trying to write
recordset results to an excel spreadsheet. I need to to
this for approx 50 recordsets and then close and quit
excel. I can get t to work if I include all the code in 1
function, but due to the number of recordsets it would be
much neater to do each recordset in it's own function, and
call each in the order required.

After I quit and set excel object to nothing, it remains
running per the task manager. Is it possible to close
excel by passing between functions etc. and qit excel, and
if so, what am I doing wrong.

Please find code below

thanks in advance
Brian

Sub ESWL_excelvalidation()

Dim objXL As Excel.Application

Dim objWKB As Excel.Workbook
Dim objSHT As Excel.Worksheet
Dim objxlname As String
Dim filesave As String
Dim batch As Integer

batch = 834
filesave = "c:\brian\testit.xls"

objxlname = "C:\Brian\Development\ESWL validation
TEMPLATE.xls"
Set objXL = New Excel.Application





objXL.Visible = True


With objXL
Set objWKB = .Workbooks.Open(objxlname)
objWKB.Application.DisplayAlerts = False
objWKB.SaveAs filename:=filesave, _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False,
CreateBackup:=False

objWKB.Sheets("cover").Select
Call ESWL_ValidationSteps(batch)
objWKB.Application.DisplayAlerts = False



objWKB.Save
.Workbooks.Close
.Application.Quit
objXL.Application.Quit



End With

Set objSHT = Nothing
Set objWKB = Nothing
Set objXL = Nothing



End Sub


Sub ESWL_ValidationSteps(batch)
Call ESWL_recordcnt(batch)
End Sub


Option Compare Database
Dim strsql As String
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Function ESWL_recordcnt(batch)

Set conn = CurrentProject.Connection

strsql = "select errorcount from tbl_eswlerrors where
errornr = '00' and batnbr = " & batch

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = conn
.Open strsql, conn, adOpenDynamic,
adLockBatchOptimistic
.MoveFirst
Worksheets("cover").Cells(4, 4) = rst
("errorcount").Value
'Range("D4").Select
'ActiveCell.FormulaR1C1 = rst
("errorcount").Value

End With

rst.Close

Set rst = Nothing
Set conn = Nothing

End Function
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top