Rename & Move File

[RunTime91]

Greetings ~

Trying to simply take an Excel file from one folder, Rename it and place it in another folder



The following is throwing a 53 File not Found error

Dim StrFile1 As String
Dim StrFile2 As String
Dim FSO As FileSystemObject
Set FSO = New FileSystemObject


StrFile1 = "N:\Sales\Internal\Current\SalesReportCurrent_2019-11-15-04-01-49.xlsx"
StrFile2 = "N:\Sales\Internal\Archive\SalesReportArchive_20191115.xlsx"


Debug.Print StrFile1
Debug.Print StrFile2


FSO.MoveFile "StrFile1", "StrFile2"

I have also tried: Name "StrFile1" AS "StrFile2"
Same Error

Thanks for any help...

Rt91

[RunTime91]

And just to make sure it's said - Yes both paths and file names exist to the letter

[ranman256]

use UNC paths in case users dont have the path assigned:
\\server\Sales\Internal\Current\SalesReportCur...

[RunTime91]

Hey Ranman ~ Thanks for steppin' in...

No Luck - Same 53 No File Found Error

I renamed to file in the souce folder to: Boo - just to make sure the spelling was correct

Same - 53

I'm assuming the 53 error is pointing to the file in the source folder?

[isladogs]

As an alternative, try copying the file then deleting the original.

[pbaldy]

The variables should not have quotes around them. They are being treated as literal strings.

[RunTime91]

OMG!!! Really!!!!

I must crack you up ~ Paul

Thank You Sir

[pbaldy]

LOL! I'm too busy with my own bone-headed mistakes to laugh at anyone else's.

Happy to help.

[ranman256]

name vSrc as vTarg

(it does the move)

[RunTime91]

Can't believe I'm back because of this - This is sooooooooooooo frustrating!!!

So I un-hard coded the above lines of code - added the necessary variables and boom! Error after error

Below is what I'm working with

Dim strDate1 As String
Dim StrFile1 As String


Dim strDate2 As String
Dim StrFile2 As String


Dim FSO As FileSystemObject
Set FSO = New FileSystemObject


StrDate1 = Format(Now(), "YYYY-MM-DD"
StrFile1 = "N:\Sales\Internal\Current\SalesReportCurrent_" & StrDate1 & "*" & ".xlsx"
StrDate2 = Format(Now(), "YYYYMMDD"
StrFile2 = "N:\Sales\Internal\Archive\SalesReportArchive_" & StrDate2 & ".xlsx"




Debug.Print StrFile1
Debug.Print StrFile2




FSO.MoveFile StrFile1, StrFile2

When I run the above code I receive the following: A 76 Path Not Found Error
The Debug on StrFile1 = N:\Sales\Internal\Current\SalesReportCurrent_2014-04-14*.xlsx (Seems to be treating the * as a literal string)
The Debug on StrFile2 is perfect

Here is an interesting result - When I run the below

Dim strDate1 As String
Dim StrFile1 As String


Dim strDate2 As String
Dim StrFile2 As String


Dim FSO As FileSystemObject
Set FSO = New FileSystemObject


StrDate1 = Format(Now(), "YYYY-MM-DD"
StrFile1 = Dir("N:\Sales\Internal\Current\SalesReportCurrent_" & StrDate1 & "*" & ".xlsx")
StrDate2 = Format(Now(), "YYYYMMDD"
StrFile2 = "N:\Sales\Internal\Archive\SalesReportArchive_" & StrDate2 & ".xlsx"




Debug.Print StrFile1
Debug.Print StrFile2




FSO.MoveFile StrFile1, StrFile2

The results returned are a 53 File not found error - and
the Debug on StrFile1 brings back: SalesReportCurrent_2020-04-14-06-33-43.xlsx - Which is the precise name of the file I'm targeting to move - but the path is ignored

Help??

Thank You!!

[pbaldy]

I'm confused, do you not know the name of the file? In other words, why the wildcard? The Dir() function returns the file name, not including path. If you need it and the *, then add the path back to the result.

[RunTime91]

Hey Paul ~


I was hoping you'd be out there...

Ok, the reason for the Wildcard is because part of the file name is randomly generated.

So the part of the name of each of these uploaded reports (shown in red) is a random sequence of #'s: SalesReportCurrent_2020-04-14-06-33-43.xlsx

Thus the reason I'm using: SalesReportCurrent_" & StrDate1 & "*" & ".xlsx" with the wildcard

When you mention to 'add the path back to the result' do you mean something like this:

StrDate1 = Format(Now(), "YYYY-MM-DD"
StrFile1 = "N:\Sales\Internal\Current\"
StrFile2 = Dir(StrFile1 & "SalesReportCurrent_" & StrDate1 & "*" & ".xlsx")

StrDate2 = Format(Now(), "YYYMMDD"
StrFile3 = "N:\Sales\Internal\Archive\SalesReportArchive_" & StrDate2 & ".xlsx"


FSO.MoveFile StrFile2, StrFile3

[RunTime91]

This did the trick - But is it correct

StrDate1 = Format(Now(), "YYYY-MM-DD"
StrFile1 = "N:\Sales\Internal\Current\"
StrFile2 = Dir(StrFile1 & "SalesReportCurrent_" & StrDate1 & "*" & ".xlsx")


StrDate2 = Format(Now(), "YYYMMDD"
StrFile3 = "N:\Sales\Internal\Archive\SalesReportArchive_" & StrDate2 & ".xlsx"


FSO.MoveFile StrFile1 & StrFile2, StrFile3

[pbaldy]

Well, you said this produced the correct file name:

StrFile1 = Dir("N:\Sales\Internal\Current\SalesReportCurrent_ " & StrDate1 & "*" & ".xlsx")

So I was thinking after that line:

StrFile1 = "N:\Sales\Internal\Current" & StrFile1

You could certainly use a variable for the path (I would), but the point was to add the path to the file name.

[RunTime91]

Thanks Paul!

I tried to find something online explaning the result I was getting using the Dir - but found nothing conclusive

So I didn't understand the results...Now I do

I went ahead and used a variable for both the path, and the file name of the source and of course the destination path

Thank You again so very much Paul...