Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2016
    Posts
    4

    cannot open workbook


    When I use the following code to open an excel workbook from Access the workbook is opened but in read only mode and is not visible in Excel. How may I open a workbook so that I may see it?


    Workbooks.Open ("D:\Data\MYMPM\MS71_GAFA_Analysis\GAF_Template _v1-01.xlsb")

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    Workbooks is a collection in Excel, not Access. At least, I cannot find any reference to the Workbooks collection for Access on MSDN. Bottom line, your code is using an Excel reference.

    I would start by instantiating an instance of Excel and use that instance to open your xlsb file. Apparently, you already have created a reference to Excel in your Access application, otherwise the code you posted would not compile.

    So maybe something like ...
    Code:
    Dim xlApp As New Excel.Application
    Dim myBook As Workbook
    Dim mySheet As Worksheet
    Set myBook = xlApp.Workbooks.Open("C:\Test\ExcelFiles\TestFile.xlsx")

  3. #3
    Join Date
    Sep 2016
    Posts
    4
    Thanks for that. That seems to work. It does open the file although the file remains non readable until the Access application is closed down.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    I am not understanding what you mean by closing down the application. The application the code is using is a New instance. That instance is the one you want to interact with.

    Because you are using a New instance, you should have full control via that instance. There can be exceptions with security and privileges. Also, if someone else (or another instance of Excel) has the file open while your code runs.

    Test your code in a control environment by creating your own XLSB file on your C drive.

    Take advantage of the fact that you instantiated a running instance of Excel. Maybe use xlApp.Visible or something, depending on what your goals are.

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Excel doesn't like to share! May have nothing to do with Access.

  6. #6
    warmslime is offline Advanced Beginner
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    49
    Quote Originally Posted by aytee111 View Post
    Excel doesn't like to share! May have nothing to do with Access.
    Do you mean that you're not able to reference its cells by pointing or using any of the functions in the View tab from any other Excel windows? Cause I've noticed that too.

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

Similar Threads

  1. Worksheet within Excel Workbook
    By rpkfish in forum Macros
    Replies: 4
    Last Post: 05-06-2016, 03:02 PM
  2. Check if excel workbook is open
    By gixerp in forum Access
    Replies: 2
    Last Post: 03-23-2016, 05:24 PM
  3. Replies: 2
    Last Post: 09-04-2013, 11:01 PM
  4. Replies: 1
    Last Post: 08-08-2013, 03:54 PM
  5. Replies: 5
    Last Post: 07-22-2013, 01:11 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
  •  
Other Forums: Microsoft Office Forums - Senior Forums