Excel 2007 Import .txt file error 1004

[Woodstock]

I am using Excel 2007 to import a text delimited file with the path of the
file as part of the macro-recorded process.

The name of the file (example): is 123_0201_0228.txt

Since the date portion of the name changes, I changed the macro to look for
path\123*.txt

When I go to run it again, I get the following info box:

Run-time error '1004':
Microsoft Office Excel cannot access the "path\123*.txt". There are several
possible reasons.

1. The file name or path does not exist.
2. The file is being used by another program.
3. The workbook you are trying to save has the same name as a. . . (I don't
know how this sentence ends).

The portion that fails is as follows:

Cells.Select
Selection.ClearContents
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;K:\path\123*.txt" _
, Destination:=Range("$A$1"))
..Name = "123*"
- truncated to fit window -
..Refresh BackgroundQuery:=False
End With
Cells.Select

 

[Woodstock]

Just to clarify, where I am using the word "path" in my string - that is not
the actual string - but I am obligated to hide company directory information.

 

[Sheeloo]

You have to give an exact name in "TEXT;K:\path\123*.txt"

You might build a string with the right name using DATE etc. but you need to
pass an exact name

 

[Woodstock]

Well, I did change the name of my text file to simply 123.txt and had the
exact path in the macro, and got the same error.

The macro runs as soon as I open my Spreadsheet - could it be that it is a
sharing violation to a read-only SS?

 

[Sheeloo]

Did you update the .name line also?

I tested with the following and could import the file without any problem
Sub t()
Cells.Select
Selection.ClearContents
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Sheeoo\Desktop\Book2.txt" _
, Destination:=Range("$A$1"))
..Name = "Book2.txt*"
..Refresh BackgroundQuery:=False
End With
Cells.Select
End Sub

 

[Woodstock]

That worked!

thank you so much for your help.