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.com...

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.com...

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\BAR\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.com...

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:\Documents and Settings\Me\Desktop\Testing*.txt")

DoCmd.TransferText 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\BAR\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:\Documents and Settings\Me\Desktop\Testing*.txt")

DoCmd.TransferText acImportDelim, "", "TestTable", "C:\Documents and
Settings\Me\Desktop\" & 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.com...

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:\Documents and Settings\Me\Desktop\Testing*.txt")

DoCmd.TransferText 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\BAR\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_djsteele@NOSPAM_canada.com> wrote:

DoCmd.TransferText acImportDelim, "", "TestTable", "C:\Documents and
Settings\Me\Desktop\" & 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_djsteele@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:\Documents and Settings\Me\Desktop\Testing*.txt")

DoCmd.TransferText acImportDelim, "", "TestTable", "C:\Documents and
Settings\Me\Desktop\" & stFile, True, ""

[Douglas J. Steele]

Even if the filename has periods in it, don't you need to keep it correct so
that you can refer to the file properly? If you remove the periods, how will
you be able to transfer the file name?

Or are you saying that you want to rename the files? If so, the Name
statement lets you.

Name "C:\Documents and Settings\Me\Desktop\" & stFile As _
Replace("C:\Documents and Settings\Me\Desktop\" & stFile, ".", "")

will remove the periods, while

Name "C:\Documents and Settings\Me\Desktop\" & stFile As _
Replace("C:\Documents and Settings\Me\Desktop\" & stFile, ".", "_")

will replace the periods with underscores

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Shyguy" <sh****@aol.com> wrote in message
news:bb********************************@4ax.com...

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_djsteele@NOSPAM_canada.com> wrote:

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

stFile = Dir$("C:\Documents and Settings\Me\Desktop\Testing*.txt")

DoCmd.TransferText acImportDelim, "", "TestTable", "C:\Documents and
Settings\Me\Desktop\" & stFile, True, ""

[Shyguy]

Thank you again for all your help. I'm sorry I wasn't as clear as I
should have been. I was asking how to change the filename to have no
periods. I tried looking up the Name Statement in help, but couldn't
find anything. After adding your code the filename had no '.' s at
all, including the one between the name and "txt" so it wouldn't
import. I played around with it a bit and made it work. Probably not
the right way but it does work. I'm just thankful I don't do this for
a living. :-)

Thanks again for your help. Not just in this instance but in the past
as well.

On Sun, 13 Jun 2004 18:46:58 GMT, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

Even if the filename has periods in it, don't you need to keep it correct so
that you can refer to the file properly? If you remove the periods, how will
you be able to transfer the file name?

Or are you saying that you want to rename the files? If so, the Name
statement lets you.

Name "C:\Documents and Settings\Me\Desktop\" & stFile As _
Replace("C:\Documents and Settings\Me\Desktop\" & stFile, ".", "")

will remove the periods, while

Name "C:\Documents and Settings\Me\Desktop\" & stFile As _
Replace("C:\Documents and Settings\Me\Desktop\" & stFile, ".", "_")

will replace the periods with underscores