• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Not able to open CSV file with partial name

ThrottleWorks

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

Code:
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
 
Last edited:

Chihiro

Excel Ninja
You need to use DIR function.
Code:
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.
 
Last edited:
Top