VBA open file with variable name with a variable file path.

Hi guys,

I need some help. I'm trying to write a VBA code that opens a CSV file but there are two problems.

Problem 1:

The folder in which the CSV file is located changes daily based on today's date, so the file path will look something like this:

On 07-02-2018: C:/File/20180207/ CSV File
On 08-02-2018: C:/File/20180208/ CSV File
On 09-02-2018: C:/File/20180209/ CSV File
On 10-02-2018: C:/File/20180210/ CSV File

Etc. The location is basically today's date, YYYYMMDD without any hyphens or slashes.

Problem 2:

The filename of CSV file that needs to be opened also changes everyday but it always ends with the same suffix (Done), so for example:

123Done.CSV
424Done.CSV
543Done.CSV
463Done.CSV
532Done.CSV

I've managed to solve the second problem using the following code.

Dim fname As Variant
Dim myPath As String

myPath = "C:\FILE\20180207\"
fname = Dir(myPath & "*DONE.CSV")

If fname <> "" Then
Workbooks.Open (myPath & fname)
End If

The only problem is that obviously tomorrow my code won't work as the file needed will be located at C:\FILE\ 20180208\ instead of C:\FILE\ 20180207\.

Any way to set the filepath to C:\FILE\(Today's date in format YYYYMMDD) ?

Hi !

As per those variable elements just pick up the file with GetOpenFilename method, to see in VBA inner help …

I've managed to solve the second problem using the following code.

Dim fname As Variant
Dim myPath As String

myPath = "C:\FILE\20180207\"
fname = Dir(myPath & "*DONE.CSV")

If fname <> "" Then
Workbooks.Open (myPath & fname)
End If

The only problem is that obviously tomorrow my code won't work as the file needed will be located at C:\FILE\ 20180208\ instead of C:\FILE\ 20180207\.

Any way to set the filepath to C:\FILE\(Today's date in format YYYYMMDD) ?