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

[Clif Mac]

I'm using this code to open a workbook so I can grab some of the builtin properties.

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>

 

[Michael M]

Maybe have a read through this thread. There are a couple of answers that may suit your needs

https://stackoverflow.com/questions/25913111/vba-open-excel-when-file-is-used-by-another-user

 

[Clif Mac]

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.

 

[Clif Mac]

Okay - I found opportunity to get back to this project. Here is what I found:
Changing

    Set OpenHidden = Workbooks.Open(szFullName, UpdateLinks:=False, _
       ReadOnly:=True, Notify:=True)

to

    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

    '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!

 

[Michael M]

Ok then at the start of the code put

With application
  .displayalerts=false
  .screenupdating=false
end with

and at the end

With application
  .displayalerts=true
  .screenupdating=true
end with

that should also stop the flickering

 

[Clif Mac]

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-

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

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

 

[Clif Mac]

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:

       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