ReignEternal
New Member
- Joined
- Apr 11, 2021
- Messages
- 41
- Office Version
- 365
- Platform
- Windows
Hello,
I have a module that is set to backup my excel file when I open the excel document. The issue I have just encountered is if the file lives in a Microsoft Sharepoint location, the VBA errors out. If the file lives on my local network, it works just fine. In the code below, the text in bold is what is in error when the file lives in Sharepoint. The overall endgoal is to have this module work for my local network as well as if it lives in Sharepoint.
Thank you.
I have a module that is set to backup my excel file when I open the excel document. The issue I have just encountered is if the file lives in a Microsoft Sharepoint location, the VBA errors out. If the file lives on my local network, it works just fine. In the code below, the text in bold is what is in error when the file lives in Sharepoint. The overall endgoal is to have this module work for my local network as well as if it lives in Sharepoint.
Thank you.
VBA Code:
Private Sub Workbook_Open()
Dim FolderPath As String, FileExt As String
Dim FullFileName As String, msg As String
Dim Response As VbMsgBoxResult
Dim FileName As Variant
With ActiveWorkbook
If .Path = "" Then Exit Sub
FolderPath = ActiveWorkbook.Path & "\z -Old"
FileName = Split(ActiveWorkbook.Name, ".")
FileExt = FileName(1)
End With
FullFileName = FolderPath & "\" & _
FileName(0) & "_" & Format(Now, "mmddyyyy-hhmmss") & "." & FileName(1)
msg = "The folder or path " & vbNewLine & vbNewLine & _
FolderPath & vbNewLine & vbNewLine & _
"does not exist." & vbNewLine & vbNewLine & _
"Want to create the backup folder?"
'check if Backup folder already exists
[B] If Dir(FolderPath, vbDirectory) = vbNullString Then[/B]
'if not ask user to create folder
Response = MsgBox(msg, 36, "Folder Not Found")
If Response = vbYes Then MkDir FolderPath Else Exit Sub
End If
ActiveWorkbook.SaveCopyAs FileName:=FullFileName
MsgBox "Auto backup created", 48, "Backup"
End Sub