Open Text file using only the first 7 characters of the file name.

[Robeen]

I need to open and process the data from a text file on the network.


The first 7 characters of the file name will be the same every day.
The rest of the file name will change from day to day.
There will only be one file in the folder that has those first 7 characters.
There are a total of about 120 text files in the folder every day - give or take - including the one I need to open and read.

I would appreciate some vba help with this.

Thanks!!

[June7]

Options:

1. manually import the data with Import/Export wizard

2. programatically set a link to the file and then it is available for manipulation like a table

3. programatically open the file as an object in VBA and read each line and parse the data into a recordset of the destination table


Is this a new folder each day? Or are the files replaced every day?

[Robeen]

Hi June!
There is a new folder each day - but that is not part of what need help with.
I am used to opening text files and doing whatever I need to with the text in them etc.
What I need help with is VBA code for opening a file that I only have the first 7 characters of the file name for.
Eg: The File Name might be "ABCDEF_12345" today and "ABCDEF_s8m39kdm65" tomorrow. The "ABCDEF_" will always be the same . . . the rest of the file name is not predictable.
I wonder if I've explained well enough.

I suspect I will have to do something like:
Dim strFileName as String
strFileName = "ABCDEF_" & "*" & ".txt"
and then use a Dir statement to search through the folder in question for the one file that meets that criterion . . .
This VBA is what I need help with.
Thanks!
Robeen

[ItsMe]

Have you tried strFileName = "ABCDEF_" & "*" & ".txt" yet? If this does not provide the desired results, you may have to enumerate all of the .txt files first. Then go through your now known .txt files and find the appropriate names.

[Robeen]

Every file in that folder will always be a .txt file.
I will try the 'strFileName . . .' deal and see if I can get it working.
I am just not sure of how to use the Dir statement to complete the logic . . .

[ItsMe]

If all files in the folder are of type .txt then

strFileName = "ABCDEF_*"

should suffice

I just can't recall how I have dealt with these issues before.... just working from memory here.

[June7]

Since the 7 characters are known and will be in only one file name in the folder, that's easy part. However, if there is a new folder each day, is the new folder name known?