Not able to open CSV file with partial name

[ThrottleWorks]

Hi,

I have a string, this string is partial file name. I am trying to open this file.
But macro is giving bug when am trying.

Folder is present, file name is correct, I checked it manually, do not know where my mistake is.
Can anyone please help me in this. Am getting pop-up as file moved, replaced.

Please note, full file will be something like 'DummyFileName.20200728_12345.CSV'
Files are in CSV format.
20200728 is date part, this will be changed everyday based on some rules.
Hence I am creating date part in Excel and passing DummyFileName.20200728 this as string.
12345 is random number, it can be anything.

So I know fixed parts are, DummyFileName and date part.
That is why am trying to open the file using partial name.

Public Sub Test
Dim sFile as String
sFile = "I:\Folder1\Folder2\Folder3\DummyFileName.20200728*.csv"
If sFile <> "" Then
MsgBox sFile
Workbooks.Open (sFile )
Else
MsgBox "File not found"
End If
End Sub

 

[Chihiro]

You need to use DIR function.

Sub Demo()
Dim fPath As String: fPath = "I:\Folder1\Folder2\Folder3\"
Dim fExt As String: fExt = "*.csv"
Dim fName As String

If Right(fPath, 1) <> "\" Then fPath = fPath & "\"

fName = Dir(fPath & "DummyFileName.20200728" & fExt)

Do While fName <> ""
    Workbooks.Open (fPath & fName)
    fName = Dir()
Loop

End Sub

Also, for csv, you need to be careful when opening file directly from Excel.

CSV is very dependent on source system, and will use separator, date format etc that's configured there. And your system may not interpret it correctly. It's always safer to import csv file using FreeFile() method, ADO, Legacy import Wizard, or PowerQuery. Specifying how each column should be interpreted/transformed.

 

[ThrottleWorks]

Hi @Chihiro sir, thanks a lot for the help. I am checking this and will revert with updates.

Sure sir, I will learn this meathod. Have a nice day ahead.

 

[ThrottleWorks]

Hi @Chihiro sir, it is working great. Thanks.