Error Handling on save location referenced in cell

[SammyCRX]

Hi,

This is probably a really easy one, but saving files etc. isn't my strong point (but I'd like to think I'm getting better!). In the code below I just require some error handling so that if the location referenced in cell B7 isn't valid then it'll exit out of the macro, keep everything as it is with no data loss, but prompt the user to amend the save location. Any ideas please?

Thanks,
Sam

Sub SaveFiles1()
'
' SaveFiles1 Macro
' Saves files in correct formats
'
    'Check user wants to continue
    If MsgBox("This will save all data and exit the spreadsheet - are you sure?", vbOKCancel) = vbCancel Then Exit Sub
   
    ActiveWorkbook.Unprotect Password:="CREATOR"
    Application.ScreenUpdating = False
   
    'Gets file name for Product Importer Template
    ProductImporterName = ActiveWorkbook.Name
   

'Sets out file names
    Dim myMonth As String
    myMonth = Format(Sheets("Suppliers").Range("G2"), "mmmm")
   
    Dim myTicket As String
    myTicket = Sheets("Importer Template").Range("B3")

                Dim myFilePathOriginal As String
                myFilePathOriginal = Sheets("Importer Template").Range("B7")

                Dim myFilePathImport As String
                myFilePathImport = Sheets("Importer Template").Range("B7")

                Dim myFileName As String
                myFileName = myMonth & "_" & myTicket

Application.DisplayAlerts = False

'   Adds workbook for 'Importer Template' to be copied to

    Workbooks.Add
    ImportData = ActiveWorkbook.Name
    Windows(ImportData).Activate
    'Add new sheets
    ActiveSheet.Name = "Import File"
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "CLI List"
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "Summary"
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "End Dated Products"
    'Copy data across
    Windows(ProductImporterName).Activate
    Sheets("Load File").Select
    Cells.Select
    Selection.Copy
    Windows(ImportData).Activate
    Sheets("Import File").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(ProductImporterName).Activate
    Sheets("CLI List").Select
    Cells.Select
    Selection.Copy
    Windows(ImportData).Activate
    Sheets("CLI List").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Windows(ProductImporterName).Activate
    Sheets("Summary").Select
    Cells.Select
    Selection.Copy
    Windows(ImportData).Activate
    Sheets("Summary").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Windows(ProductImporterName).Activate
    Sheets("End Dated Products").Select
    Cells.Select
    Selection.Copy
    Windows(ImportData).Activate
    Sheets("End Dated Products").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Copies formats across
    Windows(ProductImporterName).Activate
    Sheets("Load File").Select
    Columns("A:U").Select
    Selection.Copy
    Windows(ImportData).Activate
    Sheets("Import File").Select
    Columns("A:U").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A1").Select
   
'Copies formats across
    Windows(ProductImporterName).Activate
    Sheets("CLI List").Select
    Columns("A:N").Select
    Selection.Copy
    Windows(ImportData).Activate
    Sheets("CLI List").Select
    Columns("A:N").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A1").Select
   
'Copies formats across
    Windows(ProductImporterName).Activate
    Sheets("Summary").Select
    Columns("A:C").Select
    Selection.Copy
    Windows(ImportData).Activate
    Sheets("Summary").Select
    Columns("A:C").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A1").Select
   
'Copies formats across
    Windows(ProductImporterName).Activate
    Sheets("End Dated Products").Select
    Columns("A:B").Select
    Selection.Copy
    Windows(ImportData).Activate
    Sheets("End Dated Products").Select
    Columns("A:B").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A1").Select

Application.DisplayAlerts = True

    Sheets("Import File").Select
    ActiveWorkbook.SaveAs Filename:= _
        myFilePathImport & "Evonex Import Data_" & myFileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
   
   
'Clear out old data
    Windows(ProductImporterName).Activate
    Sheets("Importer Template").Select
    Application.Run "ResetSheets2"

    Application.ScreenUpdating = True

    'Close and save
    ActiveWindow.Close True
'
End Sub

 

[SammyCRX]

Perhaps the question should be - how do I check if the file path specified in a cell is valid? I've been reading up online and I'm sure it's straightforward enough, but I've had no luck so far in finding a solution.

 

[SammyCRX]

Oh well, no matter... in the end I used the excellent Ron DeBruin site to find the solution. In case it helps anyone in future:

    'Tests if the file path exists
    Dim FilePath As String
    Dim TestStr As String

    FilePath = Sheets("Importer Template").Range("B7")

    TestStr = ""
    On Error Resume Next
    TestStr = Dir(FilePath)
    On Error GoTo 0
    If TestStr = "" Then
        MsgBox "File path in cell B7 doesn't exist"
    Exit Sub
    Else
    End If