PDA

View Full Version : VBA coding to run macro on multiple files



rkc2009
01-13-2009, 06:22 AM
Hey everyone,

I just had a few questions as I am new to VBA on Excel. I need to create a macro that takes data from multiple files and stores the results in a table. The table is in a separate file from the files which the macro operates on and is updated as the macro operates on the said multiple files. I know how to run the code on one worksheet but I am not familiar as to how to run the code on multiple files in one folder and to store the data in a separate table. The table basically represents how many worksheets passed the given test and how many failed. For example, there is a parameter file that says a set of data passes if its above 5 and fails if below 5. I need to create a macro that compares this parameter file with each of the files selected and then updates the pass-fail table in another file. If anything I have said is not crystal clear, leave me a message. I look forward to hearing replies.

Bob Phillips
01-13-2009, 06:28 AM
This loops a folder and opens all files



Dim oFSO

Sub LoopFolders()

Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\MyTest"

Set oFSO = Nothing

End Sub


'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = oFSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

For Each file In Folder.Files
If file.Type Like "Microsoft*Excel*Worksheet*" Then
Workbooks.Open Filename:=file.Path
'do stuff
Activeworkbook.Close SaveChanges:=False
End If
Next file

End Sub

rkc2009
01-13-2009, 06:42 AM
When I ran this code, it only opened up the files in the folder which contained macros. I need to open up 100s of .csv files which contain two columns of data. Based on these columns of data, I need to run a macro that generates a x-y plot in each .csv file and closes the file. Any suggestions, anyone?

Bob Phillips
01-13-2009, 07:12 AM
Change

"Microsoft*Excel*Worksheet*"

to

"*Comma Separated*"

to open csv files

rkc2009
01-13-2009, 07:40 AM
Thanks, I got the files all to open and the ability to run the macro on multiple files. The next step in my problem is the ability to generate a table that keeps track of which files passed and which files failed. Are there any suggestions on how I could go about doing this?

rkc2009
01-13-2009, 09:30 AM
Okay, so I talked to my boss and basically, he suggested that I use an array for this task to bring it to completion. Basically, I have a master template that will display the plots in each file. There will be a next and back button programmed into the file. When the macro is initially run, the first file's 4 plots will be shown, then if u click next, the next four plots are shown (increase current position in the filearray). If you need to display the last 4 plots, you can click the back button (this will decrease the current position of the array). I understand what I need to do, but coding it is difficult for me because I'm not generally a programmer. Can anyone provide me some advice for this?

Bob Phillips
01-13-2009, 09:32 AM
Keep a counter, incrementing by 1 for items that pass, and write these to the worksheet



cntr = cntr + 1
Worksheets("Sheet1").Cells(cntr, "A").Value = file.Path