View Full Version : ThisWorkbook.SaveAs fails at second attempt
mike.magill
01-24-2017, 02:57 AM
I have some code that ask the user via a userform to provide the path to their SharePoint document library - the workbook is then saved to that location. I wanted to account for the user entering a spurious URL and ask them to re-enter. My code tries to save to the spurious location and if it fails repeats the user request. If they enter the location correctly second time around the SaveAs routine still throws up an error!
Rather than share all the code I've built a short routine below that demos the problem. When run (using a proper SharePoint address) the second SaveAs fails. If, however. you comment out the first SaveAs the second SaveAs works! I'm stumped. Can anyone shed any light?
Sub FirstTimeSave()
Dim Path1 As String
Dim Path2 As String
Dim FileSaveName As String
Path1 = "Replace with valid URL that you don't have rights to write to"
Path2 = "Replace with valid URL that you do have rights to write to"
FileSaveName = "Test.xlsm"
On Error Resume Next
ThisWorkbook.SaveAs Filename:=Path1 & FileSaveName _
, FileFormat:=52, CreateBackup:=False
If Err.Number <> 0 Then
Err.Clear
On Error Goto 0
Else
On Error Goto 0
End If
ThisWorkbook.SaveAs Filename:=Path2 & FileSaveName _
, FileFormat:=52, CreateBackup:=False
If Err.Number <> 0 Then
Err.Clear
On Error Goto 0
Else
On Error Goto 0
Exit Sub
End If
End Sub
I didn't play with the code much, but I think it may be the error handling. Read Rory's article On Error WTF? (http://excelmatters.com/2015/03/)
Other than that, I was wondering rather than using error handling to attempt to beat it into submission (sort of), why not just test the Path for existence?
Mark
mike.magill
01-24-2017, 04:49 AM
Other than that, I was wondering rather than using error handling to attempt to beat it into submission (sort of), why not just test the Path for existence?
Mark
Sorry, ignore the error handling, my actual code handles the errors better than this demo. Regarding testing the path's existence, the path exists it is just that the user does not have permission to write to that location. I test the URL exists in an earlier piece of my code.
Sub SafeSaver
Blah Blah
On Error Resume Next
ThisWorkbook.SaveAs Blah blah
If Err.Number = 0 then Exit Sub
MsgBox "Oops! Try Again"
SafeSaver
End Sub
At least for me, if I cannot access a folder on our network, then attempting to return a property of the folder fails, so I was thinking...
Option Explicit
Sub testSaveAsLocation()
Dim Path As String
Path = "C:\Some Folder\"
If ICanSaveHere(Path) Then
MsgBox "do stuff"
End If
End Sub
Function ICanSaveHere(ByVal Path2Test As String) As Boolean
Dim lCount As Long
On Error GoTo ErrHandler
' If the folder does not exist, OR, if the folder does exist but I'm not allowed access, an error is raised.
lCount = CreateObject("Scripting.FileSystemObject").GetFolder(Path2Test).Files.Count
On Error GoTo 0
ICanSaveHere = True
Exit Function
ErrHandler:
'Clarity only
ICanSaveHere = False
End Function
Hope that helps,
Mark
mike.magill
01-24-2017, 05:15 AM
Sorry but that doesn't work. Your code is essentially doing the same as my real code. When you 'try again', with a valid path, you still get an error.
I'm wondering if this has anything to do with this being a brand new Workbook created from a template?
mike.magill
01-24-2017, 05:26 AM
Thanks. That works for a file server path but it still doesn't work for a URL to a SharePoint site. :(
My bad and unfortunately, I don't use such so I am out-of-luck in assisting.
BTW, welcome to VBAX :hi:
Mark
I can't imagine why you are getting the error when Mark doesn't.
What does this style do?
Sub Main()
Do while Not SafeSaver
Do Events
Loop
End Sub
Function SafeSaver() as boolean
Blah Blah
On Error Resume Next
ThisWorkbook.SaveAs Blah blah
If Err.Number <> 0 Then Exit function
SafeSaver = True
End Function
Paul_Hossler
01-24-2017, 12:50 PM
I 'think' it's getting confused by error handling
Option Explicit
Sub FirstTimeSave()
Dim Path1 As String
Dim Path2 As String
Dim FileSaveName As String
Path1 = "c:\"
Path2 = "\\denpc2\q\users\public\documents\ (file://\\denpc2\q\users\public\documents\)"
FileSaveName = "My_Test.xlsm"
'Never trust users to do something the way you expect
If Right(Path1, 1) <> Application.PathSeparator Then
Path1 = Path1 & Application.PathSeparator
End If
If Right(Path2, 1) <> Application.PathSeparator Then
Path2 = Path2 & Application.PathSeparator
End If
On Error GoTo TryAnother
ThisWorkbook.SaveAs Filename:=Path1 & FileSaveName, FileFormat:=52, CreateBackup:=False
On Error GoTo 0
Exit Sub
TryAnother:
On Error GoTo GiveUp
ThisWorkbook.SaveAs Filename:=Path2 & FileSaveName, FileFormat:=52, CreateBackup:=False
On Error GoTo 0
Exit Sub
GiveUp:
MsgBox "Nothing worked"
End Sub
mike.magill
01-24-2017, 01:00 PM
Unfortunately, it is not as simple as a loop to solve this. If you stick the following code (changing the Path2 to your documents folder) into a new, unsaved worksheet and run. It will fail to save the worksheet despite Path2 being valid. Any ideas?
Sub FirstTimeSave1()
Dim Path1 As String
Dim Path2 As String
Dim FileSaveName As String
Path1 = "Any URL e.g. the BBC's website"
Path2 = "C:\Users\Mike Magill\Documents\"
FileSaveName = "Test.xlsm"
On Error Resume Next
ThisWorkbook.SaveAs Filename:=Path1 & FileSaveName _
, FileFormat:=52, CreateBackup:=False
If Err.Number <> 0 Then
Err.Clear
MsgBox "Despite not saving, the workbook's full name is now: " & ThisWorkbook.FullName
Else
Exit Sub
End If
ThisWorkbook.SaveAs Filename:=Path2 & FileSaveName _
, FileFormat:=52, CreateBackup:=False
If Err.Number <> 0 Then
Err.Clear
MsgBox "Valid second path not saved. The workbook's full name is still: " & ThisWorkbook.FullName
End If
End Sub
mike.magill
01-24-2017, 01:59 PM
If I paste your code into a brand new unsaved worksheet and replace path2 with my valid sharepoint URL I get an error on the TryAnother SaveAs.
Paul_Hossler
01-24-2017, 02:35 PM
I haven't used SharePoint for long time, but IIRC you need to upload to it, not FileSaveAs like it was a file server
http://sharepoint.stackexchange.com/questions/133197/excel-vba-code-to-upload-document-into-sharepoint-online-2013
or
http://excel-access-programmer.com/vba-code-to-uploaddownload-files-tofrom-sharepoint-library/
Fortunately, I don't have access to SharePoint anymore since I retired
Well done, Paul.
Mike, It seems you must first save the file to the hard drive, then copy it from the HD to Sharepoint.
mike.magill
01-25-2017, 01:11 AM
Well done, Paul.
Mike, It seems you must first save the file to the hard drive, then copy it from the HD to Sharepoint.
The thing is, I can save directly to SharePoint perfectly well as long as I don't first try and save to an invalid location. It seems that as soon as you skip over a SaveAs error SaveAs is broken for ever more. Try it with a couple of normal file locations and you'll see what I mean.
Paul_Hossler
01-25-2017, 07:50 AM
Well, I can't test any SharePoint
But I put this into a new workbook AND SAVED IT as 1.xlsm
Closed Excel
Opened 1.xlsm and single stepped through the macro and everything went as expected
At the end I had test.xlsm open and test.xlsm in Public\Documents
Option Explicit
Sub FirstTimeSave2()
Dim Path1 As String
Dim Path2 As String
Dim FileSaveName As String
Path1 = "https:\\www.google.com\"
Path2 = "C:\Users\Public\Documents\"
FileSaveName = "Test.xlsm"
On Error Resume Next
ThisWorkbook.SaveAs Filename:=Path1 & FileSaveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Despite not saving, the workbook's full name is now: " & ThisWorkbook.FullName
Else
Exit Sub
End If
On Error Resume Next
Kill (Path2 & FileSaveName)
ThisWorkbook.SaveAs Filename:=Path2 & FileSaveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Valid second path not saved. The workbook's full name is still: " & ThisWorkbook.FullName
Else
On Error GoTo 0
MsgBox "Valid second path worked. The workbook's full name is still: " & ThisWorkbook.FullName
End If
End Sub
mike.magill
01-25-2017, 08:20 AM
Well, I can't test any SharePoint
But I put this into a new workbook AND SAVED IT as 1.xlsm
Closed Excel
Opened 1.xlsm and single stepped through the macro and everything went as expected
At the end I had test.xlsm open and test.xlsm in Public\Documents
Option Explicit
Sub FirstTimeSave2()
Dim Path1 As String
Dim Path2 As String
Dim FileSaveName As String
Path1 = "https:\\www.google.com\"
Path2 = "C:\Users\Public\Documents\"
FileSaveName = "Test.xlsm"
On Error Resume Next
ThisWorkbook.SaveAs Filename:=Path1 & FileSaveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Despite not saving, the workbook's full name is now: " & ThisWorkbook.FullName
Else
Exit Sub
End If
On Error Resume Next
Kill (Path2 & FileSaveName)
ThisWorkbook.SaveAs Filename:=Path2 & FileSaveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Valid second path not saved. The workbook's full name is still: " & ThisWorkbook.FullName
Else
On Error GoTo 0
MsgBox "Valid second path worked. The workbook's full name is still: " & ThisWorkbook.FullName
End If
End Sub
If, however, you put the code in a new worksheet and run before saving it will fall over. In my situation, the code is in a template and the user would create a new, unsaved worksheet on which this runs.
Paul_Hossler
01-25-2017, 09:30 AM
In my situation, the code is in a template and the user would create a new, unsaved worksheet on which this runs.
That would be important to know
Option Explicit
Sub FirstTimeSave2()
Dim Path1 As String
Dim Path2 As String
Dim FileSaveName As String
Path1 = "https:\\www.google.com\"
Path2 = "C:\Users\Public\Documents\"
FileSaveName = "Test" '------------------------------------
On Error Resume Next
ThisWorkbook.SaveAs Filename:=Path1 & FileSaveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Despite not saving, the workbook's full name is now: " & ThisWorkbook.FullName
Else
Exit Sub
End If
On Error Resume Next
Kill (Path2 & FileSaveName & ".xlsm") ' ----------------------------------
ThisWorkbook.SaveAs Filename:=Path2 & FileSaveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Valid second path not saved. The workbook's full name is still: " & ThisWorkbook.FullName
Else
On Error GoTo 0
MsgBox "Valid second path worked. The workbook's full name is still: " & ThisWorkbook.FullName
End If
End Sub
Try this XLTM, just remove the .zip part of the attachment
I can't test it because Excel XP raises an untrappable error at the bad path.
mike.magill
01-25-2017, 10:40 AM
That would be important to know
Option Explicit
Sub FirstTimeSave2()
Dim Path1 As String
Dim Path2 As String
Dim FileSaveName As String
Path1 = "https:\\www.google.com\"
Path2 = "C:\Users\Public\Documents\"
FileSaveName = "Test" '------------------------------------
On Error Resume Next
ThisWorkbook.SaveAs Filename:=Path1 & FileSaveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Despite not saving, the workbook's full name is now: " & ThisWorkbook.FullName
Else
Exit Sub
End If
On Error Resume Next
Kill (Path2 & FileSaveName & ".xlsm") ' ----------------------------------
ThisWorkbook.SaveAs Filename:=Path2 & FileSaveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Valid second path not saved. The workbook's full name is still: " & ThisWorkbook.FullName
Else
On Error GoTo 0
MsgBox "Valid second path worked. The workbook's full name is still: " & ThisWorkbook.FullName
End If
End Sub
Try this XLTM, just remove the .zip part of the attachment
Apologies for not making the problem clear enough and thanks for the input. The above code doesn't quite work but if I change the Kill command to Path1 it frees me up to SaveAs again. I still need to test this in my real code. I'll come back to confirm it works soon.
Once again thanks for all the help.
Paul_Hossler
01-25-2017, 01:01 PM
Apologies for not making the problem clear enough and thanks for the input. The above code doesn't quite work but if I change the Kill command to Path1 it frees me up to SaveAs again. I still need to test this in my real code. I'll come back to confirm it works soon.
1. Is OK
2. If Path2 & FileSaveName & ".xlsm" already exists you'll get an Alert to overwrite it
Did the Test_Save.xltm work?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.