Access Excel From Access Closing Issues

[DavisDS]

Ref. Forum: thread692-90756

The above Thread discusses the issues with running Excel Automation from Access. Basically when Excel is opened ANYTHING the user does to alter Excel "like closing it" causes issues with the automation. More specifically, when the user MANUALLY closes Excel, Access still holds reference to it and therefore the system "hides" the program but leaves it in the processes. When Access tries to open Excel later in the code Excel no longer repaints the screen causing MEGA ISSUES!! The best solution is to fully close Excel and dereference it from Access.

My Issue, I have a database which OPENS Excel, My intend is to LEAVE it to the USER to save close or other. Is there a way to open Excel and dereference it from Access after the automation completes so that Excel can close normally?

 

[PHV]

Simply set your Excel object to nothing:
Set myXLobject = CreateObject("Excel.Application")
' some stuff with excel
Set myXLobject = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DavisDS]

Here is My code

Private Sub TaskSchedule_Click()
'SCHEDUALLED TASKS
Dim App As Object
Set App = CreateObject("Excel.Application")
With App
.Workbooks.Open ("C:\126TransportationManagement\WorkFiles\Ops\TASKING\TASKINGBLANK.xls")
.Range("A1").Select
.ActiveCell.Value = "AS OF: " & Format(Now, "dd Mmmm yyyy")
.Range("A3").Select
Dim RECURMIS, REGMIS, DAILYMIS As Recordset
Set RECURMIS = CurrentDb().OpenRecordset("SELECT * FROM Missions WHERE Recurring = TRUE AND Daily = FALSE ORDER BY ReportTime")
Set REGMIS = CurrentDb().OpenRecordset("SELECT * FROM Missions WHERE Recurring = FALSE AND Daily = FALSE ORDER BY ReportTime")
Set DAILYMIS = CurrentDb().OpenRecordset("SELECT * FROM Missions WHERE Daily = TRUE ORDER BY ReportTime")
If RECURMIS.RecordCount > 0 Then
RECURMIS.MoveFirst
While Not RECURMIS.EOF
Write some stuff to sheet
RECURMIS.MoveNext
.Cells(ActiveCell.Row + 1, 1).Activate
Wend
End If
If REGMIS.RecordCount > 0 Then
REGMIS.MoveFirst
While Not REGMIS.EOF
Write some stuff to sheet
REGMIS.MoveNext
.Cells(ActiveCell.Row + 1, 1).Activate
Wend
End If
If DAILYMIS.RecordCount > 0 Then
DAILYMIS.MoveFirst
While Not DAILYMIS.EOF
Write some stuff to sheet
DAILYMIS.MoveNext
.Cells(ActiveCell.Row + 1, 1).Activate
Wend
End If
Finish writing to sheet
End With
Set App = Nothing
End Sub

As you can see I do set App to nothing, however, Excel does not close properly when manually closed and causes repaint issues when tried to be accessed again.

 

[PHV]

Be sure that nowhere in your code you use non fully qualified Excel objects (the most common are Selection or Range)

BTW, do you know the CopyFromRecordset method of the Excel.Range object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DavisDS]

I Appreciate the info and help, but this still does not answer my question. I used your solution to set the object to nothing.

How can I break the connection from Access to Excel AFTER I SEND DATA TO THE SPREADSHEET without Programmatically Closing Excel?

Simply setting the object to nothing does not effect the bug in any way. Excel remains Linked to Access, and therefore wont close, and cant be reused.

 

[AlexCuse]

I think you missed part of PH's post

Be sure that nowhere in your code you use non fully qualified Excel objects (the most common are Selection or Range)

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[DavisDS]

what does this mean non fully qualified


what should I use?

 

[AlexCuse]

For info on qualifying your references in excel, see here:

http://msdn2.microsoft.com/en-us/library/aa141315(office.10).aspx

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[PHV]

what does this mean non fully qualified
Be sure that NO excel object referenced in your code miss a dot before its name.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DavisDS]

I miss no dots and only use the .cells and .range objects to reference Excel

 

[DavisDS]

Found the reference I was talking about...

Helpful Member!jvantichelt (IS/IT--Management)
3 Oct 03 10:20
I found this solution at the Microsoft knowlegde database (Microsoft knows his bug)number "Q187745: BUG: Microsoft Excel Does Not Repaint Properly with Automation".

It says:
This article was previously published under Q187745
SYMPTOMS
When automating Microsoft Excel 97, Excel 2000, or Excel 2002, if the application window has been made visible and the user manually closes it, Excel will not repaint correctly the next time the application window is made visible again.
CAUSE
This problem occurs when a user attempts to quit a running instance of Excel while an Automation client still has a reference to the application object for that instance. By design, Excel does not quit an instance of itself unless all external references are released; if a user tries to quit Excel manually, the application window is merely hidden so that the Automation client may continue working. If, however, the Automation client attempts to make Excel visible again, the application window will not be displayed properly and repainting will not occur.
RESOLUTION
A workaround is to set the ScreenUpdating property of the application object to True after you have made the window visible. This will force Excel to repaint its client area so that it will be displayed properly.
STATUS
Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.
MORE INFORMATION
Steps to Reproduce Behavior
Start a new Standard EXE project in Visual Basic. Form1 is created by default.
On the Project menu, click References, and then select the Microsoft Excel 8.0 object library. For Excel 2000, select Microsoft Excel 9.0 object library, and for Excel 2002, select the Microsoft Excel 10.0 object library.
Place a CommandButton on Form1.
Copy the following code to the Code Window of Form1: Private oApp As Excel.Application

Private Sub Command1_Click()
oApp.Visible = True
End Sub

Private Sub Form_Load()
Set oApp = CreateObject("Excel.Application")
Command1.Caption = "Show Excel"
End Sub




Private Sub Form_Unload(Cancel As Integer)
Set oApp = Nothing
End Sub

On the Run menu, click Start, or press the F5 key to start the program.
Click on the Command button to make Excel visible. Close Excel by pressing the Close button on Excel's title bar, or by selecting Exit from the File menu. Now press the Visual Basic Command button again, and note that Excel does not paint itself correctly.
Repeat the steps again with the Command button's code modified as follows: Private Sub Command1_Click()
oApp.Visible = True
oApp.ScreenUpdating = True
End Sub

 

[DavisDS]

Private Sub TaskSchedule_Click()
'SCHEDUALLED TASKS
Dim App As Object
Dim xlBook As Object 'Excel.Workbook
Dim xlSheet As Object 'Excel.Worksheet
Const ERR_APP_NOTRUNNING As Long = 429

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err = ERR_APP_NOTRUNNING Then
Set xlApp = CreateObject("Excel.Application")
End If
xlApp.Application.DisplayAlerts = False
xlApp.Application.ScreenUpdating = True
Set xlBook = xlApp.Workbooks.Open("C:\126TransportationManagement\WorkFiles\Ops\TASKING\TASKINGBLANK.xls")
xlApp.Visible = True
With xlApp
.Range("A1").Select
.ActiveCell.Value = "AS OF: " & Format(Now, "dd Mmmm yyyy")
.Range("A3").Select
Dim RECURMIS, REGMIS, DAILYMIS As Recordset
Set RECURMIS = CurrentDb().OpenRecordset("SELECT * FROM Missions WHERE Recurring = TRUE AND Daily = FALSE ORDER BY ReportTime")
Set REGMIS = CurrentDb().OpenRecordset("SELECT * FROM Missions WHERE Recurring = FALSE AND Daily = FALSE ORDER BY ReportTime")
Set DAILYMIS = CurrentDb().OpenRecordset("SELECT * FROM Missions WHERE Daily = TRUE ORDER BY ReportTime")
If RECURMIS.RecordCount > 0 Then
RECURMIS.MoveFirst
While Not RECURMIS.EOF
.ActiveCell.Value = RECURMIS.Fields("Control")
.Range(ActiveCell.Row, ActiveCell.Column + 1).Select
.ActiveCell.Value = RECURMIS.Fields("POC")
.Range(ActiveCell.Row, ActiveCell.Column + 1).Select
.ActiveCell.Value = RECURMIS.Fields("ReportTime")
.Range(ActiveCell.Row, ActiveCell.Column + 1).Select
.ActiveCell.Value = RECURMIS.Fields("ReleaseTime")
.Range(ActiveCell.Row, ActiveCell.Column + 1).Select
.ActiveCell.Value = RECURMIS.Fields("Soldiers")
.Range(ActiveCell.Row, ActiveCell.Column + 1).Select
.ActiveCell.Value = RECURMIS.Fields("Trucks")
.Range(ActiveCell.Row, ActiveCell.Column + 1).Select
.ActiveCell.Value = RECURMIS.Fields("Location")
.Range(ActiveCell.Row, ActiveCell.Column + 1).Select
.ActiveCell.Value = RECURMIS.Fields("Instructions")
RECURMIS.MoveNext
.Range(ActiveCell.Row + 1, 1).Select
Wend
End If
If REGMIS.RecordCount > 0 Then
REGMIS.MoveFirst
While Not REGMIS.EOF
.ActiveCell.Value = REGMIS.Fields("Control")
.Cell(ActiveCell.Row, ActiveCell.Column + 1).Activate
.ActiveCell.Value = REGMIS.Fields("POC")
.Cell(ActiveCell.Row, ActiveCell.Column + 1).Activate
.ActiveCell.Value = REGMIS.Fields("ReportTime")
.Cell(ActiveCell.Row, ActiveCell.Column + 1).Activate
.ActiveCell.Value = REGMIS.Fields("ReleaseTime")
.Cell(ActiveCell.Row, ActiveCell.Column + 1).Activate
.ActiveCell.Value = REGMIS.Fields("Soldiers")
.Cell(ActiveCell.Row, ActiveCell.Column + 1).Activate
.ActiveCell.Value = REGMIS.Fields("Trucks")
.Cell(ActiveCell.Row, ActiveCell.Column + 1).Activate
.ActiveCell.Value = REGMIS.Fields("Location")
.Cell(ActiveCell.Row, ActiveCell.Column + 1).Activate
.ActiveCell.Value = REGMIS.Fields("Instructions")
REGMIS.MoveNext
.Cell(ActiveCell.Row + 1, 1).Activate
Wend
End If
If DAILYMIS.RecordCount > 0 Then
DAILYMIS.MoveFirst
While Not DAILYMIS.EOF
.ActiveCell.Value = DAILYMIS.Fields("Control")
.Cell(ActiveCell.Row, ActiveCell.Column + 1).Activate
.ActiveCell.Value = DAILYMIS.Fields("POC")
.Cell(ActiveCell.Row, ActiveCell.Column + 1).Activate
.ActiveCell.Value = DAILYMIS.Fields("ReportTime")
.Cell(ActiveCell.Row, ActiveCell.Column + 1).Activate
.ActiveCell.Value = DAILYMIS.Fields("ReleaseTime")
.Cell(ActiveCell.Row, ActiveCell.Column + 1).Activate
.ActiveCell.Value = DAILYMIS.Fields("Soldiers")
.Cell(ActiveCell.Row, ActiveCell.Column + 1).Activate
.ActiveCell.Value = DAILYMIS.Fields("Trucks")
.Cell(ActiveCell.Row, ActiveCell.Column + 1).Activate
.ActiveCell.Value = DAILYMIS.Fields("Location")
.Cell(ActiveCell.Row, ActiveCell.Column + 1).Activate
.ActiveCell.Value = DAILYMIS.Fields("Instructions")
DAILYMIS.MoveNext
.Cell(ActiveCell.Row + 1, 1).Activate
Wend
End If
.Cell(ActiveCell.Row + 2, 8).Activate
.ActiveCell.Value = "TRUCK's Name Info"
.Cell(ActiveCell.Row + 1, 8).Activate
.ActiveCell.Value = "RANK, USA"
.Cell(ActiveCell.Row + 1, 8).Activate
.ActiveCell.Value = "Truck Master"
'.SaveAs Filename:="C:\TEST.xls", FileFormat:=xlNormal
'.Close
End With
'xlApp.Application.DisplayAlerts = True
'xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
End Sub

THIS IS MY FULL CODE!!! Now it check for an existing Exel object, BUT it doesn't change cells when entering the data

 

[PHV]

You have a LOT of not fully qualified Excel objects !
eg, replace this:
.Range(ActiveCell.Row, ActiveCell.Column + 1).Select
with this:
.Range([!].[/!]ActiveCell.Row, [!].[/!]ActiveCell.Column + 1).Select

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DavisDS]

thanks! will try!

 

[DavisDS]

.ActiveCell.Value = RECURMIS.Fields("Control")
.Range(.ActiveCell.Row, .ActiveCell.Column + 1).Select
.ActiveCell.Value = RECURMIS.Fields("POC")
.Range(.ActiveCell.Row, .ActiveCell.Column + 1).Select
.ActiveCell.Value = RECURMIS.Fields("ReportTime")
.Range(.ActiveCell.Row, .ActiveCell.Column + 1).Select
.ActiveCell.Value = RECURMIS.Fields("ReleaseTime")
.Range(.ActiveCell.Row, .ActiveCell.Column + 1).Select
.ActiveCell.Value = RECURMIS.Fields("Soldiers")
.Range(.ActiveCell.Row, .ActiveCell.Column + 1).Select
.ActiveCell.Value = RECURMIS.Fields("Trucks")
.Range(.ActiveCell.Row, .ActiveCell.Column + 1).Select
.ActiveCell.Value = RECURMIS.Fields("Location")
.Range(.ActiveCell.Row, .ActiveCell.Column + 1).Select
.ActiveCell.Value = RECURMIS.Fields("Instructions")
RECURMIS.MoveNext
.Range(.ActiveCell.Row + 1, 1).Select
tried this and with

.Range(.ActiveCell.Row, .ActiveCell.Column + 1).Select
as

.Range(.ActiveCell.Row, .ActiveCell.Column + 1).Activate

it closees excel correctly, but when the data is entered it stays in the same cell ("A3") the whole time, it doesn't move from A3-A8 to B3-B8, D3-D8, etc.

 

[PHV]

A starting point:
If RECURMIS.RecordCount > 0 Then
RECURMIS.MoveFirst
Dim r As Long
r = 3 ' first cell is A3
While Not RECURMIS.EOF
.Cells(r, 1).Value = RECURMIS.Fields("Control")
.Cells(r, 2).Value = RECURMIS.Fields("POC")
.Cells(r, 3).Value = RECURMIS.Fields("ReportTime")
.Cells(r, 4).Value = RECURMIS.Fields("ReleaseTime")
.Cells(r, 5).Value = RECURMIS.Fields("Soldiers")
.Cells(r, 6).Value = RECURMIS.Fields("Trucks")
.Cells(r, 7).Value = RECURMIS.Fields("Location")
.Cells(r, 8).Value = RECURMIS.Fields("Instructions")
RECURMIS.MoveNext
r = r + 1
Wend
End If

Again, why not use the CopyFromRecordset method of the Excel.Range object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DavisDS]

Thank you PHV for the guidence on qulifying objects correctly.

From thread692-90756:

Thanks to Dynasty for the guidence on checking for existing Excel.Application Objects.

Flize for the fact that the Object Object works better than the Excel.Application Object

jvantichelt for the information directly from MSDN about the bug and the ScreenUpdating Fix

Lastly Jeff333333 For summing most of it up except for the Object vs Excel.Application!

Corrections and facts from the code

replace .Range(.ActiveCell.Row, .ActiveCell.Column + 1).Activate
with .Cells(.ActiveCell.Row, .ActiveCell.Column + 1).Activate

this code enables ability to move from cell to cell relative from current location


On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err = ERR_APP_NOTRUNNING Then
Set xlApp = CreateObject("Excel.Application")
End If

this section checks for existing object so as not to duplicate, and Access dereferences Excel FINE


xlApp.Application.DisplayAlerts = False
xlApp.Application.ScreenUpdating = True

forces Excel to update and function normally WHILE accessed from ACCESS

 

[DavisDS]

PHV I will have to look more into the Copy from Recordset, et seems to be less coding but I have values stored in the recordset that Are NOT passed to Excel Example

recordset fields:

Display
Control
POC
ReportTime
ReleaseTime
Location
ReportTo
Trucks
Soldiers
Instructions
Daily
Reccurring
Mission
Miles

Excel spreadsheet columns

Control
POC
ReportTime
ReleaseTime
Soldiers
Trucks
Location
Instructions

That's it
If I can filter with the copyfromrecordset Great!! HOW?

 

[DavisDS]

my apolgies PHV I don't need to manipulate the recordset, I can just organize the DATA when I create the recordset

Thanks for the assistance, that kills about 9 lines of code in 3 loops (27 total, or so..) YEAH!!

Thanks for all the help, I think I've got it pretty well figured out!