Set a right path to the excel file knowing only the first part of its name

Question

Daily generated new Excel file with data will have the following name: "RawData_today date-time.xlsx", for example: "Report_2017-04-10-10-17-42.xlsx". I'm trying to set a right Path to the file, knowing only the first part of its name and ignoring the time-part?

As was suggested by @ShaiRado and @RobinMackenzie, the following code should work properly:

Dim RawDataPath As String
Dim FolderPath As String

FolderPath = ThisWorkbook.Path & "\"
RawDataPath = FolderPath & "Report" & format(Date, "yyyy-mm-dd") & "*.xlsx"

However, I am still getting the error message, and I think the issue is not in the line above.

I looked for a file using

RawDataPath = Dir$(FolderPath & "Report" & format(Date, "yyyy-mm-dd") & "*.xlsx")

If (Len(RawDataPath) > 0) Then
   MsgBox "found " & RawDataPath
End If

Result -> the right file is found. The second part of the code:

'check if the file exists
If FileExists(RawDataPath) = False Then RawDataPath = BrowseForFile("File not found. Please select the file.")
'check if the workbook is open
If Not IsWbOpen(RawDataPath) Then Workbooks.Open RawDataPath

Check if the file exists fails. Run-time error '1004': Sorry, we couldn't find False.xlsx. Is it possible it was moved, renamed or deleted?

I don't undertand why does it look for a False.xlsx? What am I doing wrong?

Any help would be appreciated.

Using the wildcard `*` , like `RawDataPath = FolderPath & "Report" & Format(Date, "yy-mm-dd") & "*.xlsx"` — Shai Rado, Apr 10 '17 at 13:32
@ShaiRado's comment should work but there is a disconnect in your question - you say that the file name would include 2017-04-10 but your code has `format(Date, "yy-mm-dd")` so use Shai's example but with `Format(Date, "yyyy-mm-dd") & "*.xlsx"` — Robin Mackenzie, Apr 10 '17 at 14:40
@L42 it gives the option to choose the file manualy from the folder if it doesn't exist or cannot be found — Slava32, Apr 11 '17 at 09:14
What is the returned value of `RawDataPath`? Does it return the correct path? — L42, Apr 11 '17 at 09:19
well, when I used Dir$(...) the MsgBox showed me "found *the name of the excel file*", but no, actually not the whole path — Slava32, Apr 11 '17 at 09:22
it seems that vba just doesn't like wildcard "*.xlsx", because when I write the whole name of the file everything works perfect. — Slava32, Apr 11 '17 at 09:33

score 1 · Answer 1 · edited May 23 '17 at 10:30

Okay, finally have found a solution, thanks to the post:

Dim RawDataPath As String
Dim FolderPath As String

FolderPath = ThisWorkbook.Path & "\"

RawDataPath = Dir$(FolderPath & "Report" & format(Date, "yyyy-mm-dd") & "*.xlsx")
If (RawDataPath <> "") Then
    Workbooks.Open FolderPath & RawDataPath
Else
    MsgBox "not found"
End If

Note: Dir$ function returns only the name of the file, not the whole path to it.

Set a right path to the excel file knowing only the first part of its name

1 Answers1