VBA - File Location from cell contents

Dancey_

New Member
Joined
Aug 18, 2010
Messages
41
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):

Code:
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.

Code:
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 :)
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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:
Rich (BB code):
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
 
Upvote 0
This is what the error trap gives us:

VBAerror2.jpg


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

Thanks for all your help!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top