PDA

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

GTO
01-24-2017, 04:09 AM
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.

SamT
01-24-2017, 05:08 AM
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

GTO
01-24-2017, 05:15 AM
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. :(

GTO
01-24-2017, 05:28 AM
My bad and unfortunately, I don't use such so I am out-of-luck in assisting.

BTW, welcome to VBAX :hi:

Mark

SamT
01-24-2017, 12:04 PM
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

SamT
01-24-2017, 05:49 PM
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

SamT
01-25-2017, 09:33 AM
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?