Results 1 to 6 of 6

Access<-->Excel Interaction Problem

  1. #1
    Ellpee is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    23

    Access<-->Excel Interaction Problem


    Have a situation where, from Access, I need to open and manipulate an Excel workbook. Has worked fine, customer just upgraded to Office 16, now not working.


    My VBA code in Access (1) opens an instance of Excel and makes it visible, (2) opens a specific workbook file, and sortakinda makes it visible. Screen becomes visible for that workbook, but blank.


    Next would be to open a particular Sheet and make that visible, because unless it's visible Access cannot do the things it needs to with the Excel data.


    This was working fine before the recent upgrade, but now the last part fails with a "Subscript out of range" error. No clue why. Any ideas, all y'all?

    Have also posted this over in the Excel Forums.

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,650
    I've gotten that error when the sheet I was referring to didn't exist, so double check that.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    Ellpee is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    23
    Checked that beforehand, sheet is there and contains data, and reference in VBA is to .Sheets(1) rather than by name, so ???

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,650
    What's the code, particularly the line that fails?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  5. #5
    Ellpee is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    23
    I played around with alternatives until I found one that worked. Declaring and setting object variables in different order, etc. As I recall the line that was failing was "Xl.Sheets(1).Visible = True" -- simple enough, and has worked for years until the Office 2016 upgrade, at which time I got the "Subscript out of range" error all of a sudden. Anyhow, I figured it out with a lot of trial and error; one part of that was activating the sheet before trying to make it visible, which I hadn't had to do before. Go figure. So thanks for your interest, but all is good now.

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,650
    Glad you got it sorted out. Sometimes you just beat your head against it until it gives up.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Interaction between Excel and Access 2010
    By mojeime in forum Access
    Replies: 1
    Last Post: 09-26-2013, 04:33 AM
  2. Access data in Excel problem
    By Count Duckula in forum Access
    Replies: 2
    Last Post: 06-16-2013, 01:57 PM
  3. Replies: 0
    Last Post: 09-18-2012, 03:15 AM
  4. Replies: 9
    Last Post: 05-16-2012, 09:17 AM
  5. excel to access problem
    By sahi0002 in forum Import/Export Data
    Replies: 0
    Last Post: 03-08-2009, 10:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums