How repeat a task on all files on a floppy drive
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.
|
All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com