I'm quite a newbie to Visual Basic and have a problem with the Input # statement in the Visual Basic Editor from Excel. I would like to read in a tab delimeted text file selected by a user with records like this:
<snip>
O 21.266 17.733 28.155
N 20.649 17.703 30.320
C 20.883 17.336 31.723
</snip>
I'm using the code below to read each line from the file and store them in the variables organicAtom, xCoord, yCoord and zCoord. The problem is that by using a String as type for the variable organicAtom, it's value for the first line becomes "N 23.179 15.744 28.550". If I replace the type of organicAtom by a Long then each value will be 0 but my other variables xCoord, yCoord and zCoord are read in well.
I don't see any extra arguments in the Input # statement that can prevent this behavior.
<code>
Dim atomsTextFile As Variant
atomsTextFile = Application.GetOpenFilename("Atom text files (*.txt), *.txt", 1, "Select atoms with reduced coordinates input file")
Dim lineNumber As Integer
Dim organicAtom As String
Dim xCoord, yCoord, zCoord As Double
If atomsTextFile <> False Then
Open atomsTextFile For Input Access Read As #1
lineNumber = 1
Do While Not EOF(1)
Input #1, organicAtom, xCoord, yCoord, zCoord
atomsSheet.Range("A" & lineNumber).Value = organicAtom
atomsSheet.Range("B" & lineNumber).Value = xCoord
atomsSheet.Range("C" & lineNumber).Value = yCoord
atomsSheet.Range("D" & lineNumber).Value = zCoord
lineNumber = lineNumber + 1
Loop
Close #1
End If
</code>
Hi. first of all, when you are declaring variables as in;
Dim xCoord, yCoord, zCoord As Double
only zCoord will be a Double type. The rest will be variants.
Read the text into organicAtom
Input #1, organicAtom
Then use Split()
Dim arData As Variant
arData = Split(organicAtom, " ")
organicAtom = arData(0)
xCoord = CDbl(arData(1))
yCoord = CDbl(arData(2))
zCoord = CDbl(arData(3))