VBA Can't close read-only workbook after updating from file

Question

I want to update a read-only workbook from the file "Data.xlsx". I change the file in another application in a read-write workbook. When I try to close the read-only workbook after updating it, an error accours.

This is my code:

Option Explicit

Public xlApp As New Application
Public wb_readWrite As Workbook
Public wb_readOnly As Workbook

Sub main()
    Dim path As String
    path = ThisWorkbook.path & "\Data.xlsx"
    
    Set wb_readOnly = Workbooks.Open(path, readOnly:=True)
    Set wb_readWrite = xlApp.Workbooks.Open(path, readOnly:=False)
    
    
    wb_readWrite.Sheets(1).Cells(1, 1) = InputBox("Input your data")
    wb_readWrite.Save
    
    MsgBox "Update Now"
    wb_readOnly.UpdateFromFile
       
    wb_readWrite.Close
    wb_readOnly.Close         'Error is here
    
    Set wb_readWrite = Nothing
    Set wb_readOnly = Nothing
End Sub

"an error" is not a very useful description of what happens when you run your code. Are you running this in Excel? If Yes why the `ms-access` tag? — Tim Williams, Feb 10 '23 at 18:01
I write this in Excel, stackoverflow forced me to pick five tags... — Abu Bakr, Feb 10 '23 at 18:05
SO might ask you for "up to 5" tags but I've never seen a 5-tag requirement... — Tim Williams, Feb 10 '23 at 18:07
The point of opening a workbook read-only is that it cannot be updated. So this question is very unclear. What do you mean by updating a read-only workbook? What exactly do you expect "UpdateFromFile" to do? Where is that code? — topsail, Feb 10 '23 at 18:16
@topsail its a builtin function that restores a version of the file on the disk that has been modified since you opened it. https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.updatefromfile — Warcupine, Feb 10 '23 at 18:16
Interesting - never heard of `UpdateFromFile`. Wonder, has it always been around (I really haven't worked with vba much for about 7 years now)? Anyway, all still seems unnecessary. Why update from file then just close. Why not just close. Why are we even opening this readonly file in the first place if it is doing nothing (I see nothing that it is doing). Indeed, it is the same workbook as the read_write one (same path). Why open it twice? Also, for what it's worth, could try `wb_readOnly.Close false`. And you may be getting unhandled messages about the workbook being available. — topsail, Feb 10 '23 at 18:22
@topsail Changes to the file should only be done via a userform, but the result should be shown to the user in a read-only workbook. — Abu Bakr, Feb 10 '23 at 18:31

score 1 · Accepted Answer · answered Feb 10 '23 at 18:29

Looks like calling UpdateFromFile reloads the workbook and breaks any existing VBA references to the workbook - you need to re-establish any references after the update.

So you could use a wrapper like this for example:

Sub ReloadWorkbook(wb As Workbook)
    Dim app As Application, nm As String
    Set app = wb.Application 'in case in a different instance of Excel
    nm = wb.Name
    wb.UpdateFromFile
    Set wb = app.Workbooks(nm)
End Sub

and call

ReloadWorkbook wb_readOnly

instead of wb_readOnly.UpdateFromFile

VBA Can't close read-only workbook after updating from file

1 Answers1