Searching for files and extracting contents

[Mark Reed]

Hi all,
I don't know if this is do-able but you never know until you ask. A
situation at work has arisen where I have to trawl through over 500 text
files, copy the contents and then paste them into an Excel sheet where it is
analysed. Would it be possible to copy the files, paste them into a
'reserved' directory and get Access to import the contents of all the file
in one go? The first line and last line of these text files would need to be
ignored. Other than that, the contents of each file is laid out in exactly
the same format and will not change.

Any help on this would be great,

Many thanks,

Mark

[Mark Reed]

I have found some code which I am trying to use but can't get it to work.
Can anyone tell me where I am going wrong?

Private Sub Command0_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("C:\Documents and Settings\Mark\My Documents\Database
work\Workstuff\Files_to_import\")
strfile = Dir("rec*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportFixed, "Standard Output", "Table1",
"C:\Documents and Settings\Mark\My Documents\Database
work\Workstuff\Files_to_import" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "C:\Documents and Settings\Mark\My Documents\Database
work\Workstuff\Files_to_import\" & strfile
strfile = Dir
Loop

End Sub

Thanks all,
Mark

"Mark Reed" <ma*********@ntlworld.com> wrote in message
news:GO7Fc.30$%A3.10@newsfe5-win...

Hi all,
I don't know if this is do-able but you never know until you ask. A
situation at work has arisen where I have to trawl through over 500 text
files, copy the contents and then paste them into an Excel sheet where it is analysed. Would it be possible to copy the files, paste them into a
'reserved' directory and get Access to import the contents of all the file
in one go? The first line and last line of these text files would need to be ignored. Other than that, the contents of each file is laid out in exactly
the same format and will not change.

Any help on this would be great,

Many thanks,

Mark

[Pieter Linden]

"Mark Reed" <ma*********@ntlworld.com> wrote in message news:<GO7Fc.30$%A3.10@newsfe5-win>...

Hi all,
I don't know if this is do-able but you never know until you ask. A
situation at work has arisen where I have to trawl through over 500 text
files, copy the contents and then paste them into an Excel sheet where it is
analysed. Would it be possible to copy the files, paste them into a
'reserved' directory and get Access to import the contents of all the file
in one go? The first line and last line of these text files would need to be
ignored. Other than that, the contents of each file is laid out in exactly
the same format and will not change.

Any help on this would be great,

Many thanks,

Mark

As you noted, use Dir() to loop through the contents of the directory,
FileCopy to copy the file to a specific location...
If the first line and last line of the text files are easily
identifiable, you could delete those records with a query. Otherwise,
you'll have to open the file, read the contents a line at a time and
process them that way.