Within an excel workbook I have created a routine that automatically logs the user and date when the workbook is initially opened. The Sub Workbook_Open() calls the routine.
For most users it has worked effectively for sometime. However, myself and a few users are now getting the Run-time error '1004' Method 'SaveAs' of object '_Workbook' failed error message. The code is shown below. The log file is an excel (.xlsx) file held on a central Sharepoint site which all users have read and write access to. Any ideas to overcome would be greatly appreciated.
Sub LogDetails()
Dim LR As Long
Application.ScreenUpdating = False
Workbooks.Open Filename:="http://.../Annual%20Statement%20Log/Log%20for%20Annual%20Statement.xlsx"
Set xls = CreateObject("Excel.Application")
xls.Application.DisplayAlerts = False
Workbooks("Log for Annual Statement.xlsx").Activate
Worksheets("Log").Activate
LR = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Log").Cells(LR + 1, 1) = (Environ$("Username"))
Sheets("Log").Cells(LR + 1, 2) = Now()
Workbooks("Log for Annual Statement.xlsx").SaveAs Filename:="http://.../Annual%20Statement%20Log/Log%20for%20Annual%20Statement.xlsx"
Workbooks("Log for Annual Statement.xlsx").Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
For most users it has worked effectively for sometime. However, myself and a few users are now getting the Run-time error '1004' Method 'SaveAs' of object '_Workbook' failed error message. The code is shown below. The log file is an excel (.xlsx) file held on a central Sharepoint site which all users have read and write access to. Any ideas to overcome would be greatly appreciated.
Sub LogDetails()
Dim LR As Long
Application.ScreenUpdating = False
Workbooks.Open Filename:="http://.../Annual%20Statement%20Log/Log%20for%20Annual%20Statement.xlsx"
Set xls = CreateObject("Excel.Application")
xls.Application.DisplayAlerts = False
Workbooks("Log for Annual Statement.xlsx").Activate
Worksheets("Log").Activate
LR = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Log").Cells(LR + 1, 1) = (Environ$("Username"))
Sheets("Log").Cells(LR + 1, 2) = Now()
Workbooks("Log for Annual Statement.xlsx").SaveAs Filename:="http://.../Annual%20Statement%20Log/Log%20for%20Annual%20Statement.xlsx"
Workbooks("Log for Annual Statement.xlsx").Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub