Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help! Opening excel 2013 from within access 2003 using VBA

    I really hope someone can help as i have tried everything.

    At work we are upgrading from windows XP & office 2003 to windows 7 & office 2013 with the exception of access which is staying as the 2003 version. My problem is when trying to open an excel workbook in 2013 using vba from within access 2003. Everything else works apart from the following which I have highlighted in red.

    dim xlAppCheck As Excel.Application
    dim xlWB As Excel.Workbook

    Set xlAppCheck = CreateObject("Excel.Application")
    With xlAppCheck
    xlAppCheck.Visible = True
    Set xlWB = xlAppCheck.Workbooks.Open("File Name", , False)
    xlAppCheck.EnableEvents = True
    End With

    It works using access 2003 & excel 2003 & also access 2013 & excel 2013 so i am starting to think it is something to do with the difference between the versions.

    Any help would be massively appreciated.
    Thanks in advance

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,494
    Thanks
    3
    Thanked 42 Times in 42 Posts
    In order to do what that line of code is trying to do with Excel, you need to have a reference set to Excel 2013. Open the VBA module and do Tools/References and see if you have a reference set for Excel. If it isn't the current version of Excel (15), then that link will fail.

    I'm also curious why you are choosing to stay with Access 2003? If it is the user interface change (Ribbons and Navigation Pane), that is understandable, but if it is because you are using a .MDB file, those work just fine in 2013. There are also ways around the ribbon/NavPane issue that can solve most user interface issues as well.
    Last edited by WendellB; 2014-03-13 at 07:44.
    Wendell

  4. #3
    New Lounger
    Join Date
    Mar 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your reply. The excel reference is already there (15). After looking at it again it seems as though although the library reference for excel 15 is ticked it doesn't seem to have any of the excel functions to choose from when declaring.

    I have no idea why the company are staying with access 2003 when they are upgrading everything else.

  5. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,494
    Thanks
    3
    Thanked 42 Times in 42 Posts
    I am suspicious that there may be an inconsistency between the OLE Automation (you should have a reference set to that as well as Excel) object for Office 2003 which is probably what Access is using and the version for Office 2013. When using automation, you often don't get "intellisense" assistance in writing code, so that isn't too concerning, but the basic concept of using an Access version three generations older to control the current version of Excel is scary to me. You might try using late binding instead of early binding - we often use that in what we do. The article How to Use Visual Basic .NET.... describes the concepts involved.

    You might also point out to the decision makers the Important Notice for users of Office 2003 in that article, which indicates that tech support for 2003 ends on April 8th.
    Wendell

Posting Permissions

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