Can I use wildcards in TransferText import?

[Shyguy]

I need to import a text file pretty much daily. I download the file
and change the name to a standard name and then run the code to import
the file into a table in my database. The problem is that the file
starts with a standard name but adds the date and some other stuff to
the end of the file name. Is there a way I could use a wildcard like
"*" so I wouldn't have to change the name?

Thanks for any help

[Michael \(michka\) Kaplan [MS]]

No, you cannot -- when the filesystem opens a file it needs a legal name.
But you can write code to get the name that uses wildcards via the Dir()
function, and then use the name you get....
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.
"Shyguy" <sh****@aol.com > wrote in message
news:o4******** *************** *********@4ax.c om...

I need to import a text file pretty much daily. I download the file
and change the name to a standard name and then run the code to import
the file into a table in my database. The problem is that the file
starts with a standard name but adds the date and some other stuff to
the end of the file name. Is there a way I could use a wildcard like
"*" so I wouldn't have to change the name?

Thanks for any help

[Shyguy]

I'm not sure I follow. The original filename doesn't use wildcards.
it just has different endings. Are you saying I can write code to
change the file name into a specific name?

On Thu, 10 Jun 2004 20:10:42 -0700, "Michael \(michka\) Kaplan [MS]"
<mi*****@online .microsoft.com> wrote:

No, you cannot -- when the filesystem opens a file it needs a legal name.
But you can write code to get the name that uses wildcards via the Dir()
function, and then use the name you get....

[Michael \(michka\) Kaplan [MS]]

You can write code to

(1) detect the filename (using wildcards)
(2) use that detected filename in your call to TransferText
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.
"Shyguy" <sh****@aol.com > wrote in message
news:46******** *************** *********@4ax.c om...

I'm not sure I follow. The original filename doesn't use wildcards.
it just has different endings. Are you saying I can write code to
change the file name into a specific name?

On Thu, 10 Jun 2004 20:10:42 -0700, "Michael \(michka\) Kaplan [MS]"
<mi*****@online .microsoft.com> wrote:

No, you cannot -- when the filesystem opens a file it needs a legal name.
But you can write code to get the name that uses wildcards via the Dir()
function, and then use the name you get....

[Shyguy]

Thanks for your reply and patience. Being pretty much a beginner at
this, I really have no clue as the where to start this. Could you
direct me to sample code, or a starting place to search?

Thanks again
On Fri, 11 Jun 2004 10:00:18 -0700, "Michael \(michka\) Kaplan [MS]"
<mi*****@online .microsoft.com> wrote:

You can write code to

(1) detect the filename (using wildcards)
(2) use that detected filename in your call to TransferText

[Michael \(michka\) Kaplan [MS]]

As I said, the Dir() function is your friend. You can say (in VBA):

Dim stFile As String

stFile = Dir$("C:\FOO\BA R\FILE??.TXT")

and stFile will contain the first file in C:\FOO\BAR\ that matches the
pattern given.

From there you can call the TransferText method, passing stFile in as the
filename. For more info, look in the online help on the Dir$ function and
the TransferText method.
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.
"Shyguy" <sh****@aol.com > wrote in message
news:r3******** *************** *********@4ax.c om...

Thanks for your reply and patience. Being pretty much a beginner at
this, I really have no clue as the where to start this. Could you
direct me to sample code, or a starting place to search?

Thanks again
On Fri, 11 Jun 2004 10:00:18 -0700, "Michael \(michka\) Kaplan [MS]"
<mi*****@online .microsoft.com> wrote:

You can write code to

(1) detect the filename (using wildcards)
(2) use that detected filename in your call to TransferText

[Shyguy]

Thank you so much for all the help. Unfortunately I still can't get
it to work. These are the two main lines of code.

stFile = Dir$("C:\Docume nts and Settings\Me\Des ktop\Testing*.t xt")

DoCmd.TransferT ext acImportDelim, "", "TestTable" , stFile, True, ""

If I substitute stFile with the actual name of the file everything
works fine.

When I run it with the stFile, I get an error message saying that it
can't find the file, but it shows the correct file name in the error
message.
On Fri, 11 Jun 2004 21:57:14 -0700, "Michael \(michka\) Kaplan [MS]"
<mi*****@online .microsoft.com> wrote:

Dim stFile As String

stFile = Dir$("C:\FOO\BA R\FILE??.TXT")

[Douglas J. Steele]

All the Dir function returns is the name of the file without its folder. You
need to add the folder yourself.

stFile = Dir$("C:\Docume nts and Settings\Me\Des ktop\Testing*.t xt")

DoCmd.TransferT ext acImportDelim, "", "TestTable" , "C:\Documen ts and
Settings\Me\Des ktop\" & stFile, True, ""
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Shyguy" <sh****@aol.com > wrote in message
news:i3******** *************** *********@4ax.c om...

Thank you so much for all the help. Unfortunately I still can't get
it to work. These are the two main lines of code.

stFile = Dir$("C:\Docume nts and Settings\Me\Des ktop\Testing*.t xt")

DoCmd.TransferT ext acImportDelim, "", "TestTable" , stFile, True, ""

If I substitute stFile with the actual name of the file everything
works fine.

When I run it with the stFile, I get an error message saying that it
can't find the file, but it shows the correct file name in the error
message.
On Fri, 11 Jun 2004 21:57:14 -0700, "Michael \(michka\) Kaplan [MS]"
<mi*****@online .microsoft.com> wrote:

Dim stFile As String

stFile = Dir$("C:\FOO\BA R\FILE??.TXT")

[Shyguy]

Thank you, thank you, thank you. I thought that since the Dir$ was =
to the directory and file name that stFile would be = to the same.
Obviously I don't understand Dir$. ;-(

Although I do understand it a little better now.

Thanks again.

On Sat, 12 Jun 2004 11:53:25 GMT, "Douglas J. Steele"
<NOSPAM_djsteel e@NOSPAM_canada .com> wrote:

DoCmd.Transfer Text acImportDelim, "", "TestTable" , "C:\Documen ts and
Settings\Me\De sktop\" & stFile, True, ""

[Shyguy]

I ran into a slight snag. The files come in with 2 periods in
different places within the filename. Is there a way to remove the
periods with code?

On Sat, 12 Jun 2004 11:53:25 GMT, "Douglas J. Steele"
<NOSPAM_djsteel e@NOSPAM_canada .com> wrote:

All the Dir function returns is the name of the file without its folder. You
need to add the folder yourself.

stFile = Dir$("C:\Docume nts and Settings\Me\Des ktop\Testing*.t xt")

DoCmd.TransferT ext acImportDelim, "", "TestTable" , "C:\Documen ts and
Settings\Me\De sktop\" & stFile, True, ""