ThisWorkbook.SaveAs fails at second attempt

[mike.magill]

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]

I didn't play with the code much, but I think it may be the error handling. Read Rory's article On Error WTF?

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]

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]

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]

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]

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]

Thanks. That works for a file server path but it still doesn't work for a URL to a SharePoint site.

[GTO]

My bad and unfortunately, I don't use such so I am out-of-luck in assisting.

BTW, welcome to VBAX

Mark

[SamT]

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]

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\"
    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]

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]

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]

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/...nt-online-2013

or

http://excel-access-programmer.com/v...point-library/

Fortunately, I don't have access to SharePoint anymore since I retired

[SamT]

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]

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]

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]

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]

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]

I can't test it because Excel XP raises an untrappable error at the bad path.

[mike.magill]

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.