Stop ms Access VBA opening Excel File Read-only

Question

I have an Access file which uses tables linked to an Excel file, which in itself has other links that need refreshing.
(Please please don't question the logic of that, it is a workaround for some knotty problems and genuinely the only way of doing it for now because access can't use .odc connections)
I have a button in an access form that should update the data. However when the button_onclick sub opens Excel it always opens it as read-only, and therefore breaks when trying to save the file. How do I prevent this?

I have already tried AppExcel.AlertBeforeOverwrite = False and entering the ReadOnly parameter as false on Workbook.Open but it still happens.

Code is as follows;

Private Sub btnUpdate_Click()
Dim AppExcel As Excel.Application
Dim wbConn As Excel.Workbook

Set AppExcel = CreateObject("Excel.Application")
AppExcel.Visible = True
Set wbConn = AppExcel.Workbooks.Open("Z:\Company Records\System Files\Connection Data.xlsx", True, False) 'Note that last parameter, Readonly = False
With wbConn
    .RefreshAll
    'Debug.Assert False
    AppExcel.AlertBeforeOverwriting = False 'This doesn't work
    .Save 'BREAKS HERE - message boxes in Excel because the file is Read-only
    .Close
End With
Set wbConn = Nothing
Set AppExcel = Nothing

End Sub

Have you tried using AppExcel.Workbooks.Add instead? It creates a new workbook using the workbook that you would normally be opening. This however assumes you do not need the filename to be the same. — braX
– braX, Commented Jan 27, 2021 at 18:51

Wito · Accepted Answer · 2021-01-27 22:19:19Z

1

Try adding IgnoreReadOnlyRecommended:=True

Set wbConn = AppExcel.Workbooks.Open("YourFilePath", True, False, IgnoreReadOnlyRecommended:=True)

If it does not work try directly:

Set wbConn = AppExcel.Workbooks.Open("YourFilePath", True, IgnoreReadOnlyRecommended:=True)

Another additional solution would be to always .SaveAs the file instead of using .Save for which you could change the name of your file or use ConflictResolution to overwrite the existing file

.SaveAs Filename:="YourFilePath", ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges

I suggest you add AppExcel.DisplayAlerts = False to the beginning of your code if you want to avoid the prompt messages that overwriting the file could cause

edited Jan 27, 2021 at 22:19

answered Jan 27, 2021 at 19:26

Wito

3031 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Stop ms Access VBA opening Excel File Read-only

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related