Error when using Workbooks.Open with the * character

[VBAam]

Hi

I have been running a code which opens an excel file whose name changes each day. The naming convention of the file is essential "Input file-[date]-[time].xlsx".

Given the time can vary, I have used the * character to specify the file name:

filesource_path = "N:\Input files"
filesource_date = Format(Date, "yyyy-mm-dd")
filesource_name = filesource_path & "Input file-" & filesource_date & "*.xlsx"

Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open(filesource_name)

This runs absolutely fine when the file is save in a folder on the network drive (N. However, I just tried to change the path to a local drive e.g. "C:\Input files" and saved a copy of the file there but then I encounter the run-time error 1004 which says "Sorry, we couldn't find C:\Input files\Input file-2017-08-01*.xlsx."

Does anyone know why this is happening?

Thanks!

 

[VBAam]

Re: Error when using Workbooks.Open with the * character - Help!

Hi

I have been running a code which opens an excel file whose name changes each day. The naming convention of the file is essential "Input file-[date]-[time].xlsx".

Given the time can vary, I have used the * character to specify the file name:

filesource_path = "N:\Input files"
filesource_date = Format(Date, "yyyy-mm-dd")
filesource_name = filesource_path & "Input file-" & filesource_date & "*.xlsx"

Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open(filesource_name)

This runs absolutely fine when the file is save in a folder on the network drive (N. However, I just tried to change the path to a local drive e.g. "C:\Input files" and saved a copy of the file there but then I encounter the run-time error 1004 which says "Sorry, we couldn't find C:\Input files\Input file-2017-08-01*.xlsx."

Does anyone know why this is happening?

Thanks!

Help!

 

[Nine Zero]

Re: Error when using Workbooks.Open with the * character - Help!

Not sure if this will help,

Try this

Using a wildcard to open an excel workbook - Stack Overflow

 

[VBAam]

Re: Error when using Workbooks.Open with the * character - Help!

Thanks Nine Zero. The odd thing is that the asterisk works when the file is saved in some locations but not others.

The bottom of that thread mentions something about UNC paths - maybe this has something to do with it?