Input from Text file
 

Hi guys, since the answer to my first post was so helpful: good and to
the point i wanted to find out how to do some of the more complicated
stuff, so far ive been doing it manually (takes a long time)

Problem: i often get files with data in them such as
NO. TYPE FROM TO Arc MEASUREMENT STD DEV V (=-b)

1 Direction 1 3 0 0 0 0.1 2.10 0.0 secs
2 Direction 1 4 0 31 11 48.0 2.26 3.2 secs
3 Direction 1 5 0 40 47 30.0 2.50 0.4 secs
4 Direction 1 6 0 79 30 0.9 2.82 3.7 secs
5 Direction 1 7 0 187 43 19.7 2.06 11.5 secs
6 Direction 2 6 0 359 59 59.9 2.26 0.0 secs

I would like to enter the above data into Excel automatically. Each
column of above data has to go to a separate column in excel and same
goes for the rows.

If anyone can help me on that or suggest possible solutions your help
is much appreciated! Thanks LOTS!! :D


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Input from Text file
 

Hi

See Chip's code at
http://www.cpearson.com/excel/imptext.htm

--
HTH. Best wishes Harald
Followup to newsgroup only please

"BOMB3RMAN" skrev i melding
...

Hi guys, since the answer to my first post was so helpful: good and to
the point i wanted to find out how to do some of the more complicated
stuff, so far ive been doing it manually (takes a long time)

Problem: i often get files with data in them such as
NO. TYPE FROM TO Arc MEASUREMENT STD DEV V (=-b)

1 Direction 1 3 0 0 0 0.1 2.10 0.0 secs
2 Direction 1 4 0 31 11 48.0 2.26 3.2 secs
3 Direction 1 5 0 40 47 30.0 2.50 0.4 secs
4 Direction 1 6 0 79 30 0.9 2.82 3.7 secs
5 Direction 1 7 0 187 43 19.7 2.06 11.5 secs
6 Direction 2 6 0 359 59 59.9 2.26 0.0 secs

I would like to enter the above data into Excel automatically. Each
column of above data has to go to a separate column in excel and same
goes for the rows.

If anyone can help me on that or suggest possible solutions your help
is much appreciated! Thanks LOTS!! :D


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Input from Text file
 

If these flat files are always the same layout, you may want to record a macro
when you do it by hand the next time.

Start a new workbook.
Tools|macro|Record new macro
Do all your importing and reformatting.
Include all the things you like (worksheet headers, print headers/footers/freeze
panes/Data|Filter|Autofilter)

Stop recording and save that workbook with the recorded code in it.
Put a giant button from the forms toolbar on the first worksheet in that
"importer" workbook. Assign your macro to the button.

You'll probably have to adjust the code a little to make it more generic. When
you recorded your macro, you got something that looked like:

Option Explicit
Sub Macro1()

Workbooks.OpenText Filename:="C:\myfile.txt", Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(15, 1), _
Array(41, 1))

End Sub

Well, instead of having your filename in the code, you can give the user a
chance to pick it themselves (take a look at getopenfilename in VBA's help):

Sub macro1A()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

And you may need to adjust some ranges (depending on what you did when you
recorded the macro). Post back with a snippet of your code (not the workbook)
and explain your problem.

But now whenever you need to import a text file with that layout, you can just
open your "importer" workbook and click the giant button.



BOMB3RMAN wrote:

Hi guys, since the answer to my first post was so helpful: good and to
the point i wanted to find out how to do some of the more complicated
stuff, so far ive been doing it manually (takes a long time)

Problem: i often get files with data in them such as
NO. TYPE FROM TO Arc MEASUREMENT STD DEV V (=-b)

1 Direction 1 3 0 0 0 0.1 2.10 0.0 secs
2 Direction 1 4 0 31 11 48.0 2.26 3.2 secs
3 Direction 1 5 0 40 47 30.0 2.50 0.4 secs
4 Direction 1 6 0 79 30 0.9 2.82 3.7 secs
5 Direction 1 7 0 187 43 19.7 2.06 11.5 secs
6 Direction 2 6 0 359 59 59.9 2.26 0.0 secs

I would like to enter the above data into Excel automatically. Each
column of above data has to go to a separate column in excel and same
goes for the rows.

If anyone can help me on that or suggest possible solutions your help
is much appreciated! Thanks LOTS!! :D

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson