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" wrote in message
...
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