upobDaPlaya
asked on
Closing an Excel Object from MS Access
If I am looping a table and creating an Excel Spreadsheet from each row in my MS Access recordset (within MS Access ) ? Do I need to close the instance of Excel after each export or at the end of the routine ?
templatefile = mytemplate.xltx
outputfile = myexcelfile.xlsx
Set xlobj = CreateObject("Excel.Applic ation)
xlobj.workbooks.Add templatefile
Set rs = My Recordset
Start Loop
Export to outputfile
Next
Loop
rs.close
xlobj.quit
set xlobj = Nothing
templatefile = mytemplate.xltx
outputfile = myexcelfile.xlsx
Set xlobj = CreateObject("Excel.Applic
xlobj.workbooks.Add templatefile
Set rs = My Recordset
Start Loop
Export to outputfile
Next
Loop
rs.close
xlobj.quit
set xlobj = Nothing
Close files when you are done with them via .Save Close Objects when you intend to reuse the objects via another Set statement. Close Objects and aet them equal to nothing when you have no further use for the.
Stupid touchscreen phone makes me spell like an imbecile!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You must be careful with terminology
You open an Excel.Application
Then you open an Excel.Workbook
The workbook is an xls file
You add an Excel.Worksheet
You then add stuff to cells on the worksheet.
Export to outputfile can mean TransferSpreadsheet or CopyFromRecordset
How are you using the template file?
Regardless in the loop you can open a workbook from a template, set a Worksheet object to something from the template file, do stuff, save and close it.
You can then reuse the sheet and workbook objects by issuing new Set statements
When your loop is complete, you then close all objects and set them to nothing.
There's no need to close the Excel.Application object until the very end.
An Excel.Application with no workbooks open is like opening Excel and closing the blank workbook.
An Excel workbook without sheets looks like a open file that you've deleted all the sheets from.
You can issue
xlobj.Visible
xlobj.UserControl = true
and what what your loop is doing occur
You open an Excel.Application
Then you open an Excel.Workbook
The workbook is an xls file
You add an Excel.Worksheet
You then add stuff to cells on the worksheet.
Export to outputfile can mean TransferSpreadsheet or CopyFromRecordset
How are you using the template file?
Regardless in the loop you can open a workbook from a template, set a Worksheet object to something from the template file, do stuff, save and close it.
You can then reuse the sheet and workbook objects by issuing new Set statements
When your loop is complete, you then close all objects and set them to nothing.
There's no need to close the Excel.Application object until the very end.
An Excel.Application with no workbooks open is like opening Excel and closing the blank workbook.
An Excel workbook without sheets looks like a open file that you've deleted all the sheets from.
You can issue
xlobj.Visible
xlobj.UserControl = true
and what what your loop is doing occur
ASKER
Nick, then you seem to be contradicting Rey as Rey is closing the object after saving the first workbook ?
Set rs = My Recordset
Start Loop
Set xlobj = CreateObject("Excel.Applic ation)
xlobj.workbooks.Add templatefile
'copy recordset here
xlobj.activeworkbook.savea s "<path to folder>\myexcelfile.xlsx
xlobj.quit
Next
Loop
rs.close
Set rs = My Recordset
Start Loop
Set xlobj = CreateObject("Excel.Applic
xlobj.workbooks.Add templatefile
'copy recordset here
xlobj.activeworkbook.savea
xlobj.quit
Next
Loop
rs.close
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If I do it Nicks way and my loop is large..thus, lets say I create 100 individual Excel Workbooks via the Loop and CopyFrom Recordset rs then will the db crash due to memory usage since I wait to the very end to close the object or is this insignificant ?
Just want to make sure I do it efficiently from a memory perspective also...
Just want to make sure I do it efficiently from a memory perspective also...
both set of codes will work
if you set the Set xlobj = CreateObject("Excel.Applic ation) inside the loop, xlobj.quit must be inside the loop
if you set the Set xlobj = CreateObject("Excel.Applic ation) outside the loop, xlobj.quit must be outside the loop
if you set the Set xlobj = CreateObject("Excel.Applic
if you set the Set xlobj = CreateObject("Excel.Applic
It is less efficient to quit the application is the nwzt iteration of the loop will demand you just re-create it. All of this is VBA, so it will be stable enough. And even if you Quit/restart Excel - which isn't as efficient - Windows will see that you are opening/closing it and leabe the bits in RAM to be reallocated.
The question was 'is it needful to close Excel'
It is not.
The question was 'is it needful to close Excel'
It is not.
yes it is, if you are doing a lot to the excel file, not just copying a recordset, or you will experience some errors like method not allowed,
... object not set etc,,
... object not set etc,,
Rey,
No one disputes that you MUST close the Workbook objects. Having a hundred workbooks open in a single instance of Excel will undoubtably lead to grief. There is no need to flush the Excel.Application object, though.
No one disputes that you MUST close the Workbook objects. Having a hundred workbooks open in a single instance of Excel will undoubtably lead to grief. There is no need to flush the Excel.Application object, though.
ASKER
Thanks its good to have both sets of opinions because it allowed me to question each suggestion, further research, implement and access..you guys are great thx again