By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,642 Members | 2,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,642 IT Pros & Developers. It's quick & easy.

Input # statement Visual Basic Editor (Excel)

P: 4
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
Share this Question
Share on Google+
3 Replies


100+
P: 1,646
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

P: 2
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

P: 61
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.

Expand|Select|Wrap|Line Numbers
  1. Sub OpenTextFileTest()
  2. Dim myAtoms As String
  3. Dim myAtomArray() As String
  4. Dim i As Integer
  5.  
  6.     Const ForReading = 1, ForWriting = 2, ForAppending = 8
  7.     Dim fs, f
  8.     Set fs = CreateObject("Scripting.FileSystemObject")
  9.     Set f = fs.OpenTextFile("C:\Rodney\Atoms.txt", ForReading) 
  10.  
  11.     [A1].Select
  12.     Do While f.atendofstream <> True
  13.     myAtoms = f.readline
  14.  
  15.     myAtomArray = Split(myAtoms, vbTab) 'Make sure that you are using the correct _
  16.     delimiter value if it's a space use " " if it's a tab us vbTab or _
  17.     if it's somenthing else use it instead.
  18.  
  19.     For i = 0 To UBound(myAtomArray)
  20.         ActiveCell.Offset(0, i) = myAtomArray(i)
  21.     Next
  22.     ActiveCell.Offset(1, 0).Select
  23.     Loop
  24.     f.Close
  25.     set fs = nothing
  26. End Sub
  27.  
hope this helps.

RR
Apr 22 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.