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.
0
ED007 (2)
8/16/2005 7:54:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
209 Views

Similar Articles

[PageSpeed] 32

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
0
petersod (12004)
8/16/2005 11:11:53 PM
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.
0
rwagner (24)
8/17/2005 12:39:44 AM
Thanks to you both for your help.

0
ED007 (2)
8/17/2005 2:01:15 PM
Reply:

Similar Artilces:

How to show all tasks on one page in PWA
How to show all tasks on one page I have a team member who is assigned more then 100 tasks and he wants to show them all on one page in vertical scrolling... how can i achieve this...i tried increasing the pixel in web part to certain height but it only increases the white area not the actual tasks and takss appears on 3 pages .... please help the view is My assignments - My tasks. Hi Imram, Is 'Show Only Current Tasks' activated and 'Expand All' deactivated? That can shorten a long list. You can’t change the web part in the way you want to, I am afra...

Can't delete file?!!?
I'm running a macro in XL2007 in Vista which is manipulating a Word document. At the end of everything, I want to delete the Word file. The Word file has been properly saved through VBA unsing SaveAs - it exists and I use the object to set ranges and such, so it's definitely a good file. But I can't delete it. I'm trying Kill and I'm trying FSO.DeleteFile. For some reason, my computer keeps flipping my file paths between C:\Users and C:\Documents and Settings, so I created two strings and am using both operations on both strings. Debug.Print shows good file p...

.mny file
I am using Money Plus Deluxe and when it creates my backup files, they are created as .mbf files. In order to switch over to Quicken I need a .mny file and can't seem to create one. No matter what I do, I get a .mbf file. I am using Windows Vista. Any suggestions would be extremely appreciated. In microsoft.public.money, DEIDennis wrote: >I am using Money Plus Deluxe and when it creates my backup files, they are >created as .mbf files. In order to switch over to Quicken I need a .mny file >and can't seem to create one. No matter what I do, I get a .mbf file. I am ...

Making a file stick
Hi, Does anyone know if there's a way of making a particular file stay o the file menu? There's one file which i need every day but sometimes it goes off th recently used file list and i have to go find it again. The thing is can't move it from where it is currently and don't really wanna put shortcut to it anywhere. Jame -- jamesrya ----------------------------------------------------------------------- jamesryan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1335 View this thread: http://www.excelforum.com/showthread.php?threadid=26914 ...

Normal.dot file
When closing Outlook, I get the error message "The file Normal.dot already exists. Do you want to replace the existing file?" I have uninstalled and re-installed, and deleted this file as well, but cannot eliminate this message. Thanks for any help ... This means you've made changes to the default Word template like margins, font settings, printer settings etc... Choose Yes to save the changes and see if it's gone now. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed w...

Repeating cell-contents
A1 contains "Smith" A53 contains "Jones" A459 contains "Smith" A1072 contains "Peterson" A23405 contains "Smith" A45234 contains "Samantha" What formula can I use to put: "Smith" in B1 thru B52 blanks in B53 thru B458 "Smith" in B459 thru B1071 blanks in A1072 thru B23404 "Smith" in A23405 thru B45233 blanks in A45234 thru A65536 Thanks, Gary Hi Gary try the following: In B1 enter =A1 in B2 enter =IF(A2="",B1,IF(B1="",A2,"")) and ...

Exporting an Outlook Task List
I am trying to export an Outlook Task list to an Excel file using the Import/Export feature in Outlook. The export does succeed, but only with a limited number of fields. One specific field that I want to export is the 'created' field. However, this field is not available in the field chooser, oddly enough. Does anyone have any ideas about how to add additional fields? thanks, Maximus_max The Created field doesn't export. Quick and dirty workaround:=20 1. Put all the items from your form in one folder.=20 2. Use a table view to display all the data fields that you want to ...

appending one Excel file to another
Hi Everyone, I'm trying to append to a annual Excel file a daily Excel file once its made available Preferably using DOS script once my user saves the Excel document under an expected name. I do this for text files and word document but I am having trouble with Excel Is there a way for the third line to work ????????????? Example. c:> del /q excel_master.xls c:> ren excel annual.xls excel_master.xls C:> copy excel_today.xls + excel_master.xls excel_annual.xls ALSO: Would this work for TEMPLATES ????? Can this be done without VB or VBA using DOS. Any ideas Tha...

copying .pst files to cd
i got a new computer, and i want to transfer my inbox and sent messages files from the old computer to the new one. however, my .pst archives do not show up in explorer or in my cd burner "explorer" and when i located the files through "find files" and then dragged them into the cd burner i/o box, it cannot see them and doesn't copy them onto the cd... how in the world do i do this seemingly simple thing: copying files onto a cd so i can transfer them to my new computer and then import them into my new computer's outlook? this is the most ridiculous thing ...

setting up tasks/reminders
I am using Outlook 2003 and want to send a reminder to several people concerning an upcoming project. I set it up in my tasks but it states that since I have it going to more than one person the task will not be updated. Also, I do not see who has accepted or declined. Is there a better way to do this? Selby wrote: > I am using Outlook 2003 and want to send a reminder to several people > concerning an upcoming project. I set it up in my tasks but it states > that > since I have it going to more than one person the task will not be > updated. > Also, I...

Outlook Task
In Outlook Task, I have alot of notes written on each task. Can I "wrap text" in the notes field of Outlook Task so that when I print "Table Style" I can see all the notes per task. Thanks ...

Printing a Repeated Section Messege like "Continued"
Setting the repeated section property in a report (Access 2007) to Yes, writes the section Heading on the next page when the detail section is not complete at the page break. I would like the bottom of the first page to print "Continued on Next Page" and/or the top of the second page to say "Continued from ...)" so the report reader knows the details are part of the previous section on the previous page, and not in an entriely new section of the report. Can this be done dynamically in a report, since some pages naturally break when a section ends and other pag...

Not able to modify another users shared tasks with owner permissio
Some of the users here have started using tasks and they have shared their tasks. Here is the senario. User A has some tasks and has given User B owner access to their tasks list. User B opens the shared tasks of User A and cannot change the names or due dates of any of the tasks. We are using outlook 2003 with a exchange 2003 server here. I have looked all over for a reason for this but havent found an answer any help would be greatly appreciate. Thanks, What *kind* of access? Haven&#8217;t tried it, but I&#8217;d bet the delegate permissions need to be set to &#8220;Ed...

Time Stamp of a Save As file Doesn't Change from Original
I use a few Excel worksheets as templates. I open them, edit them with new information, and use Save As and save them with new filenames. I've been doing this for a couple of years now and this has always worked great. About a month ago, I noticed that the date stamp and time stamp of the new files I create are always the same: 12/29/2003 9:27 PM. I can open one of the worksheets that has such a date/time stamp and save it as a different name, and it still will have that same date/time stamp. I think there must be some kind of corruption in the worksheet -- a kind of corruption that's ...

Repeating rows dont repeat fully when columns also repeat
I am repeating rows 1 through 4 and columns A through D. At least that is what I'm trying to do. On page two, the columns repeat but then the rows do not (beyond columns A through D). Is there a way to make it so that they both repeat? In re-reading the above, I'm not sure I'm making myself clear. Assume that XX's represent the repeating rows and ZZ's represent the repeating columns. This is what I'd like to see: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ZZZZZZZZZZZ YYYYYYYYYYYYYYYYYYYYYY ZZZZZ...

replace string file
I have a function that will take char buffer and dump all data into a file. How can I replace all <givenname xsi:nil='true'/> with <givenname>.</givenname> ============================= int my_block_reader(void *userdata, const char *buf, size_t len) { char *szBuffer = new char[len + 1]; memcpy(szBuffer, buf, len); szBuffer[len] = 0; dumpfile << szBuffer; delete[] szBuffer; return 0; } In article <r44ru3p72pvo62cjhr79nnfj4khttpae1p@4ax.com>, Eric Kaplan <tobycraftse@yahoo.com> wrote: >How can I replace all ><givenname xsi:n...

Initialization file for File Manager
Hey Friends I created a file manager application using CListCtrl. It has 4 columns, 1. File Name 2. Date 3. Description 4. File Path I want to save the new files added to the file manager by user. So that whenever user opens the app, user can see the files selected earlier. I think it can be done using a initialization or text file. Can anyone tell me, how to save and load into a file and then to update the CListCtrl? Thanks in advance Assume a text file. On close, save the list of files as lines in the text file On open, read the text file and add the file descriptions back in....

random numbers, how can you utilize it without repeating them
How can you use random numbers without repeating them on a worksheet? The way it is set up now is, example: =RANDBETWEEN(1,8) I would like to be able to use the random between but I don't want the numbers to repeat themselves within the worksheet. Any suggestions or Ideas? http://www.mcgimpsey.com/excel/udfs/randint.html -- Regards, Peo Sjoblom (No private emails please) "RS" <RS@discussions.microsoft.com> wrote in message news:75427E72-3EAA-4105-A65B-53F0F1213414@microsoft.com... > How can you use random numbers without repeating them on a worksheet? The >...

M04: Can't open MNY file
Yesterday at about this time I opened MS Mony 2004 and worked in it with no problem. I spent a little bit of time trying to change my password, but in the end set it back to the way it was. This morning I opened Money and I noticed something fishy about it immediately. The radio buttons for working online or not were missing. I entered my username and password and money told me that the sign-in attempt was unsuccessful. I tried repairing the file, but it won't repair without a correct password, and it says the password is incorrect. Obviously, I tried putting in the password I had playe...

Visual Studio 6 SP6 File Versions
Hello I recently downloded Service Pack 6 for Visual Studio 6. This release brings up a very important issue. What are the latest versions of MFC42.DLL and MSVCRT.DLL and what are their file time stamps? The one in Cab file is much older and lower version than what I have in my XP System directory. The one in sp has following version Service Pack MSVCRT.DLL 6.00.9782.0 the same file in my XP System Dir 7.0.2600.1106 Regards Shrishail Rana I have installed SP6 and in the System32 directory I have MSVCRT.DLL 7.0.2600.1106 MFC42.DLL 6.0.8665.0 Shrishail Rana wrote: > Hello >...

repeating cells in footer in excel
I need prepare a form with many lines. In this form I need to provide a footer (with a group of cells) for approvement. Even I can select cells to repeat in every page header there is no such a feature for footers. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http:...

Sharing violation on a file just closed
Any idea why I would get a sharing violation upon opening a file with CFile::Open on a file which was just closed without error using fclose ?? Close file code: if ( fclose ( f ) ) AfxMessageBox ( "Error on fclose of log file" ); Open code: if ( FILEIN.Open ( XFER_FILENAME_LOCAL, CFile::modeRead, &inException ) ) { // normal processing } else { inException.ReportError (); } Where the ReportError above displays: A sharing violation occurred while accessing C:\Program File\CB_Software\Program\CB_grabber.log Thanks. - Dave Can't diagnose with the info prov...

Help
Hi- I am trying to use the "save as" command to save my Publisher 2002 file as an image file (I've tried jpg, tif, gif and bmp) which will be acceptable to an advertisement firm. Unfortunately, they cannot accept MS Publisher files, so I must save it as an image. I have two problems/questions that may actually be the same underlying problem.The first is that when I save the file as one of the image files above, it heavily distorts/blurs some of the fonts to the point that it is unusable. Curiously, in some places the fonts convert fine whereas in other parts of the document the ...

How to track meetings for a task?
How would you suggest to charge meeting time to a my Timesheet task? TIA! Depends on if it's a planned meeting or informal. I typically just create a bucket task through the entire schedule for "Meetings" or book it to PM time. You may also opt to create a distinct line item bucket task for status meetings as opposed to other ad hoc meetings. If it's a big phase gate meeting, I may list that specifically as a line item, but for anything less than that, it becomes too granular for effective management. - Andrew Lavinsky Blog: http://blogs.catapultsystems...

Open a big file
I am running Excel2000 on Windows2000. I have a csv file that has a lot of rows. When I tried to open it with Excel, I got the message "file not completely loaded". What can I do about it? What I ultimately want to do is to use the Pivot table feature to analyze the data. If I import the file into multiple worksheets, can I still use Pivot table to analyze the data across multiple worksheets as if they were on the same sheet? I've never used it, but in xl2002, there's an option under Data|Pivottable that allows you to use external data. the wizard will walk you through t...