Urgent - Closing an excel application in access

[Guest]

Hi all,

Thanks in advance.

Closing an saving an appliaction but can't close or save it without a dialog
box.

What is wrong with the code?

Dim obj As Object
On Error Resume Next

'-- Set up excel
Set obj = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
'-- Excel Was Not Running So Start New Instance
Set obj = CreateObject("Excel.Application")
End If

With obj
.Workbooks.Open FileName:="C:\39104A.xls"
'-- Set up header row
.Application.Visible = True
'select first sheet only
.Sheets(1).Select
.Columns("K:IV").Select
.Selection.Delete Shift:=xlToLeft
.workbook.Close SaveChanges:=True 'is this correct?
.workbook.Save SaveChanges:=True 'is this correct?
'.Close SaveChanges:=True 'is this correct?
.Quit 'is this
correct?

End With

obj.Close
Set obj = Nothing

Once again thanks

Trev.

PS I have tried all combinations of close, quit & save but can't get it right.

 

[strive4peace]

Hi Trevor,

I have had better success with

obj.activeworkbook.save
obj.activeworkbook.close false

why do you have 2 lines for closing the workbook -- it looks like you
have only one workbook open...

also, if you use GetObject instead of CreateObject, you should not Quit
the application (don't Close it anyway). Here is some basic code I use
for a conversation with Excel from Access:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
Function Excel_Conversation()

On Error GoTo Proc_Err

Dim xlApp As Excel.Application, _
booLeaveOpen As Boolean

'if Excel is already open, use that instance
booLeaveOpen = True

'attempting to use something that is not available
'will generate an error
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo Proc_Err

'If xlApp is defined, then we
'already have a conversation
If TypeName(xlApp) = "Nothing" Then
booLeaveOpen = False
'Excel was not open -- create a new instance
Set xlApp = CreateObject("Excel.Application")
End If

'Do whatever you want


Proc_Exit:
On Error Resume Next

If TypeName(xlApp) <> "Nothing" Then
xlApp.ActiveWorkbook.Close False
If Not booLeaveOpen Then xlApp.Quit
Set xlApp = Nothing
End If

Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " Excel_Conversation"
'comment next line after debugged
Stop: Resume

Resume Proc_Exit
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[RoyVidar]

Hi all,

Thanks in advance.

Closing an saving an appliaction but can't close or save it without a
dialog box.

What is wrong with the code?

Dim obj As Object
On Error Resume Next

'-- Set up excel
Set obj = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
'-- Excel Was Not Running So Start New Instance
Set obj = CreateObject("Excel.Application")
End If

With obj
.Workbooks.Open FileName:="C:\39104A.xls"
'-- Set up header row
.Application.Visible = True
'select first sheet only
.Sheets(1).Select
.Columns("K:IV").Select
.Selection.Delete Shift:=xlToLeft
.workbook.Close SaveChanges:=True 'is this correct?
.workbook.Save SaveChanges:=True 'is this correct?
'.Close SaveChanges:=True 'is this correct?
.Quit 'is
this correct?

End With

obj.Close
Set obj = Nothing

Once again thanks

Trev.

PS I have tried all combinations of close, quit & save but can't get
it right.

Among what you are doing, there are several possibilities of
referencing Excel implicit, which may create extra instances of
Excel in memory (check task manager).

I'm not a big fan of Resume Next, and limit it's usage. After
getobject/createobject, I'd revert to usual error handler.

Also, you're not releasing the objects.

I'm a big fan of being as explicit in all object declaration/-
instantiation and referencing when doing automation. See if the below
untested air code compiles/runs with better result.

Dim obj As Object
Dim wr As Object
Dim sh as object
' in case you only wish to quit if you create the excel app
dim fCreate as boolean

On Error Resume Next

'-- Set up excel

Set obj = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
fCreate = true
'-- Excel Was Not Running So Start New Instance
Set obj = CreateObject("Excel.Application")
End If

On Error Goto YourErrorHandler

Set wr = obj.Workbooks.Open("C:\39104A.xls")
'-- Set up header row
' .Application.Visible = True
'select first sheet only
Set sh = wr.Sheets(1)
sh.Columns("K:IV").Delete Shift:=xlToLeft
set sh = nothing
wr.save
wr.close

' or replace both the above with
' wr.close True

set wr = nothing
' perhaps only close if you created the instance ?
if fCreate then obj.Quit

set obj = nothing

 

[Guest]

Roy has the correct approach.
His only problem is his overly verbose object naming.

 

[RoyVidar]

<[email protected]>:
I'm sorry, I spotted two errors in my previous reply

I accused you wrongly of not releasing object variables, sorry.

When doing late binding, the constants of the automated application
isn't available, so it needs to be replaced with the value it
represents change

sh.Columns("K:IV").Delete Shift:=xlToLeft
to
sh.Columns("K:IV").Delete Shift:=-4159

 

[Guest]

Thanks Roy,

Where can I find a list of constants and the values they represent

 

[RoyVidar]

Thanks Roy,

Where can I find a list of constants and the values they represent

I will usually use early binding when developing, which will grant the
privilleges of the Intellisence. Then, I'll usually convert to
numeric, but keep a commented versjon

sh.Columns("K:IV").Delete Shift:=-4159 ' xlToLeft

To find the value, you can for instance use the immediate window
(ctrl+g) type

? xlToLeft

followed by Enter, or you could hit F2 to enter the object browser,
then find the (Excel) Constants class and investigate there.