Reading a text file

I get a file of sales information every day named Sales-2009-07-14 (today's
date). In a process outside of Access I rename the file to "TodaySales"
Access has a linked text table to "TodaySales" so I can read records from the
file. This is working fine. However, I would like to eliminate the middle
step and have Access read the data directly from Sales-2009-07-14. I believe
I can't link the file since the name changes every day - but maybe that can
be done with VBA (this whole process is automated except for this middle
step). Can anyone suggest how I can do that? Thanks for any help on this.

On Tue, 14 Jul 2009 06:48:01 -0700, dhstein wrote:

I get a file of sales information every day named Sales-2009-07-14 (today's
date). In a process outside of Access I rename the file to "TodaySales"
Access has a linked text table to "TodaySales" so I can read records from the
file. This is working fine. However, I would like to eliminate the middle
step and have Access read the data directly from Sales-2009-07-14. I believe
I can't link the file since the name changes every day - but maybe that can
be done with VBA (this whole process is automated except for this middle
step). Can anyone suggest how I can do that? Thanks for any help on this.

But you haven't told us "how" you are reading the file?

In VBA you can use
"Sales-" & Format(Date,"yyyy-mm-dd")
to return "Sales-2009-07-14" (today).
Where you use it depends upon the "how" mentioned above.

Of course you will not be able to read any previous day's file.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Take a look at the DIR command and the Name command (VBA)

Assuming the file is always in the same directory, you might use something
like the following UNTESTED code to rename the file

Public Function fRenameFile() as Boolean
Dim sPath as String
Dim sFile as String

On Error GoTo Proc_Error:

sPath = "c:\MyDirectory\"
sFile="TodaySales-" & Format(Date(),"yyyy-mm-dd")

If Len(Dir(sPath & sFile)) = 0 Then
MsgBox "Cannot find file " & sPath & sfile
fRenameFile = False
Else
Name sPath & sFile As sPath & "TodaySales"
fRenameFile = True
End if

Exit Function

Proc_Error:
MsgBox "Whoops! " & Err.Number & ": " & Err.Description
fRenameFile = False

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

dhstein wrote:
I get a file of sales information every day named Sales-2009-07-14 (today's
date). In a process outside of Access I rename the file to "TodaySales"
Access has a linked text table to "TodaySales" so I can read records from the
file. This is working fine. However, I would like to eliminate the middle
step and have Access read the data directly from Sales-2009-07-14. I believe
I can't link the file since the name changes every day - but maybe that can
be done with VBA (this whole process is automated except for this middle
step). Can anyone suggest how I can do that? Thanks for any help on this.

John,

Thanks for your reply - and I will most likely adopt your suggestion. But
I was assuming (hoping) that I wouldn't have to rename the file - that there
is some way for Access to read a text file directly. My concern is that it
seems a little "klugey" to require an intermediate file name - I would expect
that this fairly ordinary concept of reading the text file is something that
wouldn't require this other step. So I'm wondering if there is a way. Maybe
it's just that Access is oriented to database processes and the idea of
reading a file is a little outside of the typical database concept and
therefore we need to implement something like this rename of the file. Again
thanks for your help.

"John Spencer" wrote:

Take a look at the DIR command and the Name command (VBA)

Assuming the file is always in the same directory, you might use something
like the following UNTESTED code to rename the file

Public Function fRenameFile() as Boolean
Dim sPath as String
Dim sFile as String

On Error GoTo Proc_Error:

sPath = "c:\MyDirectory\"
sFile="TodaySales-" & Format(Date(),"yyyy-mm-dd")

If Len(Dir(sPath & sFile)) = 0 Then
MsgBox "Cannot find file " & sPath & sfile
fRenameFile = False
Else
Name sPath & sFile As sPath & "TodaySales"
fRenameFile = True
End if

Exit Function

Proc_Error:
MsgBox "Whoops! " & Err.Number & ": " & Err.Description
fRenameFile = False

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

dhstein wrote:
I get a file of sales information every day named Sales-2009-07-14 (today's
date). In a process outside of Access I rename the file to "TodaySales"
Access has a linked text table to "TodaySales" so I can read records from the
file. This is working fine. However, I would like to eliminate the middle
step and have Access read the data directly from Sales-2009-07-14. I believe
I can't link the file since the name changes every day - but maybe that can
be done with VBA (this whole process is automated except for this middle
step). Can anyone suggest how I can do that? Thanks for any help on this.

Well, if you don't want to link the file, you could use VBA to open the file
and read the file line by line and populate a table in Access.

Or perhaps you could edit the connect string

Or use VBA to create the link to the text file.

or Import the data into a table

I answered what I thought was your question and that was how to rename the file.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

dhstein wrote:
John,

Thanks for your reply - and I will most likely adopt your suggestion. But
I was assuming (hoping) that I wouldn't have to rename the file - that there
is some way for Access to read a text file directly. My concern is that it
seems a little "klugey" to require an intermediate file name - I would expect
that this fairly ordinary concept of reading the text file is something that
wouldn't require this other step. So I'm wondering if there is a way. Maybe
it's just that Access is oriented to database processes and the idea of
reading a file is a little outside of the typical database concept and
therefore we need to implement something like this rename of the file. Again
thanks for your help.

"John Spencer" wrote:

Take a look at the DIR command and the Name command (VBA)

Assuming the file is always in the same directory, you might use something
like the following UNTESTED code to rename the file

Public Function fRenameFile() as Boolean
Dim sPath as String
Dim sFile as String

On Error GoTo Proc_Error:

sPath = "c:\MyDirectory\"
sFile="TodaySales-" & Format(Date(),"yyyy-mm-dd")

If Len(Dir(sPath & sFile)) = 0 Then
MsgBox "Cannot find file " & sPath & sfile
fRenameFile = False
Else
Name sPath & sFile As sPath & "TodaySales"
fRenameFile = True
End if

Exit Function

Proc_Error:
MsgBox "Whoops! " & Err.Number & ": " & Err.Description
fRenameFile = False

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

dhstein wrote:
I get a file of sales information every day named Sales-2009-07-14 (today's
date). In a process outside of Access I rename the file to "TodaySales"
Access has a linked text table to "TodaySales" so I can read records from the
file. This is working fine. However, I would like to eliminate the middle
step and have Access read the data directly from Sales-2009-07-14. I believe
I can't link the file since the name changes every day - but maybe that can
be done with VBA (this whole process is automated except for this middle
step). Can anyone suggest how I can do that? Thanks for any help on this.

On Jul 14, 12:39*pm, dhstein
wrote:
John,

* Thanks for your reply - and I will most likely adopt your suggestion. *But
I was assuming (hoping) that I wouldn't have to rename the file - that there
is some way for Access to read a text file directly. *My concern is that it
seems a little "klugey" to require an intermediate file name - I would expect
that this fairly ordinary concept of reading the text file is something that
wouldn't require this other step. *So I'm wondering if there is a way. *Maybe
it's just that Access is oriented to database processes and the idea of
reading a file is a little outside of the typical database concept and
therefore we need to implement something like this rename of the file. *Again
thanks for your help.

"John Spencer" wrote:
Take a look at the DIR command and the Name command (VBA)

Assuming the file is always in the same directory, you might use something
like the following UNTESTED code to rename the file

Public Function fRenameFile() as Boolean
Dim sPath as String
Dim sFile as String

On Error GoTo Proc_Error:

sPath = "c:\MyDirectory\"
sFile="TodaySales-" & Format(Date(),"yyyy-mm-dd")

If Len(Dir(sPath & sFile)) = 0 Then
* * MsgBox "Cannot find file " & sPath & sfile
* * fRenameFile = False
Else
* * Name sPath & sFile As sPath & "TodaySales"
* * fRenameFile = True
End if

Exit Function

Proc_Error:
* * MsgBox "Whoops! " & Err.Number & ": " & Err.Description
* * fRenameFile = False

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

dhstein wrote:
I get a file of sales information every day named Sales-2009-07-14 (today's
date). *In a process outside of Access I rename the file to "TodaySales" *
Access has a linked text table to "TodaySales" so I can read records from the
file. *This is working fine. *However, I would like to eliminate the middle
step and have Access read the data directly from Sales-2009-07-14. *I believe
I can't link the file since the name changes every day - but maybe that can
be done with VBA (this whole process is automated except for this middle
step). *Can anyone suggest how I can do that? *Thanks for any help on this.

One way of doing it:
1. create a link to one of the text files, so you have the mapping
done and saved.
2. Set the Connect property to the path to the file (use John's code).
3. Run an append query that includes the file date, which will copy
the data into your permanent table.

John and Piet,
Thanks for your answers. The part that I'mhaving trouble with is the
ability to link to a text file - one where the name changes every day. I
think you suggested that that can be done but I'm not sure how to do that. I
have a feeling that it isn't worth the effort, so I will probably just
rename the file to "TodaysSales" using the code you provided - that seems
easier. Again thanks for your help with this issue.

David

"John Spencer" wrote:

Well, if you don't want to link the file, you could use VBA to open the file
and read the file line by line and populate a table in Access.

Or perhaps you could edit the connect string

Or use VBA to create the link to the text file.

or Import the data into a table

I answered what I thought was your question and that was how to rename the file.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

dhstein wrote:
John,

Thanks for your reply - and I will most likely adopt your suggestion. But
I was assuming (hoping) that I wouldn't have to rename the file - that there
is some way for Access to read a text file directly. My concern is that it
seems a little "klugey" to require an intermediate file name - I would expect
that this fairly ordinary concept of reading the text file is something that
wouldn't require this other step. So I'm wondering if there is a way. Maybe
it's just that Access is oriented to database processes and the idea of
reading a file is a little outside of the typical database concept and
therefore we need to implement something like this rename of the file. Again
thanks for your help.

"John Spencer" wrote:

Take a look at the DIR command and the Name command (VBA)

Assuming the file is always in the same directory, you might use something
like the following UNTESTED code to rename the file

Public Function fRenameFile() as Boolean
Dim sPath as String
Dim sFile as String

On Error GoTo Proc_Error:

sPath = "c:\MyDirectory\"
sFile="TodaySales-" & Format(Date(),"yyyy-mm-dd")

If Len(Dir(sPath & sFile)) = 0 Then
MsgBox "Cannot find file " & sPath & sfile
fRenameFile = False
Else
Name sPath & sFile As sPath & "TodaySales"
fRenameFile = True
End if

Exit Function

Proc_Error:
MsgBox "Whoops! " & Err.Number & ": " & Err.Description
fRenameFile = False

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

dhstein wrote:
I get a file of sales information every day named Sales-2009-07-14 (today's
date). In a process outside of Access I rename the file to "TodaySales"
Access has a linked text table to "TodaySales" so I can read records from the
file. This is working fine. However, I would like to eliminate the middle
step and have Access read the data directly from Sales-2009-07-14. I believe
I can't link the file since the name changes every day - but maybe that can
be done with VBA (this whole process is automated except for this middle
step). Can anyone suggest how I can do that? Thanks for any help on this.