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

First, the obvious. Have you tried

Code:

---

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.

Quote:

---

Originally Posted by SamT

First, the obvious. Have you tried

Code:

---

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..

I changed the Title of the thread. We'll see what happens

Why don't you use ?

Code:

---

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

---

Quote:

---

Originally Posted by snb

Why don't you use ?

Code:

---

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

Do you understand the code ?

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

---

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

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.

Code:

---

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

---

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.