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>

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

[Grahm Schmidt]

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

-C.W.
Excel Statement

[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.

Sub OpenTextFileTest()
Dim myAtoms As String
Dim myAtomArray() As String
Dim i As Integer
 
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Dim fs, f
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile("C:\Rodney\Atoms.txt", ForReading) 
 
    [A1].Select
    Do While f.atendofstream <> True
    myAtoms = f.readline
 
    myAtomArray = Split(myAtoms, vbTab) 'Make sure that you are using the correct _
    delimiter value if it's a space use " " if it's a tab us vbTab or _
    if it's somenthing else use it instead.
 
    For i = 0 To UBound(myAtomArray)
        ActiveCell.Offset(0, i) = myAtomArray(i)
    Next
    ActiveCell.Offset(1, 0).Select
    Loop
    f.Close
    set fs = nothing
End Sub
 

hope this helps.

RR