Hello,

I need to combine information from multiple files. All
the files contain the exact same format. The difference
is each file contains a different item. I am trying to
figure out how to automatically open the data files from
Excel and insert them into Excel to create a list. Is
Excel capable of going out to my PC and opening files? If
I can do it for the first file than I would think the rest
of the files could be done through a repeatable loop.

There is a total of 111 files. This would be to painful
to do manually. Additionally the files are sent to my PC
via FTP from and Informix database daily. I need to try
and refresh this data on a daily basis. This appears to
going to be a complex macro. Some direction might be all
that I need. If anyone has some suggestions or can point
me to some websites that might go into detail this would
be great. Any help will be greatly appreciated.

Thanks,
Jeff

Ron DeBruin offers some code on opening workbooks and gathering
information.

http://www.rondebruin.nl/copy3.htm

Chip Pearson has some examples of File I/O.

http://www.cpearson.com/excel/imptext.htm

Microsoft has examples of file I/O.

http://support.microsoft.com/support...eio/fileio.asp

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

Hello,

I need to combine information from multiple files. All
the files contain the exact same format. The difference
is each file contains a different item. I am trying to
figure out how to automatically open the data files from
Excel and insert them into Excel to create a list. Is
Excel capable of going out to my PC and opening files? If
I can do it for the first file than I would think the rest
of the files could be done through a repeatable loop.

There is a total of 111 files. This would be to painful
to do manually. Additionally the files are sent to my PC
via FTP from and Informix database daily. I need to try
and refresh this data on a daily basis. This appears to
going to be a complex macro. Some direction might be all
that I need. If anyone has some suggestions or can point
me to some websites that might go into detail this would
be great. Any help will be greatly appreciated.

Thanks,
Jeff

Jeff,

If you are doing this manually, than you can record your steps to automate
the task.

There has been much code posted in this forum in opening files in a folder.
The code will loop through all the files (of a given type) in a folder and
open and work on the files.

See if the below will get you started...

Do a Google search and post back with further questions.

===================================
Here's some code from Dave Peterson

this might get you started (but I didn't rename .txt to .old. I just kept
track
of which ones were processed):


Option Explicit
Sub testme2()

Dim myfiles() As String
Dim i As Integer
Dim myfile As String
Dim myfolder As String
Dim txtwb As Workbook
Dim conswb As Workbook
Dim destcell As Range

Set conswb = Workbooks.Add(1)

myfolder = "C:\my documents\excel"

With Application.FileSearch
.NewSearch
.LookIn = myfolder
.SearchSubFolders = False
.Filename = "*.txt"
If .Execute() 0 Then
ReDim Preserve myfiles(1 To .FoundFiles.Count)
Application.StatusBar = "Found Files: " & .FoundFiles.Count
For i = 1 To .FoundFiles.Count
myfiles(i) = .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
Exit Sub
End If
End With

For i = LBound(myfiles) To UBound(myfiles)
Application.StatusBar = "Processing #" & i & ": " & myfiles(i)

Workbooks.OpenText Filename:=myfiles(i), _
DataType:=xlDelimited, Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1))
Set txtwb = ActiveWorkbook

With conswb.Worksheets(1)
If Application.CountA(.Columns("a:a")) = 0 Then
Set destcell = .Range("a1")
Else
Set destcell = .Range("a1").End(xlDown).Offset(1, 0)
===================================
And another from Rod de Bruin
Sub test()
Dim i As Long
With Application.FileSearch
.NewSearch
.LookIn = "c:\Data"
.SearchSubFolders = False
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute(msoSortOrderDescending) 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Cells(i, 2).Value = FileDateTime(.FoundFiles(i))
Cells(i, 3).Value = FileLen(.FoundFiles(i))
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
===================================

--
sb
"Jeff Armstrong" wrote in message
...
Hello,

I need to combine information from multiple files. All
the files contain the exact same format. The difference
is each file contains a different item. I am trying to
figure out how to automatically open the data files from
Excel and insert them into Excel to create a list. Is
Excel capable of going out to my PC and opening files? If
I can do it for the first file than I would think the rest
of the files could be done through a repeatable loop.

There is a total of 111 files. This would be to painful
to do manually. Additionally the files are sent to my PC
via FTP from and Informix database daily. I need to try
and refresh this data on a daily basis. This appears to
going to be a complex macro. Some direction might be all
that I need. If anyone has some suggestions or can point
me to some websites that might go into detail this would
be great. Any help will be greatly appreciated.

Thanks,
Jeff

One got truncated (and minor tweaks--long instead of integer, random
capitalizations, and ".txt" instead of "*.txt". (It seems to work better on
some versions of windows.) And used the .foundfiles(i) directly.

Option Explicit
Sub testme2()

Dim i As Long
Dim myFolder As String
Dim txtWb As Workbook
Dim consWb As Workbook
Dim DestCell As Range

Set consWb = Workbooks.Add(1)

myFolder = "C:\my documents\excel"

With Application.FileSearch
.NewSearch
.LookIn = myFolder
.SearchSubFolders = False
.Filename = ".txt"
If .Execute() 0 Then
Application.StatusBar = "Found Files: " & .FoundFiles.Count
Else
MsgBox "There were no files found."
Exit Sub
End If

For i = 1 To .FoundFiles.Count
Application.StatusBar = "Processing #" & i & ": " & .FoundFiles(i)

Workbooks.OpenText Filename:=.FoundFiles(i), _
DataType:=xlDelimited, Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1))

Set txtWb = ActiveWorkbook

With consWb.Worksheets(1)
If Application.CountA(.Columns("a:a")) = 0 Then
Set DestCell = .Range("a1")
Else
Set DestCell = .Range("a1").End(xlDown).Offset(1, 0)
End If
End With

With txtWb.Worksheets(1)
.UsedRange.Copy _
Destination:=DestCell
End With

txtWb.Close savechanges:=False

Next i
End With
Application.StatusBar = False

End Sub




steve wrote:

Jeff,

If you are doing this manually, than you can record your steps to automate
the task.

There has been much code posted in this forum in opening files in a folder.
The code will loop through all the files (of a given type) in a folder and
open and work on the files.

See if the below will get you started...

Do a Google search and post back with further questions.

===================================
Here's some code from Dave Peterson

this might get you started (but I didn't rename .txt to .old. I just kept
track
of which ones were processed):

Option Explicit
Sub testme2()

Dim myfiles() As String
Dim i As Integer
Dim myfile As String
Dim myfolder As String
Dim txtwb As Workbook
Dim conswb As Workbook
Dim destcell As Range

Set conswb = Workbooks.Add(1)

myfolder = "C:\my documents\excel"

With Application.FileSearch
.NewSearch
.LookIn = myfolder
.SearchSubFolders = False
.Filename = "*.txt"
If .Execute() 0 Then
ReDim Preserve myfiles(1 To .FoundFiles.Count)
Application.StatusBar = "Found Files: " & .FoundFiles.Count
For i = 1 To .FoundFiles.Count
myfiles(i) = .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
Exit Sub
End If
End With

For i = LBound(myfiles) To UBound(myfiles)
Application.StatusBar = "Processing #" & i & ": " & myfiles(i)

Workbooks.OpenText Filename:=myfiles(i), _
DataType:=xlDelimited, Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1))
Set txtwb = ActiveWorkbook

With conswb.Worksheets(1)
If Application.CountA(.Columns("a:a")) = 0 Then
Set destcell = .Range("a1")
Else
Set destcell = .Range("a1").End(xlDown).Offset(1, 0)
===================================
And another from Rod de Bruin
Sub test()
Dim i As Long
With Application.FileSearch
.NewSearch
.LookIn = "c:\Data"
.SearchSubFolders = False
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute(msoSortOrderDescending) 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Cells(i, 2).Value = FileDateTime(.FoundFiles(i))
Cells(i, 3).Value = FileLen(.FoundFiles(i))
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
===================================

--
sb
"Jeff Armstrong" wrote in message
...
Hello,

I need to combine information from multiple files. All
the files contain the exact same format. The difference
is each file contains a different item. I am trying to
figure out how to automatically open the data files from
Excel and insert them into Excel to create a list. Is
Excel capable of going out to my PC and opening files? If
I can do it for the first file than I would think the rest
of the files could be done through a repeatable loop.

There is a total of 111 files. This would be to painful
to do manually. Additionally the files are sent to my PC
via FTP from and Informix database daily. I need to try
and refresh this data on a daily basis. This appears to
going to be a complex macro. Some direction might be all
that I need. If anyone has some suggestions or can point
me to some websites that might go into detail this would
be great. Any help will be greatly appreciated.

Thanks,
Jeff

--

Dave Peterson