Open File

[chrisnsmith]

Is there a way to tell this macro to open in cell A6 and to preserve the
formatting of the worksheet?

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/13/2009
'

'
Workbooks.OpenText Filename:= _
"\\admis.adm\kcdfs\kcusers\A829917\My Documents\Seg Positions.txt",
Origin:= _
xlWindows, StartRow:=6, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 1), _
Array(10, 1), Array(16, 1), Array(41, 1)), TrailingMinusNumbers:=True
End Sub

 

[Dave Peterson]

This is opening a text file. There is no formatting associated with text
files--they're plain text. But you can specify how each field should be treated
(general, text, date, skipped) in that fieldinfo parm.

If you wanted, you could insert 5 empty lines at the top of the text file or
have your macro insert 5 rows at the top of the worksheet.

You could add a line to your code after the file is opened:

activesheet.rows(1).resize(5).insert

And you could apply any formatting you want (number/font/fill) after the data is
imported. Do it manually and record a macro. Then add this recorded code to
your existing macro and you should be close to set.

 

[Dave P]

Hi,
You should be able to accomplish this without a macro by using the text
import feature.
With this feature you can specify a starting cell to place the data and also
preserve cell formatting.
The properties that you set can be saved with the file and used as a sort of
template for opening (importing) the text file.
You may want to start here;
http://www.j-walk.com/ss/excel/usertips/tip030.htm
If you're using Excel 2007 try here:
http://office.microsoft.com/en-us/excel/HP100997251033.aspx
Best wishes.

Dave