Excel won't close properly
 

I'm using automation to build an Excel workbook and 4spreadsheet from within
Access.

When I'm done building the workbook and the associated worksheets, I want to
leave Excel open for my users, so I don't quit Excel, I just set the objects
to nothing in my code. But when they close Excel upon completion of their
work, it occassionally fails to remove Excel from the running processes, and
subsequent runs of the code mentioned above result in a variety of errors.
The code that initiates the selection/opening of Excel and then sets the
objects to nothing follows. If the problem is not here, then I'll try the
SynchMatrix Page code in my next post.

Public Sub SynchMatrix(FY As Integer, Optional NewWorkbook As Boolean =
True)

Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlSht As Excel.Worksheet

Dim intQuarter As Integer
Dim StartDate As Date
Dim EndDate As Date

On Error GoTo ProcError

'Open Excel and create a new workbook
Set xlApp = GetObject(, "Excel.Application ")
xlApp.Visible = True

'If no workbook exists in the current instance of Excel, or the
NewWorkbook variable = true
'then create a new workbook.
'Then select the last workbook in the workbooks collection
If xlApp.Workbooks.Count = 0 Or NewWorkbook = True Then
xlApp.Workbooks.Add
Set xlWbk = xlApp.Workbooks(xlApp.Workbooks.Count)

intQuarter = 1
StartDate = DateSerial(FY - 1, 10, 1)
While StartDate < DateSerial(FY, 10, 1)

If xlWbk.Sheets.Count < intQuarter Then xlWbk.Sheets.Add
After:=xlWbk.Sheets(intQuarter - 1)

Set xlSht = xlWbk.Sheets(intQuarter)
xlSht.Select

'Lock the top two rows for vertical scrolling
xlSht.Range("A3").Select
xlApp.ActiveWindow.FreezePanes = True

'Zoom out so users can see all of the columns being manipulated in
the SynchMatrixPage subroutine
xlApp.ActiveWindow.Zoom = 60

'Change the workbook tab titles
xlSht.Name = intQuarter & Choose(intQuarter, "st", "nd", "rd", "th")
& "_Qtr_FY" & Format(FY, "00")

'Build each page
EndDate = DateAdd("m", 3, StartDate) - 1
Call SynchMatrixPage(xlApp, StartDate, EndDate)

'Go to the next page
intQuarter = intQuarter + 1
StartDate = DateAdd("m", 3, StartDate)

Wend

ProcExit:

If Not xlSht Is Nothing Then Set xlSht = Nothing
If Not xlWbk Is Nothing Then Set xlWbk = Nothing
If Not xlApp Is Nothing Then Set xlApp = Nothing

Exit Sub

ProcError:

If Err.Number = 429 Then 'GetObject failed
Set xlApp = CreateObject("Excel.Application")
Resume Next
Else
MsgBox Err.Number & vbCrLf & Err.Description, , "SynchMatrix"
End If

End Sub

Excel won't close properly
 

OK,

After a little more research, I found that I was leaving out the reference
to the application object when I used the "With Selection" syntax. When I
replaced:

With Selection

with

With xlApp.Selection

There were also a couple of instances where I was using a syntax that looked
like:

Seletion.Font.Bold

or something along those lines. When I prepended the application object to
all of these, it resolved my problems.

Hope this helps someone else.
Dale

"Dale Fye" wrote in message
...
I'm using automation to build an Excel workbook and 4spreadsheet from
within Access.

When I'm done building the workbook and the associated worksheets, I want
to leave Excel open for my users, so I don't quit Excel, I just set the
objects to nothing in my code. But when they close Excel upon completion
of their work, it occassionally fails to remove Excel from the running
processes, and subsequent runs of the code mentioned above result in a
variety of errors. The code that initiates the selection/opening of Excel
and then sets the objects to nothing follows. If the problem is not here,
then I'll try the SynchMatrix Page code in my next post.

Public Sub SynchMatrix(FY As Integer, Optional NewWorkbook As Boolean =
True)

Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlSht As Excel.Worksheet

Dim intQuarter As Integer
Dim StartDate As Date
Dim EndDate As Date

On Error GoTo ProcError

'Open Excel and create a new workbook
Set xlApp = GetObject(, "Excel.Application ")
xlApp.Visible = True

'If no workbook exists in the current instance of Excel, or the
NewWorkbook variable = true
'then create a new workbook.
'Then select the last workbook in the workbooks collection
If xlApp.Workbooks.Count = 0 Or NewWorkbook = True Then
xlApp.Workbooks.Add
Set xlWbk = xlApp.Workbooks(xlApp.Workbooks.Count)

intQuarter = 1
StartDate = DateSerial(FY - 1, 10, 1)
While StartDate < DateSerial(FY, 10, 1)

If xlWbk.Sheets.Count < intQuarter Then xlWbk.Sheets.Add
After:=xlWbk.Sheets(intQuarter - 1)

Set xlSht = xlWbk.Sheets(intQuarter)
xlSht.Select

'Lock the top two rows for vertical scrolling
xlSht.Range("A3").Select
xlApp.ActiveWindow.FreezePanes = True

'Zoom out so users can see all of the columns being manipulated in
the SynchMatrixPage subroutine
xlApp.ActiveWindow.Zoom = 60

'Change the workbook tab titles
xlSht.Name = intQuarter & Choose(intQuarter, "st", "nd", "rd",
"th") & "_Qtr_FY" & Format(FY, "00")

'Build each page
EndDate = DateAdd("m", 3, StartDate) - 1
Call SynchMatrixPage(xlApp, StartDate, EndDate)

'Go to the next page
intQuarter = intQuarter + 1
StartDate = DateAdd("m", 3, StartDate)

Wend

ProcExit:

If Not xlSht Is Nothing Then Set xlSht = Nothing
If Not xlWbk Is Nothing Then Set xlWbk = Nothing
If Not xlApp Is Nothing Then Set xlApp = Nothing

Exit Sub

ProcError:

If Err.Number = 429 Then 'GetObject failed
Set xlApp = CreateObject("Excel.Application")
Resume Next
Else
MsgBox Err.Number & vbCrLf & Err.Description, , "SynchMatrix"
End If

End Sub

Excel won't close properly
 

"Dale Fye" wrote in message
...
OK,

After a little more research, I found that I was leaving out the
reference to the application object when I used the "With Selection"
syntax. When I replaced:

With Selection

with

With xlApp.Selection

There were also a couple of instances where I was using a syntax that
looked like:

Seletion.Font.Bold

or something along those lines. When I prepended the application
object to all of these, it resolved my problems.

Hope this helps someone else.
Dale



Appreciate it Dale. This may indeed be of use for me. I don't have
opportunity to investigate just now, but am saving this post for future
reference.

--
Clif