PDA

View Full Version : Excel 2016 Open Read Only file



twistedtrev
11-12-2019, 02:33 AM
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

SamT
11-12-2019, 03:00 AM
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.

twistedtrev
11-12-2019, 03:45 AM
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..

SamT
11-12-2019, 08:36 AM
I changed the Title of the thread. We'll see what happens

snb
11-12-2019, 09:59 AM
Why don't you use ?


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

twistedtrev
11-13-2019, 12:31 AM
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

snb
11-13-2019, 02:37 AM
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

twistedtrev
11-13-2019, 02:51 AM
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

SamT
11-13-2019, 03:48 AM
Sensei, nobody understands your code without access to google, an XP help file, and an encyclopedia of VBA and Excel like more suggestions ... (http://www.snb-vba.eu/index_en.html)

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

twistedtrev
11-13-2019, 04:17 AM
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.