Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Closing an Excel Object from MS Access

Posted on 2014-12-12
12
Medium Priority
?
200 Views
Last Modified: 2014-12-14
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.Application)
xlobj.workbooks.Add templatefile

Set rs = My Recordset
Start Loop
Export to  outputfile
Next
Loop

rs.close
xlobj.quit
set xlobj = Nothing
0
Comment
Question by:upobDaPlaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
12 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40497587
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40497588
Stupid touchscreen phone makes me spell like an imbecile!
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 800 total points
ID: 40497592
since you arecreating an Excel Spreadsheet from each recordset, you have to save the excel
for each recordset


Set rs = My Recordset
Start Loop
Set xlobj = CreateObject("Excel.Application)
 xlobj.workbooks.Add templatefile
 'copy recordset here
 
 xlobj.activeworkbook.saveas  "<path to folder>\myexcelfile.xlsx
 xlobj.quit  
Next
Loop

rs.close
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 26

Expert Comment

by:Nick67
ID: 40497603
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
0
 

Author Comment

by:upobDaPlaya
ID: 40497613
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.Application)
 xlobj.workbooks.Add templatefile
 'copy recordset here
 
 xlobj.activeworkbook.saveas  "<path to folder>\myexcelfile.xlsx
 xlobj.quit  
Next
Loop

rs.close
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 1200 total points
ID: 40497618
Indeed, I am.
You open the application object outside the loop, and close it after the loop completes

Set rs = My Recordset
 Set xlobj = CreateObject("Excel.Application)
 Start Loop

  xlobj.workbooks.Add templatefile
  'copy recordset here
 
  xlobj.activeworkbook.saveas  "<path to folder>\myexcelfile.xlsx

 Next
 Loop

  xlobj.quit  

 rs.close
0
 

Author Comment

by:upobDaPlaya
ID: 40497861
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...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40497970
both set of codes will work

if you set the  Set xlobj = CreateObject("Excel.Application) inside the loop,  xlobj.quit  must be inside the loop


if you set the  Set xlobj = CreateObject("Excel.Application)  outside the loop,  xlobj.quit  must be outside the loop
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40498095
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40498123
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,,
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40498138
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.
0
 

Author Closing Comment

by:upobDaPlaya
ID: 40499602
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
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question