MS Access Forum / New Users / December 2007
Excel Automation and Vista
|
|
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.
|
|
|