Move / Rename File

Scott13

New Member
Joined
Dec 29, 2015
Messages
31
Okay so I have tried my hand at typing some VBA and below is what I have found after doing a few days of googling.

Old File is the textbox that contains the Full Path to the file.
New File is the textbox that contains the path to the new folder where I want the file to go.

Code:
Private Sub Command24_Click()
    Dim OldFile As String
    Dim NewFile As String
    Set OldFile = Forms!frmAddDocument!txtLink.Value = ""
    Set NewFile = Forms!frmAddDocument!Text22.Value = ""
    FileCopy OldFile, NewFile
    
End Sub

But I can't get it to work. Any Help?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Old File is the textbox that contains the Full Path to the file.
New File is the textbox that contains the path to the new folder where I want the file to go.
No, OldFile and NewFile are variables that you have declared in this VBA script.

Do you mean that the old file name is found in the text box named "txtLink" and the new file name is found in text box named "Text22"?
And I assume that this button you are clicking is on the same form as these two text boxes. Is that right?

If those assumptions are correct, then your code should look like this:
Code:
Private Sub Command24_Click()
    Dim OldFile As String
    Dim NewFile As String
    OldFile = Me.txtLink
    NewFile = Me.Text22
    FileCopy OldFile, NewFile
End Sub
Note: You only use the "SET" command with setting object variables, like ranges.
 
Upvote 0
DOH!

I knew it had to be something so simple as that.

Thank you so much for the help.

Now with the same scenario, if "NewFile" is actually a folder that I want to move the file to, what to I need to enter for that?

And for a, hopefully, final question: Is it possible to "Concatenate" a couple of text fields to set as the name for the NewFile?
 
Upvote 0
Now with the same scenario, if "NewFile" is actually a folder that I want to move the file to, what to I need to enter for that?
So, do you want to keep the name of the file the same? And is the file name part of the text box holding the "old file"?
If so, you will need to extract the file name from that text box (look at the InStrRev function to locate the last backslash).
In these cases, it might be easier to have multiple text boxes: one for old file directory, one for new file directory, and one for file name.

And for a, hopefully, final question: Is it possible to "Concatenate" a couple of text fields to set as the name for the NewFile?
Of course, and it is probably easier than you think. Just use "&" to concatenate them in VBA.
 
Upvote 0
Awesome!

I got it working like a boss! Here is the Code I used:

Code:
Private Sub Command24_Click()
    Dim OldFile As String
    Dim NewFile As String
    Dim NewLocation As String
    OldFile = Me.txtLink
    NewLocation = Me.Text22


    Dim FileNumber As Integer
    FileNumber = InStrRev(OldFile, "\")
    NewFile = NewLocation & Mid(OldFile, FileNumber)


    FileCopy OldFile, NewFile
    Me.txtLink = ""
    Me.Text22 = NewFile
    MsgBox "File Rename & Move Complete"


End Sub

Now with the Concatenate problem I am facing.

In two of my text boxes that I am using to form the new file name, I have input masks in those field on the form. However when I join them together, it is not using the input mask as part of the file name. Is there a way to turn that on?

For Example, My documentNumber Field has an input mask of "ST-SPL-15-"&&&& but when the file saves, it is only using the last 4 digits and not the whole mask. Here is what I have so far:

Code:
Private Sub Command24_Click()
    Dim OldFile As String
    Dim NewFile As String
    Dim NewLocation As String


    OldFile = Me.txtLink
    NewLocation = Me.Text22


    Dim FileNumber As Integer
    FileNumber = InStrRev(OldFile, ".")
    NewFile = NewLocation & "\" & Me.txtClassCode & "\" & Me.txtDocNumber & Me.txtDocName & Mid(OldFile, FileNumber)


    FileCopy OldFile, NewFile
    Me.txtLink = ""
    Me.Text22 = NewFile
    MsgBox "File Rename & Move Complete"


End Sub
 
Upvote 0
Got IT! WOOHOO!

Thanks Joe for all the help!

Here is what I have:

Code:
Private Sub Command24_Click()
    Dim OldFile As String
    Dim NewFile As String
    Dim NewLocation As String
    Dim FileNumber As Integer
    Dim SubjectCode As String
    Dim SubjectName As String
    Dim DocNumber As String
    Dim DocName As String
    
    OldFile = Me.txtLink
    NewLocation = Me.Text22
    SubjectCode = Format(Me.txtSubjectCode, "00-00-000")
    SubjectName = Me.Text40
    DocNumber = Format(Me.txtDocNumber, "\ST-\SPL-\15-0000")
    DocName = Me.txtDocumentName
    
    FileNumber = InStrRev(OldFile, ".")
    NewFile = NewLocation & "\" & SubjectCode & "-" & SubjectName & "\" & DocNumber & "-" & DocName & Mid(OldFile, FileNumber)
      
    If Dir(NewLocation & "\" & SubjectCode & "-" & SubjectName & "\", vbDirectory) = "" Then
        MkDir (NewLocation & "\" & SubjectCode & "-" & SubjectName & "\")
    Else
    End If
           
    FileCopy OldFile, NewFile
    Me.txtLink = ""
    Me.Text22 = NewFile
    MsgBox "File Rename & Move Complete"
End Sub
 
Upvote 0
You are welcome.
Glad I was able to help!:)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

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