VBA move and rename file issue

anichols

New Member
Joined
Mar 11, 2021
Messages
39
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
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.
 

anichols

New Member
Joined
Mar 11, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
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
 

anichols

New Member
Joined
Mar 11, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
In the VB Editor, if you go to Tools -> References, what libraries does it show you having selected?
 

anichols

New Member
Joined
Mar 11, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

vba.JPG
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
What is the name of the module that you have placed this in?
Do you have any other VBA code in this workbook?
 

anichols

New Member
Joined
Mar 11, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows
It's in Module 9. There is a variety of other code in the workbook.
 

anichols

New Member
Joined
Mar 11, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,129,931
Messages
5,639,064
Members
417,067
Latest member
rohitbabshet

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
Top