CopyFile with Wild Card Runtime Error 76

[mred003]

Hello!

I'm trying to use this code to copy a file from one location to another. I've done this before with exact file path names but because these files have an unpredictable timestamp I cannot predict the entire name.

The true file name is P01 List202108 0200.csv. I've tried adding a ? or a * in multiple places around the 0200 and I get the same error path not found error.

Any idea where I may be going wrong?

Sub CopyK()

Dim fso As Object

Set fso = CreateObject("Scripting.FileSystemObject")

fso.CopyFile "C:\Users\fypw53\Desktop\K\P01 List20210208 *.csv", [I5]

Set fso = Nothing

End Sub

 

[NdNoviceHlp]

Hi mred003 and Welcome to this Board! You can trial this...

FSO.CopyFile "C:\Users\fypw53\Desktop\K\P01 List20210208 " & "*.csv", [I5]

HTH. Dave

 

[mred003]

Thank you, unfortunately it still gave me the same error.

 

[Fluff]

What is in cell I5 on the active sheet when you run the macro?

 

[mred003]

Thank you for asking.

[I5] contains ="F:\ProcessExcellence\Analyst\2) Reporting\Daily\DNFC\DNFC AM\Automation Engine\Input Files\P01 List "&TEXT(MGMT!$C$2,"ddmmyyyy")&".csv"

Which ultimately produces:

F:\ProcessExcellence\Analyst\2) Reporting\Daily\DNFC\DNFC AM\Automation Engine\Input Files\P01 List 08022021.csv

When I write the CopyFile code w/o the wildcard it works fine.

Sub CopyK()

Dim fso As Object

Set fso = CreateObject("Scripting.FileSystemObject")

fso.CopyFile "C:\Users\fypw53\Desktop\K\P01 List20210208 0200.csv", [I5]

Set fso = Nothing

End Sub

But I cannot predict what time of days it will run which is why I'm trying to incorporate the wildcard. I've tried pulling multiple codes from forums to get it to work but so far no such luck.

 

[mred003]

That worked, but unfortunately the reason I'm going through this is to remove the 0200 and change the name to P01 List 08022021.csv instead of P01 List20210208 0200.csv.

Is that possible? Really showing my lack of VBA knowledge

 

[Fluff]

Will you only have one file that matches the partial name?

 

[mred003]

Correct. P01 List20210208 0200.csv would be the only file with that name for the day. The file drops daily into the folder.

 

[Fluff]

Ok how about

Sub mred()
   Dim Pth As String, Fname As String
   
   Pth = "C:\Users\fypw53\Desktop\K\"
   Fname = Dir(Pth & "P01 List20210208 *.csv")
   If Fname <> "" Then FileCopy Pth & Fname, [I5]

End Sub

 

[mred003]

Can I link the 20210208 to a cell in active sheet? I can make that cell dynamic using the today() function.