PDA

View Full Version : Systematic access of files within directory



BrianH_1979
02-22-2010, 02:54 PM
Hi everyone.

I'm very new to VBA programming, although I do have experience with programming in general.

I have been presented with a problem. A number of text files (4,500 of them in fact) exist within a directory that I need to access through VBA. Using Excel, I want to systematically open each file, one at a time, do some tests on specific cells (the txt file is tab formulated so the information of interest is always in one specific cell), then depending on whether or not the data in the cell was significant, I want to discard the file or save it to another predefined location. At the end, I should end up with a directory full of files that were of interest to me. This way, the PC does the boring work of going through the 4,500 files and I don't!

I'm not asking anyone to do the work for me here, but a pointer in the right direction would be good. I'm happy with doing the cell testing and saving the required files, but I'm not sure how to systematically open each and every one of the files in the directory one at a time. I know how to set a directory of interest within VBA and how to call the open file dialogue box to select files, but instead of this I just want to tell excel what my directory of interest is and let it open every single file, one at a time, and do some specific tasks that I have in mind on them each time. Until all the files in the directory have been done.

I found some info on the internet relating to this:

Dir$([<filepath>[,<attributes>]])

Which I thought may do what I want. I'll have a look into that tomorrow. In the meantime, if anyone here can point me in the direction of some interesting reading or volunteer some potential methods that could be of interest to me... then I'd very much appreciate it!

Cheers all,

Brian

BrianH_1979
02-22-2010, 02:57 PM
I should add that I am not interested in the order in which the files are opened so I don't need to sort the files by any specific criteria, I just need to go through them all one at a time and apply the necessary tests in a repeatable fashion until all 4500-odd files have been done.

Thanks all.

geekgirlau
02-22-2010, 07:55 PM
You're on the right track! This is a very simplified example, but it should get you started.


Dim strFile As String


strFile = Dir("C:\MyPath\*.txt", vbNormal)

Do Until strFile = ""
' test actions here

strFile = Dir()
Loop


This example will loop through all the files matching the naming convention (*.txt) in the nominated folder, repeating until every file has been processed.

GTO
02-22-2010, 09:15 PM
Hi there,

You mention opening ea one. Are you planning on opening ea .txt file in Excel?

This sounds like a one-time operation, so maybe speed is not a concern. I was just thinking that using FSO and textstreams might be quicker, as you mentioned wanting ideas.

Mark

BrianH_1979
02-23-2010, 01:07 AM
You're on the right track! This is a very simplified example, but it should get you started.

This example will loop through all the files matching the naming convention (*.txt) in the nominated folder, repeating until every file has been processed.

Hi geekgirlau - thanks very much for your response. I'll take a look at that this Morning and I'll let you know how I get on!

GTO:

Thanks for your response. Excuse my ignorance but, what is FSO?

Thanks both,

Brian

GTO
02-23-2010, 01:10 AM
Hi Brian,

Sorry, FSO is generally short for the FileSystemObject Object. Sounds like I'm stuttering, but nope.

Mark

GTO
02-23-2010, 02:46 AM
Using Excel, I want to systematically open each file, one at a time, do some tests on specific cells (the txt file is tab formulated so the information of interest is always in one specific cell), then depending on whether or not the data in the cell was significant, I want to discard the file or save it to another predefined location...

BTW, in case you get stuck, you could zip a couple of the textfiles (fake data subbing for any sensitive) and state as to the test (move or kill). Might bring forth better ideas...

Mark

BrianH_1979
02-23-2010, 03:18 AM
Hi Brian,

Sorry, FSO is generally short for the FileSystemObject Object. Sounds like I'm stuttering, but nope.

Mark

Ah, okay, yes. I've just been reading up on the FileSystemObject. Thanks for that.

Okay well I've been doing a lot of reading but I think the best way to go about this is try some coding and see how far I can get with it!

I find VBA a little confusing so far, mainly because I'm used to working with C and Assembly at an Embedded level (that's my background) so working with an Object Orientated language is a little uncomfortable at the moment!

Thanks for the help so far.

Brian

BrianH_1979
02-23-2010, 10:50 AM
Hi all,

I've managed to come up with a functional solution so thanks to GeekGirlau and Mark for their help. In the end I used the FSO and textstreams to get the job done. I'm not going to claim that it's the most efficient program ever written, but it certainly does what I need and without doubt it does it a lot faster than I'd do it manually (and with less boredom too)!

Here's my current work-in-progress:


Sub ResultsAnalysis()

'==============================================
'Program: TestLimit Results Analysis
'Version: 0.1
'Author: Brian J Hoskins
'Date: February 23, 2010
'==============================================

'==============================================
'VARIABLE DECLARATIONS
'==============================================
Dim strLocation As String
Dim strFile As String
Dim ResultsStream As TextStream
Dim fso As New FileSystemObject
Dim fls As Files
Dim strNegCorona As String
Dim strPosCorona As String
Dim i As Integer
Dim intNegCoronaLower As Integer
Dim intNegCoronaUpper As Integer
Dim intPosCoronaLower As Integer
Dim intPosCoronaUpper As Integer
intNegCoronaLower = 1185 'These are the new test limits to apply
intNegCoronaUpper = 3791
intPosCoronaLower = 1126
intPosCoronaUpper = 3603
'==============================================
'APPLICATION PARAMETERS
'==============================================
'Set the Results Folder location here:
strLocation = "C:\Documents\Limits File Investigation\Sample Results\"

'==============================================
'APPLICATION
'==============================================
Set fls = fso.GetFolder(strLocation).Files 'Point fls to our directory of interest

i = 2 'Start printing data from 2nd row (header is first row)

With Worksheets("Sheet1") 'Print out the header on the first row
.Cells(1, 1) = "File Name"
.Cells(1, 2) = "File Size"
.Cells(1, 3) = "Date"
.Cells(1, 4) = "Neg Corona Pk"
.Cells(1, 5) = "Pos Corona Pk"
For Each f In fls
Set ResultsStream = fso.OpenTextFile(f, 1) 'Copy contents of results file to 'ResultsStream'
For j = 1 To 74
ResultsStream.SkipLine 'Skip 75 lines
Next j
ResultsStream.Skip (38) 'Skip 38 characters
strNegCorona = ResultsStream.Read(4) 'Read Neg Corona Result (4 characters)
ResultsStream.SkipLine 'Skip to next line
ResultsStream.Skip (38) 'Skip 38 characters
strPosCorona = ResultsStream.Read(4) 'Read Pos Corona Result (4 characters)

'Apply new test limits...
If _
strNegCorona > intNegCoronaUpper Or _
strNegCorona < intNegCoronaLower Or _
strPosCorona > intPosCoronaUpper Or _
strPosCorona < intPosCoronaLower Then
.Cells(i, 1) = f.Name 'If any results were out of spec, print out details to excel worksheet
.Cells(i, 2) = f.Size
.Cells(i, 3) = f.DateLastModified
.Cells(i, 4) = strNegCorona
.Cells(i, 5) = strPosCorona
i = i + 1
End If

Next
End With
End Sub

SamT
02-23-2010, 11:00 AM
:help

Since I'm going to need the capability in a project I'm currently working on, I was inspired to come up with this Function.

Can you Masters of VBA T-shoot it?


Static Function Process_File_In_Folder(Folder_Path_NoLastSlash As String, _
Optional ByVal File_Name_Like As String _
Default = "\*" _
Optional ByVal Connector_String As String _
Default = "." _
Optional ByVal File_Extension_NoDot As String _
Default = "*")
'A Function to return selected files from a folder one at a time
'
'Inspired by geekgirlau on VBAX to a query by BrianH_1979
'By SamT on 2/23/10
'
'Usage:
'
'In a Procedure:
'Loop until Process_File_In_Folder = ""
' Do Action on Process_File_In_Folder(Folder_Path, [Desired Options])
'Loop
'
'Process_File_In_Folder(Some_Folder) returns all files in Folder
'Process_File_In_Folder(Folder, "\*Report") returns "Folder\*Report.*"
'Process_File_In_Folder(Folder,,,"BAK") returns "Folder\*.BAK"
'
'You can use Variables instead of actual Strings for the Arguments'
'
Dim FPath As String
FPath = Folder_Path_NoLastSlash
Dim FName As String
FName = File_Name_Like
Dim FDot As String
FDot = Connector_String
Dim Fext As String
Fext = File_Extension_NoDot
'
Dim strFile As String
Dim File_Name As String
Dim Start_Flag As Boolean
Start_Flag = False
'
strFile = Dir(FPath & FName & FDot & Fext, vbNormal)
If Not(Start_Flag) Then
File_Name = strFile
Process_File_In_Folder = File_Name
Start_Flag = True
Exit Function
End If
'
Do Until File_Name = ""
FileName = Dir() 'Set File_Name = to next file in Folder
Process_File_In_Folder= File_Name
Loop
'
Process_File_In_Folder = ""
End Function



SamT