I have some equipment that periodicaly records some data to disk.
Unfortunatly each time it records the data it creates a new file and records
a great deal of extranious data. I use sneaker net to transfer the files to
my PC. I can open each file (theyare text), and have written a macro to
extract the data needed and place it in an excel sheet in order. However, I
must open each file change its name to the name used in the macro and run the
macro. This is almost as big a pain as printing each file and copying the
data by hand.

I see that there is a For Each ... Next structure in visual basic, but the
only example of how to use this with files is a bit confusing to me. I
learned to program 15 years ago and never learned visual anything.

In any case can someone show me how to write a loop that performs a task on
every file on a floppy drive? All files would be in the root directory if
that helps.

Thanks for any help.

I think I would copy the files from the floppy to a dedicated folder on my
harddrive--just for the improvement in speed, but you can get a list of the
names of the text files with something like:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String

'change to point at the folder to check
myPath = "a:\"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.txt")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
MsgBox myPath & myNames(fCtr)
'your code would go here
Next fCtr
End If

End Sub




ED007 wrote:

I have some equipment that periodicaly records some data to disk.
Unfortunatly each time it records the data it creates a new file and records
a great deal of extranious data. I use sneaker net to transfer the files to
my PC. I can open each file (theyare text), and have written a macro to
extract the data needed and place it in an excel sheet in order. However, I
must open each file change its name to the name used in the macro and run the
macro. This is almost as big a pain as printing each file and copying the
data by hand.

I see that there is a For Each ... Next structure in visual basic, but the
only example of how to use this with files is a bit confusing to me. I
learned to program 15 years ago and never learned visual anything.

In any case can someone show me how to write a loop that performs a task on
every file on a floppy drive? All files would be in the root directory if
that helps.

Thanks for any help.

--

Dave Peterson

And here is the one I came up with. It uses the File SaveAs dialog to let you
pick your drive and directory and is set to automatically search
subdirectories. I tried it with some very simple text files and it seems to
work. The files are opened in sequential mode. You may need to alter that
aspect if your files cannot be properly read sequentially. The data is placed
in column A of the active sheet starting a row 1. You need to alter that
portion to put the data where you want it. I would test in a new workbook and
naturally, back up your data first. I like Dave's idea of copying the disks
to the hard drive as well. I tested the code below in the click event of a
command button on sheet 1.

Roy

Option Explicit
Private Sub CommandButton1_Click()

Dim UserPath As String, myData As String
Dim x As Integer, i As Integer
Dim ThisFile As Integer, DestRow As Long
Dim myBad As Integer

On Error GoTo ErrorHandler
UserPath = Application.GetSaveAsFilename(" ", , , "Select Drive/Path To
Search") '.GetOpenFilename(, , "Select a file to open")
If UserPath = "False" Then Exit Sub
For x = Len(UserPath) To 1 Step -1
If Mid(UserPath, x, 1) = "\" Then
UserPath = Left(UserPath, x)
Exit For
End If
Next
With Application.FileSearch
.NewSearch
.LookIn = UserPath
.SearchSubFolders = True
.Filename = "*.txt"
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
For ThisFile = 1 To .FoundFiles.Count
i = FreeFile
Open .FoundFiles(ThisFile) For Input As #i
While Not EOF(i)
Input #i, myData
DestRow = DestRow + 1
Cells(DestRow, 1).Value = myData
Wend
Close i
Next ThisFile
MsgBox Str(DestRow) & " items were added to the list."
Else
MsgBox "Sorry, no " & .Filename & " files found in that location."
End If
End With
Exit Sub

ErrorHandler:
Close i
myBad = MsgBox("Procedure aborted with errors." & vbLf & vbLf & "Error" &
Str(Err) & " occurred in " & Err.Source & vbLf & Err.Description & vbLf &
vbLf & "Error occurred in file: " &
Application.FileSearch.FoundFiles(ThisFile), vbInformation, "Oh No!")
On Error GoTo 0

End Sub

"ED007" wrote:

I have some equipment that periodicaly records some data to disk.
Unfortunatly each time it records the data it creates a new file and records
a great deal of extranious data. I use sneaker net to transfer the files to
my PC. I can open each file (theyare text), and have written a macro to
extract the data needed and place it in an excel sheet in order. However, I
must open each file change its name to the name used in the macro and run the
macro. This is almost as big a pain as printing each file and copying the
data by hand.

I see that there is a For Each ... Next structure in visual basic, but the
only example of how to use this with files is a bit confusing to me. I
learned to program 15 years ago and never learned visual anything.

In any case can someone show me how to write a loop that performs a task on
every file on a floppy drive? All files would be in the root directory if
that helps.

Thanks for any help.

Thanks to you both for your help.