Error when using Workbooks.Open with the * character

VBAam

New Member
Joined
Aug 1, 2017
Messages
3
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!
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

VBAam

New Member
Joined
Aug 1, 2017
Messages
3
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!
 

VBAam

New Member
Joined
Aug 1, 2017
Messages
3
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,396
Messages
5,444,233
Members
405,275
Latest member
RefreshSQL

This Week's Hot Topics

Top