Input # statement Visual Basic Editor (Excel)

marknoten

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>

Nov 13 '06 #1

willakawill

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))

Nov 13 '06 #2

Grahm Schmidt

I am also struggeling with dim coords. Has there been any major changes to the Excel 2010 structure?

-C.W.
Excel Statement

Apr 22 '11 #3

Rodney Roe

You shouldn't even need to dimension your coordinate if you are imputing them right into Excel. Here is an example code,

This code uses Textstream instead of input/output I think it's easier to read and use.

hope this helps.

RR

Apr 22 '11 #4