If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Reading a text file
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 |
#3
|
|||
|
|||
Reading a text file
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. |
#4
|
|||
|
|||
Reading a text file
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. |
#5
|
|||
|
|||
Reading a text file
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. |
#6
|
|||
|
|||
Reading a text file
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. |
#7
|
|||
|
|||
Reading a text file
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. |
Thread Tools | |
Display Modes | |
|
|