VBA - File Location from cell contents

[Dancey_]

Evening guys,

Wondering if you can help please, I am creating a Macro where a user can input file paths and file names and then press a button to create a back up of these files in a specified location.

This is my code when I use a fixed location (this works perfectly):

Sub Create_Backup()
If Range("B8") = "" Then
MsgBox "Please enter at least one file location and file name"
Exit Sub
Else
Application.Workbooks.Open ("X:\" & Sheets(1).Range("B8").Text & "\" & Sheets(1).Range("C8").Text)
ActiveWorkbook.SaveCopyAs "X:\Validations backup - DO NOT DELETE\backup " & _
     Format(Date, "yyyy-mm-dd") & " - " & ActiveWorkbook.Name
        ActiveWorkbook.Save
    ActiveWorkbook.Close
End If
End Sub

Now I try and be clever so several different teams can use it without any intervention from me, at the moment it is hard coded to the 'Validations' team as you can see by the file path.

I change it to this, using the same method that I use to specify the file path to open the file but I get a "400" error.

Sub Create_Backup()
If Range("B2") = "" Then
MsgBox "Please enter backup to location (Cell B2)"
Exit Sub
Else
If Range("B8") = "" Then
MsgBox "Please enter at least one file location and file name"
Exit Sub
Else
Application.Workbooks.Open ("X:\" & Sheets(1).Range("B8").Text & "\" & Sheets(1).Range("C8").Text)
ActiveWorkbook.SaveCopyAs "X:\" & Sheets(1).Range("B2").Text & "\backup " & _
     Format(Date, "yyyy-mm-dd") & " - " & ActiveWorkbook.Name
        ActiveWorkbook.Save
    ActiveWorkbook.Close
End If
End If
End Sub

I have tried removing the "\backup ", this has no affect, I have tried removing the space after backup above but no luck.

Please help

 

[Von Pookie]

What is the text in cell B2?

 

[Dancey_]

What is the text in cell B2?

This is what is in cell B2, without the quote marks "Validations backup - DO NOT DELETE". Which is the name of the folder.

Cheers

 

[Von Pookie]

Hrm. Just to be specific--what line of code is highlighted when the error occurs?

 

[Dancey_]

Hrm. Just to be specific--what line of code is highlighted when the error occurs?

None

This is a screen shot:

-- removed inline image ---

 

[Von Pookie]

I would think there has to be something, somewhere. Maybe we can make it tell us what the actual error is?

Try adding an error trap in your code and see if it tells us anything:

Sub Create_Backup()
 
On Error GoTo ErrTrap
 
If Range("B2") = "" Then
    MsgBox "Please enter backup to location (Cell B2)"
    Exit Sub
Else
    If Range("B8") = "" Then
        MsgBox "Please enter at least one file location and file name"
        Exit Sub
    Else
        Application.Workbooks.Open ("X:\" & Sheets(1).Range("B8").Text & "\" & Sheets(1).Range("C8").Text)
        ActiveWorkbook.SaveCopyAs "X:\" & Sheets(1).Range("B2").Text & "\backup " & _
        Format(Date, "yyyy-mm-dd") & " - " & ActiveWorkbook.Name
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    End If
End If

Exit Sub
ErrTrap: MsgBox "Error " & Err.Number & vbCrLf & Err.Description
 
End Sub

 

[Dancey_]

This is what the error trap gives us:

I have changed the formatting of B2 to text to check it wasn't that, and still no luck

Thanks for all your help!

 

[Von Pookie]

It appears that it is trying to use a date as the folder name, not your "Validation..." string you say is the folder name.

I think this error may be occuring when you are OPENING the workbook, not when you are saving it. What values do you have for B8 and C8?