opening multiple .txt files from multiple folders

At my work, we have about 10 computers that save data to a text file on a
daily basis. The file names that are created at the same, so we have to save
it to a different location on the network. Filenames are in date format ex:
03-15-06.txt

I'd like to be able to put the information together, and have been doing it
manually for a while now. Right now I open each file, copy the data, and then
create a seperate single .txt file with all the data for each day. I then
open the file in Excel and use Tab, Semicolon, and Colon as delimiters.

Does anyone know if a macro can be set up to look into the seperate folders,
and open each file, copying all the data into 1 worksheet?

If this can be done, or if anyone has any suggestions, I'd appreciate it.
Thanks,

I'm using Excel 2003

"Corben" <[email protected]> wrote in message
news:[email protected]...
> At my work, we have about 10 computers that save data to a text file on a
> daily basis. The file names that are created at the same, so we have to
> save
> it to a different location on the network. Filenames are in date format
> ex:
> 03-15-06.txt
>
> I'd like to be able to put the information together, and have been doing
> it
> manually for a while now. Right now I open each file, copy the data, and
> then
> create a seperate single .txt file with all the data for each day. I then
> open the file in Excel and use Tab, Semicolon, and Colon as delimiters.
>
> Does anyone know if a macro can be set up to look into the seperate
> folders,
> and open each file, copying all the data into 1 worksheet?
>
> If this can be done, or if anyone has any suggestions, I'd appreciate it.
> Thanks,
>
> I'm using Excel 2003


The answer is "yes".

Based on my own learning style, the best way to learn how is to record a
macro as you do the steps manually. But, don't use important files while
experimenting. Rename the text files first, and use a dummy Excel sheet, not
the one you need for business purposes. After recording the macro, open the
editor to inspect it, and with the help of a VBA book (from your local
library?), begin to understand how the macro performs each step.

Record a macro when you import the real text file. You'll get the layout of
each field (text, general, date, etc).

But then you could merge your recorded macro into something like:

Option Explicit
Sub testme()

Dim myFolders As Variant
Dim iCtr As Long
Dim myDate As Date
Dim TestStr As String
Dim myFileName As String
Dim NewWks As Worksheet
Dim DestCell As Range

myFolders = Array("C:\my documents\excel\", _
"c:\temp")

myDate = Application.InputBox(Prompt:="Please enter the date", _
Default:=Format(Date, "mmmm dd, yyyy"), Type:=1)

If myDate = 0 Then
Exit Sub
End If

If Year(myDate) < 2006 _
Or Year(myDate) > 2010 Then
MsgBox "Please check your date"
Exit Sub
End If

Set NewWks = Worksheets.Add
Set DestCell = NewWks.Range("a1")

For iCtr = LBound(myFolders) To UBound(myFolders)
If Right(myFolders(iCtr), 1) <> "\" Then
myFolders(iCtr) = myFolders(iCtr) & "\"
End If

myFileName = myFolders(iCtr) & Format(myDate, "mm-dd-yy") & ".txt"
TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
If TestStr = "" Then
MsgBox myFileName & " Is missing!"
Else
Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
Comma:=False, Space:=False, Other:=True, OtherChar:=":", _
FieldInfo:=Array(1, 1)

With ActiveSheet
.UsedRange.Copy _
Destination:=DestCell
.Parent.Close savechanges:=False
End With

With NewWks
Set DestCell _
= .Cells(.Cells.SpecialCells(xlCellTypeLastCell).Row + 1, "A")
End With

End If
Next iCtr

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Corben wrote:
>
> At my work, we have about 10 computers that save data to a text file on a
> daily basis. The file names that are created at the same, so we have to save
> it to a different location on the network. Filenames are in date format ex:
> 03-15-06.txt
>
> I'd like to be able to put the information together, and have been doing it
> manually for a while now. Right now I open each file, copy the data, and then
> create a seperate single .txt file with all the data for each day. I then
> open the file in Excel and use Tab, Semicolon, and Colon as delimiters.
>
> Does anyone know if a macro can be set up to look into the seperate folders,
> and open each file, copying all the data into 1 worksheet?
>
> If this can be done, or if anyone has any suggestions, I'd appreciate it.
> Thanks,
>
> I'm using Excel 2003

--

Dave Peterson

Thank you for this exmaple and the website link.
I've had some experience with macros, so with a little bit of testing I
should be able to get this working.



"Dave Peterson" wrote:

> Record a macro when you import the real text file. You'll get the layout of
> each field (text, general, date, etc).
>
> But then you could merge your recorded macro into something like:
>
> Option Explicit
> Sub testme()
>
> Dim myFolders As Variant
> Dim iCtr As Long
> Dim myDate As Date
> Dim TestStr As String
> Dim myFileName As String
> Dim NewWks As Worksheet
> Dim DestCell As Range
>
> myFolders = Array("C:\my documents\excel\", _
> "c:\temp")
>
> myDate = Application.InputBox(Prompt:="Please enter the date", _
> Default:=Format(Date, "mmmm dd, yyyy"), Type:=1)
>
> If myDate = 0 Then
> Exit Sub
> End If
>
> If Year(myDate) < 2006 _
> Or Year(myDate) > 2010 Then
> MsgBox "Please check your date"
> Exit Sub
> End If
>
> Set NewWks = Worksheets.Add
> Set DestCell = NewWks.Range("a1")
>
> For iCtr = LBound(myFolders) To UBound(myFolders)
> If Right(myFolders(iCtr), 1) <> "\" Then
> myFolders(iCtr) = myFolders(iCtr) & "\"
> End If
>
> myFileName = myFolders(iCtr) & Format(myDate, "mm-dd-yy") & ".txt"
> TestStr = ""
> On Error Resume Next
> TestStr = Dir(myFileName)
> If TestStr = "" Then
> MsgBox myFileName & " Is missing!"
> Else
> Workbooks.OpenText Filename:=myFileName, _
> Origin:=437, StartRow:=1, DataType:=xlDelimited, _
> TextQualifier:=xlDoubleQuote, _
> ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
> Comma:=False, Space:=False, Other:=True, OtherChar:=":", _
> FieldInfo:=Array(1, 1)
>
> With ActiveSheet
> .UsedRange.Copy _
> Destination:=DestCell
> .Parent.Close savechanges:=False
> End With
>
> With NewWks
> Set DestCell _
> = .Cells(.Cells.SpecialCells(xlCellTypeLastCell).Row + 1, "A")
> End With
>
> End If
> Next iCtr
>
> End Sub
>
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
> Corben wrote:
> >
> > At my work, we have about 10 computers that save data to a text file on a
> > daily basis. The file names that are created at the same, so we have to save
> > it to a different location on the network. Filenames are in date format ex:
> > 03-15-06.txt
> >
> > I'd like to be able to put the information together, and have been doing it
> > manually for a while now. Right now I open each file, copy the data, and then
> > create a seperate single .txt file with all the data for each day. I then
> > open the file in Excel and use Tab, Semicolon, and Colon as delimiters.
> >
> > Does anyone know if a macro can be set up to look into the seperate folders,
> > and open each file, copying all the data into 1 worksheet?
> >
> > If this can be done, or if anyone has any suggestions, I'd appreciate it.
> > Thanks,
> >
> > I'm using Excel 2003
>
> --
>
> Dave Peterson
>