Office Automation Access/Excel/VISTA Ultimate

Topic

New Reply

Has anyone experienced problems with automating Excel from Access/VBA while in Vista environment?

I recently “upgraded” to Vista Ultimate from Vista Premium, and now when I run an app that opens Excel and does some things, it doesn’t quit/ close the instance of Excel. I have four calls to the same function in my VBA code, and when done I have four instances of Excel open in the Task Manager.

I move the app to an XP operating system, and everything is fine.

I use the following to close and exit the function:

Exit_fFormatSpreadsheet:
Set xlSourceRange = Nothing
Set xlColPoint = Nothing
Set xlChartObj = Nothing
‘ Close and save the workbook
xlWrkbk.Close SaveChanges:=True
Set xlWrkbk = Nothing
‘ Quit Excel
xlApp.Quit
Set xlApp = Nothing
Exit Function

This has worked fine for two years, now it doesn’t. I had a choice of ‘upgrading’ to Ultimate, or ‘downgrading’ to XP. It appears I made the wrong choice.

Any ideas are greatly appreciated.

Thanks,

Ken

Reply | Quote

Replies

The code that you posted looks OK. I suspect that you have an unqualified reference to an Excel object earlier in your code. For example,

xlApp.ActiveSheet.Name = “MySheet”

is OK because it refers to xlApp, but

ActiveSheet.Name = “MySheet”

will start a rogue instance of Excel because it does not refer directly or indirectly to xlApp.

Reply | Quote

Hans,

I have been bitten by that bug many times in the past, and you have gratiously helped me find and fix. This code is that work product, AND it runs fine on other machines running Windows XP. I took the entire directory where the app resides and moved it to other workstations, and it worked fine, without any problems.

One other thing I didn’t mention, was that for some reason Task Manager sometimes shows the instance as EXCEL.EXE and other times as EXCEL.EXE *32. I have not been able to find a pattern to that, nor do I know the difference between the two.

Below is the entire code in the function

‘==================================================
Function fFormatSpreadsheet(strFullPath As String, strFileName As String, strSheetName As String, strNewSheetName As String)

Dim xlWrkbk As Excel.Workbook
Dim xlChartObj As Excel.Chart
Dim xlSourceRange As Excel.Range
Dim xlColPoint As Excel.Point
Dim xlApp As Excel.Application

Dim SheetName As Excel.Worksheet
Dim WSD As Excel.Worksheet
Dim i As Integer
On Error GoTo Err_fFormatSpreadsheet

‘ Create a Microsoft Excel object.
‘ This opens an instance of Excel
Set xlApp = CreateObject(“Excel.Application”)

‘ Open the spreadsheet with the exported data.
Set xlWrkbk = xlApp.Workbooks.Open(strFullPath & strFileName)

Set WSD = xlWrkbk.Worksheets(strSheetName)
xlApp.Sheets(strSheetName).Select

With WSD
With xlApp.Cells.Font
.Name = “Times New Roman”
.FontStyle = “Regular”
.Size = 10
.ColorIndex = xlAutomatic
End With
””””””””””””””””””””””
xlApp.Rows(“1:1″).Select
xlApp.Selection.Font.Bold = True
xlApp.Columns.AutoFit
End With

‘Rename the sheet
xlApp.Sheets(strSheetName).Name = strNewSheetName

Exit_fFormatSpreadsheet:
Set xlSourceRange = Nothing
Set xlColPoint = Nothing
Set xlChartObj = Nothing
‘ Close and save the workbook
xlWrkbk.Close SaveChanges:=True
Set xlWrkbk = Nothing
‘ Quit Excel
xlApp.Quit
Set xlApp = Nothing
Exit Function

Err_fFormatSpreadsheet:
MsgBox CStr(Err) & ” ” & Err.Description
Resume Exit_fFormatSpreadsheet

End Function
‘==================================================

Did I miss something? As I stated, it runs fine from XP machines.
Thanks for your consideration.
Ken

The code that you posted looks OK. I suspect that you have an unqualified reference to an Excel object earlier in your code. For example,

xlApp.ActiveSheet.Name = “MySheet”

is OK because it refers to xlApp, but

ActiveSheet.Name = “MySheet”

will start a rogue instance of Excel because it does not refer directly or indirectly to xlApp.

Reply | Quote

The code is OK. although it contains some unused variables, and could be streamlined a bit:

[codebox]Function fFormatSpreadsheet(strFullPath As String, strFileName As String, _
strSheetName As String, strNewSheetName As String)
Dim xlWrkbk As Excel.Workbook
Dim xlApp As Excel.Application
Dim WSD As Excel.Worksheet

On Error GoTo Err_fFormatSpreadsheet

‘ Create a Microsoft Excel object.
‘ This opens an instance of Excel
Set xlApp = CreateObject(“Excel.Application”)

‘ Open the spreadsheet with the exported data.
Set xlWrkbk = xlApp.Workbooks.Open(strFullPath & strFileName)
Set WSD = xlWrkbk.Worksheets(strSheetName)

With WSD
With .Cells.Font
.Name = “Times New Roman”
.FontStyle = “Regular”
.Size = 10
.ColorIndex = xlAutomatic
End With
.Rows(1).Font.Bold = True
.Columns.AutoFit
‘Rename the sheet
.Name = strNewSheetName
End With

Exit_fFormatSpreadsheet:
On Error Resume Next
Set WSD = Nothing
‘ Close and save the workbook
xlWrkbk.Close SaveChanges:=True
Set xlWrkbk = Nothing
‘ Quit Excel
xlApp.Quit
Set xlApp = Nothing
Exit Function

Err_fFormatSpreadsheet:
MsgBox CStr(Err) & ” ” & Err.Description
Resume Exit_fFormatSpreadsheet
End Function
[/codebox]
BTW Excel.exe *32 means that a) you run 64-bit Windows and Excel runs in 32-bit compatibility mode. I don’t know whether this has any bearing on the problem. I fear I cannot help you with this.

Reply | Quote

Hans,

You are always a help, even when you don’t have an answer for the question asked. Your streamlining alone was well worth the time. I will continue to research the VBA/Access/Excel in Vista Ultimate 64 and post back when/if I find something.

Thank you for your consideration.

Ken

The code is OK. although it contains some unused variables, and could be streamlined a bit:

[codebox]Function fFormatSpreadsheet(strFullPath As String, strFileName As String, _
strSheetName As String, strNewSheetName As String)
Dim xlWrkbk As Excel.Workbook
Dim xlApp As Excel.Application
Dim WSD As Excel.Worksheet

On Error GoTo Err_fFormatSpreadsheet

‘ Create a Microsoft Excel object.
‘ This opens an instance of Excel
Set xlApp = CreateObject(“Excel.Application”)

‘ Open the spreadsheet with the exported data.
Set xlWrkbk = xlApp.Workbooks.Open(strFullPath & strFileName)
Set WSD = xlWrkbk.Worksheets(strSheetName)

With WSD
With .Cells.Font
.Name = “Times New Roman”
.FontStyle = “Regular”
.Size = 10
.ColorIndex = xlAutomatic
End With
.Rows(1).Font.Bold = True
.Columns.AutoFit
‘Rename the sheet
.Name = strNewSheetName
End With

Exit_fFormatSpreadsheet:
On Error Resume Next
Set WSD = Nothing
‘ Close and save the workbook
xlWrkbk.Close SaveChanges:=True
Set xlWrkbk = Nothing
‘ Quit Excel
xlApp.Quit
Set xlApp = Nothing
Exit Function

Err_fFormatSpreadsheet:
MsgBox CStr(Err) & ” ” & Err.Description
Resume Exit_fFormatSpreadsheet
End Function
[/codebox]
BTW Excel.exe *32 means that a) you run 64-bit Windows and Excel runs in 32-bit compatibility mode. I don’t know whether this has any bearing on the problem. I fear I cannot help you with this.

Reply | Quote