Excel Automation and Vista
in [Getting Started]
|
Prev: Emailing Access Reports
Next: Back-up Bob Larson
From: scadav on 24 Dec 2007 14:20 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
From: boblarson on 24 Dec 2007 18:05 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. __________________________________ "scadav" wrote: > 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 >
From: RoyVidar on 25 Dec 2007 09:08 scadav wrote: > 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. -- Roy-Vidar
From: RoyVidar on 25 Dec 2007 09:14 boblarson wrote: > 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 -- Roy-Vidar
From: david on 26 Dec 2007 01:27
>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) "RoyVidar" <roy_vidarNOSPAM(a)yahoo.no> wrote in message news:47711038$0$13724$c83e3ef6(a)nn1-read.tele2.net... > boblarson wrote: > > 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 > > -- > Roy-Vidar |