SaveAs new worksheet fails for valid path if SaveAs failed previously

[Mike Magill]

I have a template that once opened as a new worksheet asks the user to provide the path to their SharePoint document library - the workbook should then saved to that location.

If the user provides a valid URL first time, the SaveAs command works perfectly. However, if the user provides a spurious URL, I have some error trapping that asks them to re-enter. My code first of all tries to save to the spurious location and if it fails resets the error code and repeats the user request. If they enter the location correctly second time around the SaveAs routine still throws up an error! It seems the workbook's fullname has been set to the spurious URL despite the save failing.

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 FirstTimeSave1()
   
    Dim Path1 As String
    Dim Path2 As String
    Dim FileSaveName As String

    Path1 = "http://bbc.co.uk/"
    Path2 = "Replace with URL to SharePoint document library"
   
    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

 

[PCosta87]

Hi and welcome to the forum Mike

Have you tried using:

sub FirstTimeSave1()

On Error GoTo errorhandler

...your code here...

Exit Sub
   
errorhandler:
        MsgBox "Custom error message"
        FirstTimeSave1

End sub

This way, upon encountering an error, you can skip to the message box and call back the subroutine.
Combine this with some code to allow the user to cancel, and you have a loop that keeps prompting the user to select a path and stops only when the user either cancels or inputs a valid path.

Hope this helps

 

[Mike Magill]

Hi and welcome to the forum Mike

Have you tried using:

sub FirstTimeSave1()

On Error GoTo errorhandler

...your code here...

Exit Sub
  
errorhandler:
        MsgBox "Custom error message"
        FirstTimeSave1

End sub

This way, upon encountering an error, you can skip to the message box and call back the subroutine.
Combine this with some code to allow the user to cancel, and you have a loop that keeps prompting the user to select a path and stops only when the user either cancels or inputs a valid path.

Hope this helps

Hi Thanks for the response. My real code does have a loop just as you suggest but once SaveAs has failed it will no longer work no matter how many times you loop and get a new path!

 

[PCosta87]

I'm sorry, I should have read the post with more attention

Instead of ".SaveAs", use:

.SaveCopyAs Path1 & FileSaveName

It will not keep the first invalid path in the FullName!
Not sure why it behaves that way with the ".SaveAs" method though .

Hope this helps

 

[Mike Magill]

I'm sorry, I should have read the post with more attention

Instead of ".SaveAs", use:

.SaveCopyAs Path1 & FileSaveName

It will not keep the first invalid path in the FullName!
Not sure why it behaves that way with the ".SaveAs" method though .

Hope this helps

So close! That works as a solution if the path is to a mapped drive but not if it's a SharePoint URL. Any further ideas?

 

[PCosta87]

Only thing that comes to mind is to save first to a mapped drive and then use the ".CopyFile" method to send the file to the SharePoint URL.

I can't test it but let me know if it works.