Access VBA and Excel Automation not working. RRS feed

  • Question

  • I have a number of pc's running an Access Application and everything works perfectly except on one machine. The "good" machines have various software configurations as required by the applicable user. Most are running Windows 7 and Office 365. All are up to date. One machine is a 64 bit machine and is running Office 365 and MS Excel 2010. The App works just fine on this machine. The "bad" machine has the same setup but its a 32 bit machine. On the 32 bit machine the Excel Automation fails. The code in question is as follows:

    'Variables to refer to Excel and Objects
    Dim MySheetPath As String
    Dim xl As Excel.Application
    Dim XlBook As Excel.Workbook
    Dim XlSheet As Excel.Worksheet
    ' Tell it location of actual Excel file
    MySheetPath = strFileSource

    Set XlBook = GetObject(MySheetPath)      'This is the line that fails on the 32 bit machine.
    'Make sure excel is visible on the screen
    XlBook.Windows(1).Visible = True
    'Define the sheet in the Workbook as XlSheet
    Set XlSheet = XlBook.Worksheets(1)

    Any ideas on how to fix the problem? Uninstalling Excel 2010 is not an option because its essential for other software the company relies on.


    Tuesday, February 28, 2017 10:27 PM

All replies

  • I think you need help us out a bit by identifying the problem. Are you getting an error? If so, what is it?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, March 1, 2017 6:03 PM
  • Try like this

    'Add referencje Tools/References/Microsoft Excel x,xx Object Lib.
    Dim xlApp As Excel.Application: Set xlApp = New Excel.Application
    xlApp.Visible = True
    Dim xlWKB As Excel.Workbook
    Set xlWKB = xlApp.Workbooks.Open("C:\youre_file.xlsx")  
    Dim xlWKS As Excel.Worksheet
    Set xlWKS = xlWKB.Sheets("Your_sheet")

    And then you can use reference to xlWKS to your cells data

    Oskar Shon, Office System MVP -
    if Helpful; Answer when a problem solved

    Thursday, March 2, 2017 6:31 PM