So I recently wrote my first VBA script which pulls in a list of text files, each containing a coma delimited file which contains and ID# and a corresponding Z value.
ex.
1,20
2,19
3,17
4,20
5,22
...
The script pulls the files into excel, one by one, and then iterates through the coma delimited file to calculate the 97th and 3rd percentile. The script calculates percentile for the entire text file, then kicks the min and max value to a text tile. Here is the kicker... each individual text files has somewhere between 3000 and 50000 entries. The smaller ones dont take so long, but the 50000 entry text files can take upwards to 45 mins. I calculated the total time to run this on all my files, upwards of 4000 text files, and yeah... it'll be done next week. I know in python you can stream things to memory, but I have been having a heck of a time trying to figure out memory streaming in VBA.
Sub run_Click()
'Clear Contents
Range("A2:B100000").Select
Selection.ClearContents
Dim i As Long, nd As Long, MaxBuilding As Integer, MinBuilding As Integer
Dim Temp(100000) As Double, Dens(100000) As Double, File_name As String, inc As Long, j As Integer, volume As Long
inc = 1
For j = 0 To inc
Do
If Sheet1.Cells(inc, 17).Value = "" Then Exit Do
File_name = Sheet1.Cells(inc, 17).Value
'fetch data from the file
Open File_name For Input As #1
Do
If EOF(1) Then Exit Do
nd = nd + 1
Input #1, Temp(nd), Dens(nd)
Loop
Close #1
'Display values on the worksheet
Sheets("Export_Output1").Select
Range("a2").Select
For i = 0 To nd
ActiveCell.Value = Temp(i)
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Dens(i)
ActiveCell.Offset(1, -1).Select
Next i
nd = -1
Erase Dens
Erase Temp
Range("a1").Select
'create a file
Open "c:\z_unit_write.txt" For Append As #2
MaxBuilding = Range("h12")
MinBuilding = Range("k12")
volume = Range("m13")
Write #2, MaxBuilding, MinBuilding, volume, File_name
Close #2
'clear Contents
Range("A2:B100000").Select
Selection.ClearContents
inc = inc + 1
Loop
Next j
End Sub
What is good about this script is that it iterates through a list of text files (in a range) until the range = "", something that took me forever to figure out how to do in a batch process. My question is, does anyone happen to know of a faster way to process all this data? Maybe instead of loading in each file and loading each coma delimited entry one at a time, you can set something in vba to load the entire list in at once?
Thanks for any tips!
aaron