Hi Douglas,
sorry for the delay in responding to your reply, but just to say thanks
for the pointers and code snippet - it has really helped me out!
Many thanks
Phil
Douglas J. Steele wrote:[color=blue]
> Assuming that there will always be a 2 digit sequence number if there is a
> sequence number, something like the following untested air-code should work:
>
> Dim intDecimal As Integer
> Dim strFolder As String
> Dim strFile As String
> Dim strFileNoSequence As String
>
> strFolder = "C:\SomeFolder\SomeSubFolder\"
> strFile = Dir(strFolder & "*.txt")
> Do While Len(strFile) > 0
> intDecimal = InStr(strFile, ".txt")
> If IsNumeric(Mid(strFile, intDecimal-2, 2)) Then
> strFileNoSequence = Left(strFile, intDecimal - 3) & ".txt"
> Name strFolder & strFile As strFileNoSequence
> Else
> strFileNoSequence = strFile
> End If
>
> ' At this point, you can import strFolder & strFileNoSequence
>
> Kill strFolder & strFileNoSequence
> strFile = Dir()
> Loop
>
> --
> Doug Steele, Microsoft Access MVP
>
http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Phil Latio" <phil.latio@lycos.co.uk> wrote in message
> news:1120773480.439009.232420@z14g2000cwz.googlegr oups.com...[color=green]
> > We have despatch process which kicks out picking information into text
> > files at hourly intervals.
> >
> > The text files are named sequentially by process; 'ABC.txt',
> > 'ABC01.txt', 'ABC02.txt' for one process, or 'XYZ.txt', 'XYZ01.txt' for
> > another process.
> >
> > The folder contents would look like this;
> > ABC.txt
> > ABC01.txt
> > ABC02.txt
> > XYZ.txt
> > XYZ01.txt
> > XYZ02.tx
> > AAA.txt
> > AAA01.txt
> > AAA02.txt
> >
> > I have created a database which has a link to the first file, the one
> > that does not contain a number in the filename.
> >
> > I run a bit of code to import the data from the linked file.
> > I then go into Explorer, manually delete 'ABC.txt', and rename
> > 'ABC01.txt' to 'ABC.txt'.
> > I do this until all the text files have been imported, for each process
> > - yikes!
> >
> > The import of each set of process text files requires different bits of
> > code to manipulate them into the database.
> > So a different query would work on the ABC data to the query that works
> > on the XYZ data.
> >
> > As I sit and peck away at the keyboard like a psychotic pigeon, doing
> > this manually, I realise this is a laborious method and need to refine
> > it!
> >
> > My thoughts;
> > I think I need a SELECT CASE structure to determine the filename and
> > therefore the correct code to manipulate the data into the application.
> >
> > Where I need some guidance, is how to automate this!!
> > I want to basically automate what I do manually - import data from
> > ABC.txt, delete the text file, grab ABC01.txt rename it to ABC.txt,
> > import it, delete the ABC.txt text file, grab ABC02.txt, rename it to
> > ABC.txt and so on!
> >
> > Anyone have any ideas/thoughts as to which way I go with this?
> >
> > Any comments are gratefully appreciated.
> >
> > Thanks in advance.
> >
> > Phil
> >[/color][/color]
