VBA move and rename file issue

[anichols]

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.

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!

 

[Joe4]

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]

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]

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:

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]

Yea, I get the same error with that one. I'm confused as to what is causing it. I have a very similar 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]

In the VB Editor, if you go to Tools -> References, what libraries does it show you having selected?

 

[anichols]

 

[Joe4]

What is the name of the module that you have placed this in?
Do you have any other VBA code in this workbook?

 

[anichols]

It's in Module 9. There is a variety of other code in the workbook.

 

[anichols]

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