VBA move and rename file issue

anichols

Board Regular
Joined
Mar 11, 2021
Messages
87
Office Version
  1. 365
Platform
  1. Windows
Hello all,

I have a fairly simple code that selects a source file and then moves it to a new location and renames it. My issue is, if I put the location directly in the code it works. If I try to use a cell reference that has the same location in the cell, it doesn't. Not sure why as similar functions using the identical cell reference seems to work.

VBA Code:
Sub Prologue()
Dim OldName, NewName
'OldName = "P:\Servicing\Prologt.csv": NewName = "P:\Servicing - 2. Specific\Crest - 37\5. Reporting\c. Daily Reports\Prologue\2021\2021.03\2021.03.24\Prologt_03-24-2021.csv"
OldName = "P:\Servicing\Prologt.csv": NewName = (Sheets("DAILY02").Range("D6").Value)
Name OldName As NewName
End Sub
Any help would be greatly appreciated!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Your code worked just fine for me.

Are you sure that you have the complete string:
"P:\Servicing - 2. Specific\Crest - 37\5. Reporting\c. Daily Reports\Prologue\2021\2021.03\2021.03.24\Prologt_03-24-2021.csv"
in cell D6 on the DAILY02 sheet?
Note that any typos will cause it not to work.
 
Upvote 0
Your code worked just fine for me.

Are you sure that you have the complete string:
"P:\Servicing - 2. Specific\Crest - 37\5. Reporting\c. Daily Reports\Prologue\2021\2021.03\2021.03.24\Prologt_03-24-2021.csv"
in cell D6 on the DAILY02 sheet?
Note that any typos will cause it not to work.
It works with the manual entry. To double check, I put my location that was in VBA into the cell. I get Run-Time error '5': Invalid procedure call or argument
 
Upvote 0
That's odd. I am using Office 365, and it works just fine for me.
I am not really sure what the issue could be.

Just for kicks, try this and see if it works:
VBA Code:
Sub Prologue()
    Dim OldName As String, NewName As String
    OldName = "P:\Servicing\Prologt.csv"
    NewName = Sheets("DAILY02").Range("D6").Value
    Name OldName As NewName
End Sub
 
Upvote 0
Yea, I get the same error with that one. I'm confused as to what is causing it. I have a very similar code:
VBA Code:
Sub PrologueT()
Dim moveService
Set moveService = CreateObject("Scripting.FileSystemObject")
sSourceFile = "P:\Servicing\Prologt.csv"
sDestinationFile = (Sheets("Folders").Range("D7").Value)
moveService.MoveFile sSourceFile, sDestinationFile
End Sub
This code does nearly the same thing, it moves the same file to the same exact folder, it just doesn't rename it. Do you think it's possible to use the name function on the file after it was moved? I can easily make a cell reference that has just the file name and not the folder.
 
Upvote 0
In the VB Editor, if you go to Tools -> References, what libraries does it show you having selected?
 
Upvote 0
vba.JPG
 
Upvote 0
What is the name of the module that you have placed this in?
Do you have any other VBA code in this workbook?
 
Upvote 0
It's in Module 9. There is a variety of other code in the workbook.
 
Upvote 0
I tried a new workbook with the code and cell reference and I got the same error again. I was really hoping that was it...
 
Upvote 0

Forum statistics

Threads
1,224,606
Messages
6,179,865
Members
452,948
Latest member
UsmanAli786

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