Results 1 to 6 of 6
  1. #1
    Join Date
    May 2019
    Posts
    65

    Workbooks.Open won't open Excel file in Edit Mode

    I am trying to open two workbooks at the same time using Access VBA. I am using the WITH statement to open the files. I need one of the files to open in edit mode, but they open in read only. I tried .ReadOnly=False and .EditMode=True and .Edit=True, but I keep getting an error "Object doesn't support this property or method". Here is the code



    Code:
    Public Sub runExcelwkbk()
    
    
        'Get last number of contacts
        
        Dim MyXL1 As Object
        Dim MyXL2 As Object
        
        Set MyXL1 = CreateObject("Excel.Application")
        
        Set MyXL2 = CreateObject("Excel.Application")
        
        
        
        With MyXL1
            .Application.Visible = True
            .Workbooks.Open (CurrentProject.Path & "\" & "SCD Master Code1.xlsm ")
           
                   
            End With
       With MyXL2
            .Application.Visible = True
            .Workbooks.Open (CurrentProject.Path & "\" & "San Juaquin.xls ")
            .ReadOnly = False
       End With
       
       With MyXL1
            .Run replaceFNumberinFacilityOwnersTable
     
            
            End With
            
            
    '        MyXL.Workbooks.Close
    '        MyXL.Application.Visible = False
    '
      
    
    
    
    
    End Sub
    Any help would be appreciated.
    Thanks

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what is the point of opening the 2nd workbook? you don't do anything with it
    it would also help if you stepped through the code so you can identify which line is causing the error, which has nothing to do with opening in edit mode. My guess is it is the .run line, perhaps the macro in question is not in a general module so cannot be found or perhaps the error is being generated there? so suggest provide the code for that macro?

    I also suggest you investigate all the parameters for your .workbooks.open call - you would need to put your readonly setting there. You can't change the readonly status once the file is open

  3. #3
    Join Date
    May 2019
    Posts
    65
    Regarding the 2nd workbook, the VBA code is in one Excel file which creates the other. At this point I'm just simulating both being opened. What I do thereafter is not relevant to my current test.
    I did step through the code and the ReadOnly=False in the with statement is where the failure is located. I tried putting it before the Workbook.Open statement and get the same error.
    The macro is in a general module in Excel.

    I also tried putting the ReadOnly argument (False) in the format below and that produced the same error. Not sure where to go from here.

    Code:
     With MyXL2
       
        .Application.Visible = True
        .Workbooks.Open CurrentProject.Path & "\" & "San Juaquin.xls ", , False     
            
       End With

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the VBA code is in one Excel file which creates the other.
    if there is code in SCD Master Code1 which is run to create San Juaquin.xls, then it probably hasn't finished executing when you try to open San Juaquin.xls

  5. #5
    Join Date
    May 2019
    Posts
    65
    Quote Originally Posted by Ajax View Post
    if there is code in SCD Master Code1 which is run to create San Juaquin.xls, then it probably hasn't finished executing when you try to open San Juaquin.xls
    VBA Code in SCD Master Code1 creates San Juaquin.xls and does some conversion of data. That code works fine, however I need to stop and manipulate some data manually and then call two more sub routines in SCD Master Code1 that complete the conversion on San Juaquin.xls. I can't put the code in San Juaquin.xls as it is a new file every time i run the first part of the code in SCD Master Code1. In this test, San Juaquin.xls was saved and closed and ready for the second part of the conversion.

    Ok, I just tested other excel files in the same directory/folder and they work, so it has to do with the file itself, not the program. I will recreate the file and see if that works.

  6. #6
    Join Date
    May 2019
    Posts
    65
    Hi,
    I discovered a couple of things. First the problem wasn't in that code, rather other code which was used to create San Juaquin.xls. I restored that code from a previous version and it worked.

    The second thing I discovered is that when opening two Excel applications MYXL1 and MYXL2, you can't communicate from subroutines in one to a worksheet (data) to another. The trick was to open one Excel Application and then open both workbooks under that object.
    However, the better way was to use Access to open one Excel workbook with the code in it (SCD Master Code1.xlsm) and then open the second using the Excel code. Both work great.
    Thanks AJAZ for your help.

    Jeff

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

Similar Threads

  1. Replies: 1
    Last Post: 02-03-2019, 09:16 AM
  2. Replies: 3
    Last Post: 12-01-2014, 07:47 AM
  3. Replies: 1
    Last Post: 03-11-2014, 09:29 AM
  4. Replies: 1
    Last Post: 12-04-2011, 09:11 PM
  5. Replies: 2
    Last Post: 09-01-2011, 10:48 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