VBA to open file with date and partial name

[iitoutcast]

Hi all,

I have a file by the name ABSolute_20160718_Prod_214323598..csv
I receive this file automatically and the date changes accordingly. The last digits are random based on timestamp and hence cannot be predicted. Could someone suggest how to open this file automatically using VBA so I can do my other operations on this file.

Thanks,
Deepak K S

[GTO]

Which parts are dependable and how "dependable" are these parts? For instance, will "ABSolute_" always be the start of the filename? OR, are we just assured that the start of the filename will be n to nn letters?


Will the date part "20160718" always be within a day or two of present date?


Is this part always there: "_Prod_" ?

And is the length (number of numbers) in "214323598" always 9?

Mark

[iitoutcast]

Hi,
"ABSolute_" is fixed, "20160718" is the current date, "_Prod_" is again fixed and then "214323598" is random.

Thanks

[iitoutcast]

Also, the number of numbers seem to be always 9.

[mancubus]

and is there only one file whose name contains the current date in the folder?

[iitoutcast]

Yes, I receive one file everyday. So the file dates will be different. Alternatively I don't mind opening any one of the files if there are multiple ones.

[GTO]

Also, the number of numbers seem to be always 9.

I started the example before reading that, so that part of the pattern only required 1 or more digits.



Option Explicit

'ABSolute_20160718_Prod_214323598

Sub example()
Dim arrFileNames As Variant
Dim sFileName As String
Dim Pattern As String

'// Presuming we know the folder we're looking in...//
sFileName = Dir(ThisWorkbook.Path & "\*.csv")

'// Set a simple pattern to test for the filename //
Pattern = "^ABSolute_" & Format(Date, "yyyymmdd") & "_Prod_[0-9]+" & ".csv$"

'// If ANY .csv files...//
If Not sFileName = vbNullString Then
'// Loop until no more //
Do
'// test against our pattern, building an array of names (in case more than one) //
If TestFileNamePattern(Pattern, sFileName) Then
If Not IsArray(arrFileNames) Then
ReDim arrFileNames(0 To 0)
Else
ReDim Preserve arrFileNames(0 To UBound(arrFileNames) + 1)
End If

arrFileNames(UBound(arrFileNames)) = sFileName
End If

sFileName = Dir()
Loop While Not sFileName = vbNullString

'// Do whatever you want with the returned filename(s) //
MsgBox Join(arrFileNames, vbLf)

End If

End Sub

Function TestFileNamePattern(Pattern As String, FileName As String) As Boolean
' late-bound or early-bound
Static REX As Object 'VBScript_RegExp_55.RegExp

If REX Is Nothing Then
Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = False
.IgnoreCase = True
End With
End If

REX.Pattern = Pattern

TestFileNamePattern = REX.Test(FileName)

End Function


Change:
Pattern = "^ABSolute_" & Format(Date, "yyyymmdd") & "_Prod_[0-9]+" & ".csv$"
To:
Pattern = "^ABSolute_" & Format(Date, "yyyymmdd") & "_Prod_[0-9]{9}" & ".csv$"

To have that part match exactly nine digits.

Hope that helps,

Mark

[mdmackillop]

Perhaps this (http://www.vbaexpress.com/forum/showthread.php?56554-Find-and-open-latest-csv-file-in-given-directory) might help

[iitoutcast]

Hi Mark,

I am looking to open the file once found. I am still struggling to do it :-\

Hi mdmackillop,

I have multiple csv files in the folder, so this one need not necessarily be the latest csv. But with the given specification, there will be only one file in a day.

Thanks,
Deepak K S

[mdmackillop]

Sub Test()
fld = "C:\TestDir\"
f = fld & "ABSolute_" & Format(Date, "yyyymmdd") & "_Prod_"
Workbooks.Open Split(CreateObject("wscript.shell").exec("cmd /c dir """ & f & "*.csv"" /b/s").stdout.readall, vbCrLf)(0)
End Sub

[iitoutcast]

Sub Test()
fld = "C:\TestDir\"
f = fld & "ABSolute_" & Format(Date, "yyyymmdd") & "_Prod_"
Workbooks.Open Split(CreateObject("wscript.shell").exec("cmd /c dir """ & f & "*.csv"" /b/s").stdout.readall, vbCrLf)(0)
End Sub

This is giving the below error:-
Run-time error '9':
Subscript out of range

[iitoutcast]

Hi, I got the solution using Mark's code.

Thanks everyone :)

[mdmackillop]

Did you change the file path?

This is giving the below error:-
Run-time error '9':
Subscript out of range

[iitoutcast]

Yes, of course