aaronreyna
08-24-2009, 04:04 PM
Hello everyone, I am so new it's pitiful. With less than a week of VBA 6.5 under my belt, I have run into a wall. I have been struggling and cant seem to get past this problem.
Ok, here goes.
I have about 3000 comma delimited text files, each with a ID# and a corresponding Z unit. (about 40,000 entries in each).
example:
1,20
2,22
3,30
4,15
...
When I read it into excel, I preform a quick(not always) percentile function and end up with a minimum and maximum height for the total input. I take these two numbers and write them to a text file.
I was able to successfully do this, but when I try and preform a loop based off of a list of cells each with a path name to an individual text file (ex. C:\test\test1.txt, C:\test\test2.txt...), I end up looping through the list more than the total number of text files I read in, and the output file that is written to only lists the first text file in the loop multiple times.
Sub run_Click()
'Clear Contents
Range("A2:B100000").Select
Selection.ClearContents
Dim i As Long, nd As Long, MaxBuilding As Integer, MinBuilding As Integer, q As Long
Dim Temp(100000) As Double, Dens(100000) As Double, File_name As String
q = 1
nxtc = q + 1
Range("Q1").Select
For q = 0 To nxtc
File_name = Sheet1.Cells(1, 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
Range("a1").Select
'create a file
Open "c:\test_output\test_out.txt" For Append As #2
MaxBuilding = Range("h12")
MinBuilding = Range("k12")
Write #2, MaxBuilding, MinBuilding, File_name
Close #2
'clear Contents
Range("A2:B100000").Select
Selection.ClearContents
Next q
End Sub
Sub clear()
Range("A2:B100000").Select
Selection.ClearContents
End Sub
What is written in the output file is similar to:
25,17,"C:\test_output\text_in1.txt"
25,17,"C:\test_output\text_in2.txt"
25,17,"C:\test_output\text_in3.txt"
What is interesting is that in this case, i only read in 2 input files, and ended up with three identical outputs.
Any help would be greatly appreciated. thanks!
aaron
Ok, here goes.
I have about 3000 comma delimited text files, each with a ID# and a corresponding Z unit. (about 40,000 entries in each).
example:
1,20
2,22
3,30
4,15
...
When I read it into excel, I preform a quick(not always) percentile function and end up with a minimum and maximum height for the total input. I take these two numbers and write them to a text file.
I was able to successfully do this, but when I try and preform a loop based off of a list of cells each with a path name to an individual text file (ex. C:\test\test1.txt, C:\test\test2.txt...), I end up looping through the list more than the total number of text files I read in, and the output file that is written to only lists the first text file in the loop multiple times.
Sub run_Click()
'Clear Contents
Range("A2:B100000").Select
Selection.ClearContents
Dim i As Long, nd As Long, MaxBuilding As Integer, MinBuilding As Integer, q As Long
Dim Temp(100000) As Double, Dens(100000) As Double, File_name As String
q = 1
nxtc = q + 1
Range("Q1").Select
For q = 0 To nxtc
File_name = Sheet1.Cells(1, 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
Range("a1").Select
'create a file
Open "c:\test_output\test_out.txt" For Append As #2
MaxBuilding = Range("h12")
MinBuilding = Range("k12")
Write #2, MaxBuilding, MinBuilding, File_name
Close #2
'clear Contents
Range("A2:B100000").Select
Selection.ClearContents
Next q
End Sub
Sub clear()
Range("A2:B100000").Select
Selection.ClearContents
End Sub
What is written in the output file is similar to:
25,17,"C:\test_output\text_in1.txt"
25,17,"C:\test_output\text_in2.txt"
25,17,"C:\test_output\text_in3.txt"
What is interesting is that in this case, i only read in 2 input files, and ended up with three identical outputs.
Any help would be greatly appreciated. thanks!
aaron