Excel Automation and Vista

[scadav]

Hoping someone can help...

I use the following code (access database) with Access 2003 and Windows
XP and have no problems with Excel shutting down after the code runs. If
I use the same access database with the same version of Access on Vista,
Excel will stay running as a process. Anyone see anything like this
before?

=========================================================================
=========================================================================

Public Function ExcelExportStandard(sXLSTemplate As String, sVBAQuery As
String, oExcelProgress As Object, oExcelForm As Form, Optional sHeader1
As String, Optional sHeader2 As String)
On Error GoTo err_Handler

Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Set appExcel = CreateObject("Excel.Application")

Dim sTemplate As String
Dim sOutput As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTab As Byte = 1
Const cStartRow As Byte = 6
Const cStartColumn As Byte = 1

Dim iSkipNewLineForNewGrouping As Integer

' Start with a clean file built from the template file
sTemplate = CurrentProject.Path & "\" & sXLSTemplate

sOutput = GetUserLocationAndFileName

If IsNull(sOutput) Or (sOutput = "") Then
MsgBox "No File Name Chosen, Exiting"
Exit Function
End If

If Dir(sTemplate) = "" Then
MsgBox "You are Missing a Required DLL (" & sTemplate & ") in
order to Output to Excel. Can't Complete Operation."
Exit Function
End If

FileCopy sTemplate, sOutput

' Create the Excel Applicaiton, Workbook and Worksheet and Database
object
'Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTab)

' Create the recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sVBAQuery, dbOpenSnapshot)

If Not rst.BOF Then rst.MoveFirst

' Set the starting point for the excel spreadsheet
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow

Do Until rst.EOF
'DoEvents
iFld = 0
lRecords = lRecords + 1
oExcelProgress.Visible = True
oExcelProgress.Value = "Exporting record #" & lRecords & " to " &
sOutput
oExcelForm.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop


' Save the export
wbk.Close savechanges:=True
appExcel.Application.Quit
Set wbk = Nothing: Set appExcel = Nothing

MsgBox "Complete"

exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
'DoCmd.Hourglass False
''''Me.lb_Status.Visible = False
Exit Function

err_Handler:
ExcelExport = Err.Description
MsgBox Err.Description
Resume exit_Here

End Function
=========================================================================
=========================================================================



Thanks in advance

 

[boblarson]

A couple of things:

1. If you are using "Dim appExcel As Excel.Application" Then Don't use Set
appExcel = CreateObject("Excel.Application") but instead use Set appExcel =
New Excel.Application.

Or, if you want to use late binding then declare the object as
Dim objExcel As Object and then you can use Set objExcel = CreateObject
("Excel.Application")

But if you are doing it the way you are doing, you are mixing early binding
with late binding.

2. I don't ee where you save the workbook to a new file. I see that you are
opening a template and then you issue a save but if the template is a
readonly then you should use the syntax for saving to a new file. Also, you
are using Excel syntax and not Access syntax with wbk.Close savechanges:=True
which may have nothing to do with the problem but you should use wbk.SaveAs
"FileName" if you want it to save to a different file name and then if not,
you just want the current one to save it would be wbk.Save

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[RoyVidar]

Hoping someone can help...

I use the following code (access database) with Access 2003 and Windows
XP and have no problems with Excel shutting down after the code runs. If
I use the same access database with the same version of Access on Vista,
Excel will stay running as a process. Anyone see anything like this
before?

=========================================================================
=========================================================================

Public Function ExcelExportStandard(sXLSTemplate As String, sVBAQuery As
String, oExcelProgress As Object, oExcelForm As Form, Optional sHeader1
As String, Optional sHeader2 As String)
On Error GoTo err_Handler

Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Set appExcel = CreateObject("Excel.Application")

Dim sTemplate As String
Dim sOutput As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTab As Byte = 1
Const cStartRow As Byte = 6
Const cStartColumn As Byte = 1

Dim iSkipNewLineForNewGrouping As Integer

' Start with a clean file built from the template file
sTemplate = CurrentProject.Path & "\" & sXLSTemplate

sOutput = GetUserLocationAndFileName

If IsNull(sOutput) Or (sOutput = "") Then
MsgBox "No File Name Chosen, Exiting"
Exit Function
End If

If Dir(sTemplate) = "" Then
MsgBox "You are Missing a Required DLL (" & sTemplate & ") in
order to Output to Excel. Can't Complete Operation."
Exit Function
End If

FileCopy sTemplate, sOutput

' Create the Excel Applicaiton, Workbook and Worksheet and Database
object
'Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTab)

' Create the recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sVBAQuery, dbOpenSnapshot)

If Not rst.BOF Then rst.MoveFirst

' Set the starting point for the excel spreadsheet
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow

Do Until rst.EOF
'DoEvents
iFld = 0
lRecords = lRecords + 1
oExcelProgress.Visible = True
oExcelProgress.Value = "Exporting record #" & lRecords & " to " &
sOutput
oExcelForm.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop


' Save the export
wbk.Close savechanges:=True
appExcel.Application.Quit
Set wbk = Nothing: Set appExcel = Nothing

MsgBox "Complete"

exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
'DoCmd.Hourglass False
''''Me.lb_Status.Visible = False
Exit Function

err_Handler:
ExcelExport = Err.Description
MsgBox Err.Description
Resume exit_Here

End Function
=========================================================================
=========================================================================



Thanks in advance

I can't see anything particular. What I would suggest to try, is
allowing the file to save prior to exiting Excel, and release the
objects in the order of dependenscies.

I e, place a DoEvents after you issue the save operation.

Then, perhaps release the wks object and the wbk object. Then quit the
application (perhpaps with a DoEvents), then release the application
object (appExcel).

You are releasing the application object and workbook object prior to
the worksheet object, which in some cases may cause anomalities.

But again, I'm only guessing, I'm not sure.

 

[RoyVidar]

A couple of things:

1. If you are using "Dim appExcel As Excel.Application" Then Don't use Set
appExcel = CreateObject("Excel.Application") but instead use Set appExcel =
New Excel.Application.

Why?

"When creating an instance of an Microsoft Office application, use
CreateObject instead of New. CreateObject more closely maps to the
creation process used by most Visual C++ clients, and allows for
possible changes in the server's CLSID between versions. CreateObject
can be used with both early-bound and late-bound objects."

http://support.microsoft.com/?kbid=244264

 

[Guest]

http://support.microsoft.com/?kbid=244264

But note that article repeats the old, out-of-date information that
the LocalServer32 key should point to the application server.

It does not.

The LocalServer32/default key is ignored.

The LocalServer32/LocalServer32 key is a coded value which
is not a clear path, and for which I have never found documentation.

(david)

 

[scadav]

Ok.

I tried all suggestions here, but still no luck (I will admit I am not an
expert). Once again, the code works in Win XP, but not Vista. Very
strange?!??!!

Anyway, does anyone have some very generic excel automation code that they
are 100% sure works in both operating systems? If so, can you post it and
I will if I can just rewrite my code into it.

I am desperate.

 

[RoyVidar]

But note that article repeats the old, out-of-date information that
the LocalServer32 key should point to the application server.

It does not.

The LocalServer32/default key is ignored.

Have you tried changing it, and then automate?

On my WinXP/Office 2007 i changed part of the path from

....Office12... to ...xxOffice14...

then when running some automation code, I get a coffebreak (large
friendly message screen saying "Configuring Microsoft Office
Professional 2007...") while it repairs the path in the registry key.

Anyway, I tend not to delve into such depths, but have observed that
when doing early binding, instantiating through New might fail where
CreateObject work, which seems to be consistant with MS recommandation.

 

[Guest]

Our automatic build process required us to modify the LocalServer32
key to support user-level security. It just stopped working with Access
2000. I found that if I removed the LocalServer32\LocalServer32 key,
the behaviour reverted to the use of the clear LocalServer32\default key.

I formed the opinion that the new key pointed to the installation data
(possibly to protect the user from automation spoofing), but I've never
found any documentation at all. Documentation might be interesting,
because (A) there might be a legitimate way to work with the current
behaviour, and (B) it might shed some light on the behaviour of Access
2007 when installed on the same PC as 2003 etc.

There is copious documentation on OLE, ActiveX, and COM, but it all
dates from the time when MS was offering COM as an open standard
competing with CORBA or JAVA beans. Nothing at all that I've ever
found about the current behaviour of OLE or Windows Installer. MS
OLE automation documentation seems to have stopped around 1999,
as seen in the KB article.

(david)

 

[scadav]

Ok.

I tried all suggestions here, but still no luck (I will admit I am not
an expert). Once again, the code works in Win XP, but not Vista.
Very strange?!??!!

Anyway, does anyone have some very generic excel automation code that
they are 100% sure works in both operating systems? If so, can you
post it and I will if I can just rewrite my code into it.

I am desperate.

All,

Still having problems here. Has anyone ever gotten excel automation to
work on Vista? Can someone post the code they used that worked or point me
in the appropriate direction.

Thanks,
Dave

 

[scadav]

I did some more playing around with the code and was able to get Excel to
close in Vista if I removed the line that says: wbk.Close savechanges:
=True

However, when I did this, it interactively popped up to the screen asking
that I save the file. Once I chose to save the file and the code finished,
EXCEL.EXE would shutdown as a process.

So I tried changing the wbk.Close savechanges:=True to wbk.SaveAs (as
previously suggested). I also tried wbk.Save and wks.Save, but EXCEL.EXE
would remain open still. (I obviously don't want this to interact with the
user).

Two other pieces of interesting information is:

#1 - if I leave the wbk.Close savechanges:=True in and open the file once
it is complete I do not get an "this file is in use message"
#2 -Once I close the file after opening it that instance of EXCEL.EXE will
shutdown.

Really need to solve this problem and hoping someone else has some ideas.

 

[Guest]

Change to late binding, and fix everything that breaks.

This is to be sure that you are not implicitly creating any objects
that you aren't aware of.

Change all your named variables to comma counting:
wbk.Close savechanges:=True
becomes something like
wbk.Close,,,True

I don't know the comma counts: you will have to look that up.
This is to get rid of any variables which are actually creating objects
that you don't know about.

These are only general suggestions about using Excel Automation.
I don't know why Vista is different.

(david)

 

[RoyVidar]

It happens that david@epsomdotcomdotau formulated :

Our automatic build process required us to modify the LocalServer32
key to support user-level security. It just stopped working with
Access 2000. I found that if I removed the
LocalServer32\LocalServer32 key, the behaviour reverted to the use of
the clear LocalServer32\default key.

I formed the opinion that the new key pointed to the installation
data (possibly to protect the user from automation spoofing), but
I've never found any documentation at all. Documentation might be
interesting, because (A) there might be a legitimate way to work with
the current behaviour, and (B) it might shed some light on the
behaviour of Access 2007 when installed on the same PC as 2003 etc.

There is copious documentation on OLE, ActiveX, and COM, but it all
dates from the time when MS was offering COM as an open standard
competing with CORBA or JAVA beans. Nothing at all that I've ever
found about the current behaviour of OLE or Windows Installer. MS
OLE automation documentation seems to have stopped around 1999,
as seen in the KB article.

(david)

Interesting!

Thanx!