By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,570 Members | 999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,570 IT Pros & Developers. It's quick & easy.

Searching for files and extracting contents

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
"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.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.