VBA workbook.open ReadOnly:=True failing - what's wrong?

Clif Mac

New Member
Joined
Jun 4, 2019
Messages
9
I'm using this code to open a workbook so I can grab some of the builtin properties.
Code:
Function OpenHidden(ByRef szFullName As String) As Workbook
<other code="" here="">... other code here ...
    Set OpenHidden = Workbooks.Open(szFullName, UpdateLinks:=False, _
        ReadOnly:=True, Notify:=True)
End Function
The code works fine until I hit a workbook that someone has open -then I get this error dialog:
...xlsx is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen ...xlsx?
When I choose No I get the debugger dialog, which in turn breakpoints on the Workbooks.Open line above.
If I choose End instead of Debug (or if I Reset the VBE) the target workbook is presented as an open workbook?! (I thought I just asked not to open the workbook!) If I go to the File tab on the ribbon, the Properties shows me as the Last Modified By.

The object of the exercise is to walk a branch of the volume tree to gather statistical information on many dozens of workbooks. I'm using Workbooks.Open because I don't know any other way to get to the builtin document properties (can I get them using ADODB?) . As far as I know I'm doing nothing to modify the workbook in any way (other than the fact that I'm touching it) - so I should be able to open read only, grab my data, close it and move on - even if someone else is editing it at the time.

So - my question is: What am I doing wrong? How can I get data out of a workbook someone else has open? (Without interrupting my macro?)

Nothing I've found so far with Google's assistance has helped. :(</other>
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Thanks for the link - I hadn't found that one. I won't have a chance to test today -- I'll post back with my results whenever I get it looked into.
 
Upvote 0
Okay - I found opportunity to get back to this project. Here is what I found:
Changing
Code:
    Set OpenHidden = Workbooks.Open(szFullName, UpdateLinks:=False, _
       ReadOnly:=True, Notify:=True)
to
Code:
    Set OpenHidden = Workbooks.Open( _
            FileName:=szFullName, _
            UpdateLinks:=False, _
            ReadOnly:=True, _
            IgnoreReadOnlyRecommended:=True, _
            Notify:=True)
Did not change what was happening if another user had the workbook open.
From another discussion somewhere (I forgot where) I found a reference to the DisplayAlerts property.
This
Code:
    'defeat pop-up if another user has this workbook open
    Application.DisplayAlerts = False
    Set OpenHidden = Workbooks.Open( _
            FileName:=szFullName, _
            UpdateLinks:=False, _
            ReadOnly:=True, _
            IgnoreReadOnlyRecommended:=True, _
            Notify:=True)
    Application.DisplayAlerts = True 'restore normal operation
allows my code to continue. I do get a flicker when processing the open workbook - presumably Excel is displaying the dialog, auto-magically supplying the default answer, and continuing. That's another issue that if I am unable to resolve I can live with.

Thanks for the help!
 
Upvote 0
Ok then at the start of the code put
Code:
With application
  .displayalerts=false
  .screenupdating=false
end with

and at the end

Code:
With application
  .displayalerts=true
  .screenupdating=true
end with
that should also stop the flickering
 
Upvote 0
Yeah, that sounds right. I've always wrapped that code around sub-processes- never "outside" of what I'm doing. Most times it probably wouldn't make much difference; but in this case I have in mind to launch a potentially lengthy process which will happily crunch away in the background whilst I'm working in other windows - quite likely in other workbooks that also will be executing VBA code at the same time [perhaps I'll discover that is not permitted]; so I'm reluctant to try what you suggest. I will keep it in mind if I need more experimentation. Also, I'm using a form to display a progress bar, and I suspect disabling screen updating would break that? (No, I haven't tested that thought -- another future effort.)
'Excel Tips From John Walkenbach: Displaying A Progress Indicator
'http://spreadsheetpage.com/index.php/tip/displaying_a_progress_indicator/
I'll show more of my code below; I've built a collection of workbooks of interest, now I'm walking (For Each) the collection to harvest properties from qualifying workbooks. I solved the flicker (and gained a small performance improvement- not sure why) by instantiating a hidden Excel Application object. (The code I'm including has what I was attempting commented out.)
To open and close each workbook-
Code:
''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Comments:   Returns a backgrounded workbook object (read only)
'''             Disables various Excel functions for improved performance
'''             Designed to use in tandem with CloseHidden() thus:
'''                 OpenHidden
'''                 Code to harvest information from background workbook
'''                 CloseHidden <restores disabled="" excel="" functions="">
'''
''' Arguments:  szFullName      [in] The full path and filename of the workbook
'''                             you want to query.
''' Date        Developer       Action
''' --------------------------------------------------------------------------
''' 05/10/19    Clif McIrvin    OpenHidden
'''
Function OpenHidden(ByRef szFullName As String) As Workbook
    
Static xlApp As New Excel.Application
Static bInitialized As Boolean


'Initialize background xlApp
    If Not bInitialized Then
        With xlApp
            .Visible = False
            .Calculation = xlCalculationManual
            .EnableEvents = False
        End With
        bInitialized = True
    End If


'Get current state of various Excel settings;
'put this at the beginning of your code


'Calculation is undefined if activesheet is nothing


'   With Application
'      screenUpdateState = .ScreenUpdating
'      statusBarState = .DisplayStatusBar
'      eventsState = .EnableEvents
'      If ActiveSheet Is Nothing Then 'assign default values
'        calcState = xlCalculationAutomatic
'        displayPageBreakState = False
'      Else
'        calcState = .Calculation
'        displayPageBreakState = ActiveSheet.DisplayPageBreaks
'
''turn off some Excel functionality so your code runs faster
'        .Calculation = xlCalculationManual
'        ActiveSheet.DisplayPageBreaks = False
'      End If 'Not ActiveSheet Is Nothing Then
'
'      .ScreenUpdating = False
'      .DisplayStatusBar = False
'      .EnableEvents = False
'   End With


'>>your code goes here<<


    'defeat pop-up if another user has this workbook open
    xlApp.DisplayAlerts = False
    Set OpenHidden = xlApp.Workbooks.Open( _
            FileName:=szFullName, _
            UpdateLinks:=False, _
            ReadOnly:=True, _
            IgnoreReadOnlyRecommended:=True, _
            Notify:=True)
'    Application.DisplayAlerts = True 'restore normal operation
'    OpenHidden.Windows(1).Visible = False 'very hidden
    
End Function

'
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Comments:   Closes backgrounded workbook and restores Excel settings
'''             used in tandem with OpenHidden()
'''
''' Arguments:  wb              [in] workbook object to close
'''
''' Date        Developer       Action
''' --------------------------------------------------------------------------
''' 05/10/19    Clif McIrvin    CloseHidden
'''
Sub CloseHidden(ByRef wb As Workbook)
    
'Get current state of various Excel settings;
'put this at the beginning of your code


'turn off some Excel functionality so your code runs faster


'>>your code goes here<<


    wb.Close SaveChanges:=False
    
'after your code runs, restore state;
'put this at the end of your code
'   With Application
'    If Not ActiveSheet Is Nothing Then
'      .Calculation = calcState
'      ActiveSheet.DisplayPageBreaks = displayPageBreakState
'    End If
'      .EnableEvents = eventsState
'      .DisplayStatusBar = statusBarState
'      '.ScreenUpdating = screenUpdateState
'   End With
      
    Set wb = Nothing


End Sub
And here's the code fragment walking the collection and harvesting the properties
Code:
'iterate collection
lCounter = 109 'initialize for 'large value' counter
'110 will trigger progressbar update on first file
szProcess = "Adding New Rows- "
ShowProgress -2, szProcess 'Reset Progress Indicator (to 2%)
For Each vBook In cListOfBooks
    lCounter = lCounter + 1
        ShowProgress lCounter, szProcess & vBook(cmOptValue)


    sName = vBook(cmFullPath)
    'use fast ADO to read closed workbook
    vLocationValue = GetCellValue(sName, "c3:c3")
    'I don't know how to get BuiltinDocumentProperties with ADO
    'so will open the workbook for that.
    
    'template workbook location name is blank (empty) cell
    'GetCellValue (ADO) returns Null for blank cells
    If VarType(vLocationValue) > vbNull Then 'skip unused workbooks


' the FileSystem File Object has properties that do not appear to
' be available to the workbook nor the old file property VBA functions
' so also get the file system object


    Set fo = GetFileObject(sName)
    
'for performance get BuiltinDocumentProperties and close the book
' the properties are lost when the book closes, so abandon
' the locally defined collection and close the book after
' building vNewRow() -- hmm: could I solve that by using ByValue
' instead of ByRef? Possibly, but that would in fact add extra steps;
' this code is fairly efficient as it is
'Stop
    Set wbk = OpenHidden(sName)
    
' now build the new worksheet row


    With wbk.BuiltinDocumentProperties
    vNewRow = Array( _
        vLocationValue, _
        vBook(cmOptValue), _
        vBook(cmFileDate), _
        FileLen(sName), _
        Now(), _
        fo.DateCreated, _
        fo.DateLastAccessed, _
        fo.DateLastModified, _
        fo.Size, _
        .Item("Author").Value, _
        .Item("Last Author").Value, _
        .Item("Creation Date").Value, _
        .Item("Last save time").Value, _
        sName)
    End With
    
    CloseHidden wbk


rNextRow.Resize(1, UBound(vNewRow) - LBound(vNewRow) + 1) = vNewRow
Set rNextRow = rNextRow.Offset(1)


    Set fo = Nothing
    End If ' skip unused workbooks
    Next vBook 'iterate cListOfBooks
This code is running well, and once I instantiated the second instance of Excel the flicker went away.
Not surprisingly, if I commented out everything related to opening and working with wbk my code completes in about 15% of the time - but I don't think I'm going to invest the effort into seeing if I can get to builtin document properties using ADO.
Once again, thanks! for your help.
</restores>
 
Upvote 0
Okay - an update. It bothered me that I didn't even try your suggestion, so I did, and have a report to make.
First, in case it is relevant: I'm running Excel Version 15.0.5137.1000 on Terminal Server 2012. (Office 356 subscription)
  1. Tested my code as previously posted - I am able to open other workbooks and execute VBA macros while this code is executing. I found it interesting that manipulating another workbook and executing a couple macros had no significant impact on execution speed.
  2. (I found an error in the above code: Application.Calculation appears to be workbook (worksheet?) specific, and throws an error if I try to set it before I have an open workbook - so I simply left that code out.)
  3. I added Michael M.'s suggested code at the beginning and end of my macro and removed all code referencing the background xlApp object- execution time went from 31 to 41 seconds. AND:
    Code:
        Set OpenHidden = Application.Workbooks.Open( _
                FileName:=szFullName, _
                UpdateLinks:=False, _
                ReadOnly:=True, _
                IgnoreReadOnlyRecommended:=True, _
                Notify:=True)
    briefly creates both a taskbar Icon and an Excel application window on the desktop so the flicker did not go away. That is probably why the execution took longer.
  4. Completely unable to do anything at all with any other Excel workbook while the code is running.

Once again, thank you for your help! It is much appreciated.
Clif
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top