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!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
What happens when you try this:
VBA Code:
Sub Test()
    Dim OldName As String, NewName As String
    OldName = "P:\Servicing\Prologt.csv"
    NewName = Sheets("DAILY02").Range("D6").Value
    MsgBox (OldName = "P:\Servicing - 2. Specific\Crest - 37\5. Reporting\c. Daily Reports\Prologue\2021\2021.03\2021.03.24\Prologt_03-24-2021.csv")
End Sub
Does it return TRUE or FALSE?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

anichols

New Member
Joined
Mar 11, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows
What happens when you try this:
VBA Code:
Sub Test()
    Dim OldName As String, NewName As String
    OldName = "P:\Servicing\Prologt.csv"
    NewName = Sheets("DAILY02").Range("D6").Value
    MsgBox (OldName = "P:\Servicing - 2. Specific\Crest - 37\5. Reporting\c. Daily Reports\Prologue\2021\2021.03\2021.03.24\Prologt_03-24-2021.csv")
End Sub
Does it return TRUE or FALSE?
False
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
That means that the value you have typed in cell D6 is NOT the same as what you had hard-coded into the VBA code.
You have some difference between the two.
That may be the cause of your errors.
 

anichols

New Member
Joined
Mar 11, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows
So I took the exact file path from the msgbox and pasted it into the cell. I reran the code and still returned false (which presumably should have returned true.)
One thing I've noticed twice when closing the file, is that even with no VBA running or paused, I will get a error popup saying run time error '9' Subscript out of range.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,931
Messages
5,639,056
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