Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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



    [quote name='HansV' post='765150' date='12-Mar-2009 15:47']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.[/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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

    [quote name='HansV' post='765159' date='12-Mar-2009 16:28']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.[/quote]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •