Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open Excel Spreadsheet from Access (2003 SP2)

    I need to open a spreadsheet from within Access. I've been following some code I found in a book but can't get it to work. I have a short bit of code to illustrate my approach. I would appreciate it if someone could tell me where I'm going wrong. When I run the code I get the following error # and description when attempting to execute the actual open statement: "Error 91, Object vatiable or With block variable not set"

    I've attached a copy of the code I'm using - short and to the point

    Thanks for any help you can offer.
    Lance
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Excel Spreadsheet from Access (2003 SP2)

    The m_objExcel variable isn't set anywhere in the code.

    Do you need to manipulate the workbook in code, or do you want to open it so that the user can view / edit it?

    For the latter situation, you can use a simpler solution:

    Application.FollowHyperlink "K:_OrgSupply LogisticsSandsDataTest.xls"

  3. #3
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Excel Spreadsheet from Access (2003 SP2)

    Hans;
    I have to open it and manipulate the file.

    Lance

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Excel Spreadsheet from Access (2003 SP2)

    First, set a reference (if you don't already have one) to the Microsoft Excel 11.0 Object Library in Tools | References... This makes it possible to use Excel VBA in Access.

    I have attached an example of code you could use.
    If you add to it, make sure that *each* reference to something in Excel is prefixed with the appropriate object variable (objExcel, objWorkbook and objWorksheet in the sample code). Referring to something in Excel without prefix will almost certainly result in an invisible instance of Excel remaining behind in memory and may cause the code to fail if run a second time.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Excel Spreadsheet from Access (2003 SP2)

    Hans;

    That looks great.

    Once again, you've saved me countless hours of frustrating trial and error.

    Thank you so much.

Posting Permissions

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