Opening files.

[Pandab2002]

I am trying to create a macro that will open a file from a directory that contains about 200 .csv files. All the files have a 3 digit numeric name such as 101.CSV and 707.CSV. Id like to be able to open the first file perform some other macros, save it as a .XLS. Then open the next file. Any suggestions?

 

[Homersim2600]

If they are in cronological order, then why not do a loop and assign the filename to variable? First you would need to get how many files were in the directory, then, you would assign this number to a variable and make the variable the condition on which the loop should end...

Dim IntFile as Integer
Dim NumberOfFiles as long
Dim FileNme as string
Dim InString as string

Do Until NumberOfFiles = 0

DoEvents
Intfile = FreeFile()
Open "c:\YourDirectory\" & FileNme for input as #IntFile
Do while not EOF(IntFile)
DoEvents
Input #IntFile, Instring
loop

NumberOfFiles = NumberOfFiles - 1
loop


To determine how many files are in the directory you can do this a few different ways, the easiest would to be to add a filelistbox to a form and set the directory to where your files are located, then simply do this...

NumberOfFiles = File1.ListCount


You can also look into the FileSystemObject, this gets a bit more complex, but with a bit of effort you will be able to work through it. I hope this helps you a bit...


LF




"As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein

 

[SonOfEmidec1100]

Heres a simple routine that should do the job, however it will process the files in the order they were created, not neccessarily in numeric order. To sort the files I would think about maybe adding the names into a hidden list box with sorted property true....

Dim strFpath As String
Dim strFName As String
Dim strRec As String
Dim fNumber As Integer

strFpath = "c:\temp\"
strFName = Dir(strFpath & "*.csv")

Do Until strFName = ""
   strFName = strFpath & strFName
   fNumber = FreeFile()
   Open strFName For Input As fNumber
   Do Until EOF(fNumber)
     Line Input #fNumber, strRec
     'do something with the row
   Loop
   Close fNumber
   strFName = Dir()
Loop

 

[Pandab2002]

Thanks. I will try these.