Consulting

Results 1 to 10 of 10

Thread: Excel 2016 Open Read Only file

  1. #1
    VBAX Newbie
    Joined
    Nov 2019
    Location
    Essex
    Posts
    5
    Location

    Excel 2016 Open Read Only file

    Hi

    I'm using some fairly simple code, I want to open a workbook, pull some information, and put it in another workbook.
    The workbook I want to open is 'Write-Reserved'. So when you open it a password has to be entered or you can select read only.
    I'm not writing to it, just reading, so I've used the normal code:

    Set Passdown = Workbooks.Open(Path & fileName, ReadOnly:=True)

    Which works a treat, on my PC, which is running Office 365. When I try and run it on PC's running office 2016, as soon it opens the workbook, it opens a SaveAs dialog box. I can't get the Damn thing to go away.
    I have tried alot of different variations of the above code, I've been searching the web for a couple of days now trying to solve this. Anyone know why it works fine on 365 and not on 2016 ?????? And any possible workaround solutions please !!!

    Thank you


    Trevor

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,014
    Location
    First, the obvious. Have you tried
    Application.DisplayAlerts = False
    Set Passdown = Workbooks.Open(Path & fileName, ReadOnly:=True)
    DoEvents
    Application.DisplayAlerts = True
    
    Second, I know there are ways to read closed workbooks. Perhaps repost a rephrased question.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  3. #3
    VBAX Newbie
    Joined
    Nov 2019
    Location
    Essex
    Posts
    5
    Location
    Quote Originally Posted by SamT View Post
    First, the obvious. Have you tried
    Application.DisplayAlerts = False
    Set Passdown = Workbooks.Open(Path & fileName, ReadOnly:=True)
    DoEvents
    Application.DisplayAlerts = True
    
    Second, I know there are ways to read closed workbooks. Perhaps repost a rephrased question.

    Sam T. Thanks for your response, I have tried that with no luck. It frustrates me that on my PC it works just fine, the workbook opens and closes as expected. I don't understand why on everyone else's PC's it immediately wants to save it, even though I've opened it in ReadOnly.
    I've had a look into reading the workbook without opening it, but can't get it to work. Most people always suggest using the readonly:=true command. I have found that the WriteResPassword:="password" works fine, as long as someone else doesn't already have it open.. I'm not seeing a sollution..

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,014
    Location
    I changed the Title of the thread. We'll see what happens
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  5. #5
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,662
    Why don't you use ?

    Sub M_snb()
      with getobject("G:\OF\example.xlsx")
         sn=.sheets(1).usedrange
        .close 0
      end with
    End Sub

  6. #6
    VBAX Newbie
    Joined
    Nov 2019
    Location
    Essex
    Posts
    5
    Location
    Quote Originally Posted by snb View Post
    Why don't you use ?

    Sub M_snb()
      with getobject("G:\OF\example.xlsx")
         sn=.sheets(1).usedrange
        .close 0
      end with
    End Sub

    Snb

    Thank you for your reply. I've tried your suggestion but I can't get it to work any better. It's frustrating that on my PC, the code is fine. It does exactly what I want it to do, which is to open a Write-Restricted workbook as read only, compare cell values against values on another workbook, and if they don't exist, it copies those values across.
    The problem really feels like a difference between the way office 365 & 2016 work. Although I can't see why.

    Cheers Trev

  7. #7
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,662
    Do you understand the code ?

    Sub M_snb()
      with getobject("G:\OF\example.xlsx")
         sn=.sheets(1).usedrange
        .close 0
      end with
    
      msgbox sn(1,1) & sn(1,2) & sn(2,1) & sn(2,2) & ubound(sn) & ubound(sn,2)
    End Sub

  8. #8
    VBAX Newbie
    Joined
    Nov 2019
    Location
    Essex
    Posts
    5
    Location
    Not well enough ! But when I tried it, it still tried to open the workbook, which is the problem.
    I have found away around the problem (Not ideal, but works), but if you have a moment to explain, I'd like to understand the Getobject code better ?

    Cheers

  9. #9
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,014
    Location
    Sensei, nobody understands your code without access to google, an XP help file, and an encyclopedia of VBA and Excel like more suggestions ...

    Trev, I've been pondering over his code for 7 years. In that time, I think I've seen him make two mistakes, In the other hand, I can't count the number of times I got his code wrong with out taking off my shoes. At least I don't have to drop my drawers to count. Yet.

    But, let's see if I can keep my shorts on today.
    Option Explicit
    
    Sub M_snb_BySamT()
    Dim sn As Variant
    Dim sn2 As Variant
    Dim NumSheets As Long
    Dim VirtualWorkbook As Object
    Dim VirtualSheet As Object
    
       Set VirtualWorkbook = GetObject("G:\OF\example.xlsx")
       With VirtualWorkbook
          sn = .Sheets(1).UsedRange
          NumSheets = .Sheets.Count
          MsgBox .Sheets(1).Name
          ThisWorkbook.Sheets(1).Range("A1") = sn
          ThisWorkbook.Sheets(1).Name = .Sheets(1).Name
       End With
       Set VirtualWorkbook = Nothing
          
       If NumSheets > 1 Then
           Set VirtualSheet = GetObject("G:\OF\example.xlsx")!Sheets(2)
           With VirtualSheet
              MsgBox .Name
              sn2 = .UsedRange
           End With
           Set VirtualSheet = Nothing
           
           ThisWorkbook.Sheets(2).Range("A1") = sn2
       End If
      
    End Sub
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  10. #10
    VBAX Newbie
    Joined
    Nov 2019
    Location
    Essex
    Posts
    5
    Location
    That's much better, Thank you. It still doesn't solve my problem, but I have learn't something.
    I've transferred my code to the write-reserved spreadsheet that was giving me grief when trying to open as read only.
    So now I run the macro from there, it does all the formatting I need it to do, and leaves the results workbook open to print off the reports.

    Thanks again for your help, appreciate the friendliness, Expect I'll be back with more questions at sometime.

Posting Permissions

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