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