Worked great. Understood the process but now I want to import a second file
after the end of the first file.

Same file twice, fisrt in a Delimited format - to extraxct the header info
(fisrt 20 lines). But then I want to open the file again and extract the data
in a Fixed Width format undernear the header infor above.

The rest of the file will vary in length, from 1000 to 30,000.

Thanks



Dave Peterson wrote:

I'd just import it to a new workbook, then copy what I want and paste to where I
want it. Then close the new workbook.

Option Explicit
Sub testme01()

Dim DestCell As Range
Dim TextWks As Worksheet

Set DestCell = ActiveSheet.Range("a10")

Workbooks.OpenText Filename:="C:\yourfilenamehere.txt", _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1)

Set TextWks = ActiveSheet

TextWks.Range("1:35").Copy _
Destination:=DestCell

TextWks.Parent.Close savechanges:=False

End Sub

Record a macro when you do the importing and replace my Opentext line with
yours.


Dan wrote:

I want to import a file into excel but I want to limit the number of lines
that is imported. For example the file may be 150 lines but I only want the
first 35. I want to automate this task.

Also I want the to import the data at a specific line in excel - not A1,
somewhere around A10.

Thanks

OK. Here is what I have to achieve this task. This was created by recoding a
macro then tweaking the file. It can probably be cleaned up some more.

Now I need to accomplish two additonal tasks:
1. Have the user select the file;

myFileName = Application.GetOpenFilename(filefilter:="Text or ASC Files,
*.txt; *.asc", _
Title:="Select the Data File")

2. A stand alone macro so it can be run by selecting an icon on the desk top.

Thanks

Sub Import_Files()
'
' Import_Files Macro
'

Workbooks.OpenText Filename:= _
"C:\myfile.txt" _
, Origin:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 1), Array(9, 1), Array(12, 1), Array(18, 1), Array(22, 1),
Array(24, 1), Array(26, _
1), Array(34, 1), Array(35, 1), Array(37, 1), Array(40, 1),
Array(43, 1), Array(46, 1), _
Array(49, 1), Array(52, 1), Array(55, 1), Array(58, 1)),
TrailingMinusNumbers:=True
Rows("6:6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book1").Activate
Range("A6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("A:AB").Select
Columns("A:AB").EntireColumn.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A6").Select
Windows("myfile.txt").Activate
Range("D637").Select
ActiveWindow.Close
Workbooks.OpenText Filename:= _
"C:\myfile.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Rows("1:5").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Windows("myfile.txt").Activate
Range("B9").Select
ActiveWindow.Close
End Sub

"Dan" wrote:

Worked great. Understood the process but now I want to import a second file
after the end of the first file.

Same file twice, fisrt in a Delimited format - to extraxct the header info
(fisrt 20 lines). But then I want to open the file again and extract the data
in a Fixed Width format undernear the header infor above.

The rest of the file will vary in length, from 1000 to 30,000.

Thanks



Dave Peterson wrote:

I'd just import it to a new workbook, then copy what I want and paste to where I
want it. Then close the new workbook.

Option Explicit
Sub testme01()

Dim DestCell As Range
Dim TextWks As Worksheet

Set DestCell = ActiveSheet.Range("a10")

Workbooks.OpenText Filename:="C:\yourfilenamehere.txt", _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1)

Set TextWks = ActiveSheet

TextWks.Range("1:35").Copy _
Destination:=DestCell

TextWks.Parent.Close savechanges:=False

End Sub

Record a macro when you do the importing and replace my Opentext line with
yours.


Dan wrote:

I want to import a file into excel but I want to limit the number of lines
that is imported. For example the file may be 150 lines but I only want the
first 35. I want to automate this task.

Also I want the to import the data at a specific line in excel - not A1,
somewhere around A10.

Thanks