Automating Excel

L

Larry

I recently converted from Windows NT (and Access '97) to
Windows XP (and Access 2002). I have existing code that
worked fine in Access '97 that opens an Excel spreadsheet
using GetObject, makes some changes, and then closes the
spreadsheet using .Quit.

I'm finding in Access 2002 that the .Quit command still
leaves Excel running (I can see this in Task Manager) and
if I try to reopen that spreadsheet I get a message that
it is already in use.

Any ideas why Excel is not being closed properly anymore
with the .Quit command?

Thanks in advance,
Larry
 
K

Ken Snell [MVP]

Likely your code is creating an object that refers to the EXCEL file,
worksheet, or range and you're not closing that object before you try to
quit EXCEL. This creation can be subtle and not immediately obvious.

Post the code that you're using and let's see.
 
L

Larry

Here are the key parts of the code:

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
....

Set objXLBook = GetObject(mState.strFilePath)
Set objXLApp = objXLBook.Parent
....

With objXLBook.Worksheets(1)
MANIPULATE SPREADSHEET HERE...
End with

objXLBook.Windows(1).Visible = True
objXLBook.Save
objXLApp.Quit
Set objXLBook = Nothing
Set objXLApp = Nothing


Thanks for your help, Ken!
Larry
 
K

Ken Snell [MVP]

First, I'd set the objXLBook object to Nothing before you quit the
application.

Second, the most likely location of the subtle object creation is in the
worksheet manipulation code, which you didn't post.
 
L

Larry

Ken,

Here's the entire code. I tried your first suggestion and
it didn't work.

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim intCtr As Integer
Dim ExcelAlreadyOpen As Boolean
Dim varX As Variant
Dim lngMeterCtr As Long

ExcelAlreadyOpen = pfCheckExcel

'system.StatusMessage = "Formatting spreadsheet. Please
wait ... "

Set objXLBook = GetObject(mState.strFilePath)
Set objXLApp = objXLBook.Parent

' Make sure both Excel and the workbook are visible (they
won't
' be if Excel was launched by our Automation request)
'objXLApp.Visible = true
'objXLBook.Windows(1).Visible = true

With objXLBook.Worksheets(1)
varX = SysCmd(acSysCmdInitMeter, "Formatting
spreadsheet ", .UsedRange.Rows.Count)

intCtr = 2 'start at 2nd row of spreadsheet to avoid
headers.
Do Until intCtr = .UsedRange.Rows.Count + 1
lngMeterCtr = lngMeterCtr + 1
varX = SysCmd(acSysCmdUpdateMeter, lngMeterCtr)

'Cert ID #
.Range("AB" & intCtr).Value = " " & .Range("AB" &
intCtr).Value
.Range("AB" & intCtr).Value = Right(.Range("AB" &
intCtr).Value, Len(.Range("AB" & intCtr).Value) - 1)
'Underlyer GL NAIC
.Range("AX" & intCtr).Value = " " & .Range("AX" &
intCtr).Value
.Range("AX" & intCtr).Value = Right(.Range("AX" &
intCtr).Value, Len(.Range("AX" & intCtr).Value) - 1)
'Underlyer Auto NAIC
.Range("BA" & intCtr).Value = " " & .Range("BA" &
intCtr).Value
.Range("BA" & intCtr).Value = Right(.Range("BA" &
intCtr).Value, Len(.Range("BA" & intCtr).Value) - 1)
'Underlyer Emp Liability NAIC
.Range("BL" & intCtr).Value = " " & .Range("BL" &
intCtr).Value
.Range("BL" & intCtr).Value = Right(.Range("BL" &
intCtr).Value, Len(.Range("BL" & intCtr).Value) - 1)
'Underlyer Other 1 NAIC
.Range("BP" & intCtr).Value = " " & .Range("BP" &
intCtr).Value
.Range("BP" & intCtr).Value = Right(.Range("BP" &
intCtr).Value, Len(.Range("BP" & intCtr).Value) - 1)
'Underlyer Other 2 NAIC
.Range("BT" & intCtr).Value = " " & .Range("BT" &
intCtr).Value
.Range("BT" & intCtr).Value = Right(.Range("BT" &
intCtr).Value, Len(.Range("BT" & intCtr).Value) - 1)
intCtr = intCtr + 1
Loop
End With

objXLBook.Windows(1).Visible = True
objXLBook.Save
objXLBook.Close
'If ExcelAlreadyOpen Then
'just close workbook
' objXLBook.Close
'Else
'close excel.
' objXLApp.Quit
'End If
If ExcelAlreadyOpen = False Then
objXLApp.Quit
End If
'Set objQuerySheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

End Sub


THANKS AGAIN!!
 
K

Ken Snell [MVP]

Because you're creating objXLApp via the Parent property of objXLBook, my
guess is that the code isn't actually closing the workbook that is
objXLBook's target because objXLApp is still open. If this isn't it, I'm not
seeing anything else obvious.

Why not use this variation and see if it eliminates the problem:

Dim objXLApp As Object
Dim objXLBook As Object
...
...
...
On Error Resume Next
Set objXLApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set objXLApp = CreateObject("Excel.Application")
ExcelAlreadyOpen = False
Else
ExcelAlreadyOpen = True
End If
Set objXLBook = objXLApp.Workbooks("PathFileString")
...
...
...
objXLApp.Visible = True
objXLBook.Save
objXLBook.Close
Set objXLBook = Nothing
If ExcelAlreadyOpen = False Then
objXLApp.Quit
End If
Set objXLApp = Nothing
 
L

Larry

Ken,

I tried this suggestion and i'm getting a 429 - "Object
doesn't support this property or method" error code with
the GetObject(, "Excel.Application") line. Am I missing a
reference or something?

thanks,
Larry
 
K

Ken Snell [MVP]

Hmmm... perhaps there is a typo or other error in the code you're trying to
use. Post the code that you ran when this error occurred.
 
L

Larry

Ken,

Sorry for the brain fart but the error code 429 is exactly
what we should see if we use GetObject when Excel is not
opened (that's why we Resume Next). So no problem there.

However, I got the code to run but the .quit method is
still not closing Excel in the background.

Here's my entire subroutine (thanks for any additionl
help):

Private Sub psFormatTextFields()
'For Excess and Umbrella, format all Cert ID # and
Underlyer NAIC code fields as text. This is to
'prevent Numeric overload errors.

Dim objXLApp As Object
Dim objXLBook As Object
Dim intCtr As Integer
Dim ExcelAlreadyOpen As Boolean
Dim varX As Variant
Dim lngMeterCtr As Long

On Error Resume Next
Set objXLApp = GetObject(, "Excel.Application")
If Err.number <> 0 Then
Err.Clear
Set objXLApp = CreateObject("Excel.Application")
ExcelAlreadyOpen = False
Else
ExcelAlreadyOpen = True
End If

'Set objXLBook = objXLApp.Workbooks(mState.strFilePath)
Set objXLBook = GetObject(mState.strFilePath)

' Make sure both Excel and the workbook are visible (they
won't
' be if Excel was launched by our Automation request)
'objXLApp.Visible = true
'objXLBook.Windows(1).Visible = true

With objXLBook.Worksheets(1)
varX = SysCmd(acSysCmdInitMeter, "Formatting
spreadsheet ", .UsedRange.Rows.Count)

intCtr = 2 'start at 2nd row of spreadsheet to avoid
headers.
Do Until intCtr = .UsedRange.Rows.Count + 1
lngMeterCtr = lngMeterCtr + 1
varX = SysCmd(acSysCmdUpdateMeter, lngMeterCtr)

'Cert ID #
.Range("AB" & intCtr).Value = " " & .Range("AB" &
intCtr).Value
.Range("AB" & intCtr).Value = Right(.Range("AB" &
intCtr).Value, Len(.Range("AB" & intCtr).Value) - 1)
'Underlyer GL NAIC
.Range("AX" & intCtr).Value = " " & .Range("AX" &
intCtr).Value
.Range("AX" & intCtr).Value = Right(.Range("AX" &
intCtr).Value, Len(.Range("AX" & intCtr).Value) - 1)
'Underlyer Auto NAIC
.Range("BA" & intCtr).Value = " " & .Range("BA" &
intCtr).Value
.Range("BA" & intCtr).Value = Right(.Range("BA" &
intCtr).Value, Len(.Range("BA" & intCtr).Value) - 1)
'Underlyer Emp Liability NAIC
.Range("BL" & intCtr).Value = " " & .Range("BL" &
intCtr).Value
.Range("BL" & intCtr).Value = Right(.Range("BL" &
intCtr).Value, Len(.Range("BL" & intCtr).Value) - 1)
'Underlyer Other 1 NAIC
.Range("BP" & intCtr).Value = " " & .Range("BP" &
intCtr).Value
.Range("BP" & intCtr).Value = Right(.Range("BP" &
intCtr).Value, Len(.Range("BP" & intCtr).Value) - 1)
'Underlyer Other 2 NAIC
.Range("BT" & intCtr).Value = " " & .Range("BT" &
intCtr).Value
.Range("BT" & intCtr).Value = Right(.Range("BT" &
intCtr).Value, Len(.Range("BT" & intCtr).Value) - 1)
intCtr = intCtr + 1
Loop
End With

objXLBook.Windows(1).Visible = True
objXLBook.Save
objXLBook.Close
Set objXLBook = Nothing

If ExcelAlreadyOpen = False Then
'just close workbook
objXLApp.Quit
End If
Set objXLApp = Nothing

End Sub
 
K

Ken Snell [MVP]

I see you went back to using GetObject to open the workbook. I still think
that that is the source of EXCEL continuing to run, but you may want to post
your question in an excel group for more assistance. We've reached the limit
of my knowledge at this point...sorry!
 
Top