Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / December 2007

Tip: Looking for answers? Try searching our database.

Excel Automation and Vista

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
scadav - 24 Dec 2007 19:20 GMT
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 - 24 Dec 2007 23:05 GMT
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

Signature

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.
__________________________________

> Hoping someone can help...
>
[quoted text clipped - 124 lines]
>
> Thanks in advance
RoyVidar - 25 Dec 2007 14:14 GMT
> 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

Signature

Roy-Vidar

david@epsomdotcomdotau - 26 Dec 2007 06:27 GMT
>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)

> > A couple of things:
> >
[quoted text clipped - 11 lines]
>
> http://support.microsoft.com/?kbid=244264
RoyVidar - 26 Dec 2007 15:07 GMT
>> http://support.microsoft.com/?kbid=244264
>
[quoted text clipped - 4 lines]
>
> 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.

Signature

Roy-Vidar

david@epsomdotcomdotau - 26 Dec 2007 22:58 GMT
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)

> >> http://support.microsoft.com/?kbid=244264
> >
[quoted text clipped - 18 lines]
> when doing early binding, instantiating through New might fail where
> CreateObject work, which seems to be consistant with MS recommandation.
RoyVidar - 30 Dec 2007 11:38 GMT
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
[quoted text clipped - 17 lines]
>
> (david)

Interesting!

Thanx!

Signature

Roy-Vidar

RoyVidar - 25 Dec 2007 14:08 GMT
> Hoping someone can help...
>
[quoted text clipped - 124 lines]
>
> 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.

Signature

Roy-Vidar

scadav - 26 Dec 2007 11:02 GMT
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.
scadav - 27 Dec 2007 14:08 GMT
> Ok.
>
[quoted text clipped - 7 lines]
>
> 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 - 28 Dec 2007 08:48 GMT
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.
david@epsomdotcomdotau - 29 Dec 2007 05:17 GMT
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)

> 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:
[quoted text clipped - 17 lines]
>
> Really need to solve this problem and hoping someone else has some ideas.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.