problems closing excel
 

dear All,

Exporting data from acceess Excel-template and printout a selection
Close NoSave
First run is Ok
Second run triggers error 1004 'Application-defined or object-defined error'

What's wrong in this closing-code ????

'Print and close excel (no save)
Range("A1:K" & Trim(CStr(iRow))).Select
Selection.PrintOut Copies:=1, Collate:=True
xlApp.ActiveWorkbook.Close SaveChanges:=False
xlApp.Quit
Set xlApp = Nothing

Thanks,

Filip

problems closing excel
 

Filip,

Here are some general guidelines to use when automating Excel.

Regards,
Jim Cone
San Francisco, CA

'-----------------------------------------------------------------
1. Set a reference to the primary Excel objects used in your program.
Dim xlApp As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

Set xlApp = New Excel.Application
Set WB = xlApp.Workbooks.Add
Set WS = WB.Sheets(1)

Use the appropriate reference Every Time you make reference to a spreadsheet.
Do not use Range(xx) - use WS.Range(xx)
Cells should be WS.Cells(10, 20) or _
WS.Range(WS.Cells(10, 20), WS.Cells(20, 40))

2. Avoid the use of ActiveSheet, ActiveWorkbook, Selection etc.
Use your object references.

3. Avoid the use of the "With" construct.

4. Set all objects to Nothing in the proper order - child then parent.
Set WS = Nothing
WB.Close SaveChanges:=False 'your choice
Set WB = Nothing
xlApp.Quit
Set xlApp = Nothing

Violating any of these guidelines can leave "orphans" that still refer
to Excel and prevent the application from closing.
'------------------------------------------------------------

Articles dealing with unqualified references and automation application not quitting.

1. 178510 - PRB: Excel Automation Fails Second Time Code Runs
http:// support.microsoft.com / default.aspx?scid=kb%3ben-us%3b178510
Summary: While running code that uses Automation to control Microsoft Excel,
one of the following errors may occur: With Microsoft Excel 97 and later, you receive the error:
Run-time error '1004': Method '<name of method' of object '_Global' failed -or-...

2. 189618 - PRB: Automation Error Calling Unqualified Method or Property
http:// support.microsoft.com / default.aspx?scid=kb%3ben-us%3b189618
Summary: While running code that uses Automation to control Microsoft Word 97, Word 2000, or Word 2002,
you may receive one of the following error messages:
Run-time error '-2147023174' (800706ba) Automation error -or- Run-time error '462': The remote server...

3. 199219 - XL2000: Automation Doesn't Release Excel Object from Memory
http://support.microsoft.com/default...;en-us;q199219
When you run a macro that uses automation to create a Microsoft Excel object (instance),
the Excel object does not exit from memory when you specify.
If you create another Excel object after quitting the first, a second instance is in memory.
This problem occurs when your macro uses a "WITH" statement that refers to the automation object.

4. 319832 - INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic
http:// support.microsoft.com / default.aspx?scid=kb%3ben-us%3b319832
Summary: When you automate a Microsoft Office application,
you may receive an error message or you may experience unexpected behavior, as follows.
You may receive one of the following error messages: Error 91: Object variable or With block variable not set....
'------------------------------------------------------------

"Filips Benoit" wrote in message ...
dear All,
Exporting data from acceess Excel-template and printout a selection
Close NoSave
First run is Ok
Second run triggers error 1004 'Application-defined or object-defined error'
What's wrong in this closing-code ????
'Print and close excel (no save)
Range("A1:K" & Trim(CStr(iRow))).Select
Selection.PrintOut Copies:=1, Collate:=True
xlApp.ActiveWorkbook.Close SaveChanges:=False
xlApp.Quit
Set xlApp = Nothing
Thanks,
Filip

problems closing excel
 

Thanks, problem solved!!

"Jim Cone" wrote in message
...
Filip,

Here are some general guidelines to use when automating Excel.

Regards,
Jim Cone
San Francisco, CA

'-----------------------------------------------------------------
1. Set a reference to the primary Excel objects used in your program.
Dim xlApp As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

Set xlApp = New Excel.Application
Set WB = xlApp.Workbooks.Add
Set WS = WB.Sheets(1)

Use the appropriate reference Every Time you make reference to a
spreadsheet.
Do not use Range(xx) - use WS.Range(xx)
Cells should be WS.Cells(10, 20) or _
WS.Range(WS.Cells(10, 20), WS.Cells(20, 40))

2. Avoid the use of ActiveSheet, ActiveWorkbook, Selection etc.
Use your object references.

3. Avoid the use of the "With" construct.

4. Set all objects to Nothing in the proper order - child then parent.
Set WS = Nothing
WB.Close SaveChanges:=False 'your choice
Set WB = Nothing
xlApp.Quit
Set xlApp = Nothing

Violating any of these guidelines can leave "orphans" that still refer
to Excel and prevent the application from closing.
'------------------------------------------------------------

Articles dealing with unqualified references and automation application
not quitting.

1. 178510 - PRB: Excel Automation Fails Second Time Code Runs
http:// support.microsoft.com / default.aspx?scid=kb%3ben-us%3b178510
Summary: While running code that uses Automation to control Microsoft
Excel,
one of the following errors may occur: With Microsoft Excel 97 and
later, you receive the error:
Run-time error '1004': Method '<name of method' of object '_Global'
failed -or-...

2. 189618 - PRB: Automation Error Calling Unqualified Method or Property
http:// support.microsoft.com / default.aspx?scid=kb%3ben-us%3b189618
Summary: While running code that uses Automation to control Microsoft
Word 97, Word 2000, or Word 2002,
you may receive one of the following error messages:
Run-time error '-2147023174' (800706ba) Automation error -or- Run-time
error '462': The remote server...

3. 199219 - XL2000: Automation Doesn't Release Excel Object from Memory
http://support.microsoft.com/default...;en-us;q199219
When you run a macro that uses automation to create a Microsoft Excel
object (instance),
the Excel object does not exit from memory when you specify.
If you create another Excel object after quitting the first, a second
instance is in memory.
This problem occurs when your macro uses a "WITH" statement that refers
to the automation object.

4. 319832 - INFO: Error or Unexpected Behavior with Office Automation When
You Use Early Binding in Visual Basic
http:// support.microsoft.com / default.aspx?scid=kb%3ben-us%3b319832
Summary: When you automate a Microsoft Office application,
you may receive an error message or you may experience unexpected
behavior, as follows.
You may receive one of the following error messages: Error 91: Object
variable or With block variable not set....
'------------------------------------------------------------

"Filips Benoit" wrote in message
...
dear All,
Exporting data from acceess Excel-template and printout a selection
Close NoSave
First run is Ok
Second run triggers error 1004 'Application-defined or object-defined
error'
What's wrong in this closing-code ????
'Print and close excel (no save)
Range("A1:K" & Trim(CStr(iRow))).Select
Selection.PrintOut Copies:=1, Collate:=True
xlApp.ActiveWorkbook.Close SaveChanges:=False
xlApp.Quit
Set xlApp = Nothing
Thanks,
Filip