Getting text file into excell

[dvt]

I have text files which are loaded to excell.
The text files are always of the same format.

I want to load the file, but always I need to mark the positions of
the current loaded file.

How can I load the file always in a fixed format (can I do a
templette for that ?)

I just did something very similar last week. I recorded a macro and then
modified the code slightly to meet my needs. To record a macro, Tools |
Macro | Record new macro.... Then you perform your typical text loading
function and stop the macro recorder. To edit the macro, Tools | Macro |
Macros | (select the macro) Run.

My macro looks like the following. I added the second line to give a prompt
for a new filename each time I execute the macro. You might like to use
that (or you might not .

Sub open_ascii_file()
DirName = "C:\files\pathname"
Filename = InputBox("Enter a filename")
Workbooks.OpenText Filename:=DirName & Filename, Origin:=xlWindows,
StartRow:= _
9, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(12,
1), Array(25 _
, 1), Array(38, 1), Array(51, 1), Array(64, 1), Array(77, 1),
Array(90, 1), Array(103, 1), _
Array(116, 1), Array(129, 1), Array(142, 1), Array(155, 1),
Array(168, 1), Array(181, 1), _
Array(194, 1), Array(207, 1), Array(220, 1), Array(233, 1),
Array(246, 1), Array(259, 1), _
Array(272, 1), Array(285, 1), Array(298, 1), Array(311, 1),
Array(324, 1), Array(337, 1), _
Array(350, 1))
End Sub

Dave
dvt at psu dot edu

 

[Mr. J.]

Hello.

I have text files which are loaded to excell.
The text files are always of the same format.

I want to load the file, but always I need to mark the positions of the
current loaded file.

How can I load the file always in a fixed format (can I do a templette for
that ?)

Thanks

 

[Mr. J.]

Thanks
How can I load a macro after I saved it in *.bas (vb file) into excell ?

Sub open_ascii_file() [snip]
End Sub

Sorry about the word wrap. Let me try a second time....

Sub open_ascii_file()
DirName = "C:\files\pathname"
Filename = InputBox("Enter a filename")
Workbooks.OpenText Filename:=DirName & Filename, _
Origin:=xlWindows, StartRow:= 9,_
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),_
Array(12, 1), Array(25 , 1), Array(38, 1), Array(51, 1), _
Array(64, 1), Array(77, 1), Array(90, 1), Array(103, 1), _
Array(116, 1), Array(129, 1), Array(142, 1), Array(155, 1),_
Array(168, 1), Array(181, 1), Array(194, 1), Array(207, 1),_
Array(220, 1), Array(233, 1), Array(246, 1), Array(259, 1), _
Array(272, 1), Array(285, 1), Array(298, 1), Array(311, 1),_
Array(324, 1), Array(337, 1), Array(350, 1))
End Sub

 

[dvt]

How can I load a macro after I saved it in *.bas (vb file) into
excell ?

I'm not sure how to do it from a .bas file. I only know how to do this from
the VB editor in Excel.

In Excel, hit alt-F11. Click Insert | Module. Copy the code from your
*.bas file, paste it into the window you just created. Go back to the
spreadsheet, click Tools | Macro | Macros. Select the appropriate macro and
click Run.

Does this help?

Dave
dvt at psu dot edu

 

[Mr. J.]

Thanks

With Excel open hit ALT + F11

Select your workbook/project and right-click>Import File.

You will be presented with a dialog box(file types .bas, .frm, .cls). Browse
to your *.bas file and "Open". It will be imported as a new Module.

Gord Dibben Excel MVP - XL97 SR2 & XL2002