Open Text File with Excel Macro

[rnordquest]

I need a macro to open the following text file in probably Fixed Width format to get data into separate aligned columns. My problem is that I don't want to have to individually adjust the fixed width for each of the 400 or so files I want to process.

The example shows only the first 2 of 6 data points and of course not all of the file. The number of data points varies. In real life the data columns do line up. They didn't in the preview post.


Diaphragm Type: Floating Fuel LHV: 18573.2
PT Type: J Plus Fuel Spec Grav: 0.8137
Num Points: 6 Airflow Meas: No

OBSERVED DATA/RECORD NO 1 2
N1 (RPM) 48644 49536
N2 (RPM) 33246 33285
Dyne Torque (Ft-Lbs) 236.4 262.6
_________________


Here is what I have for a macro so far:

Index = 2

For i = 1 To 1000
fileToOpen = Application.GetOpenFilename
If fileToOpen <> False Then
Workbooks.OpenText Filename:=fileToOpen _
, Origin:=437, StartRow:=1, DataType:=xlFixedWidth, OtherChar:="|", _
FieldInfo:=Array(Array(0, 1), Array(26, 1), Array(60, 1), Array(75, 1), Array(80, 1), _
Array(84, 1)), TrailingMinusNumbers:=True
End If

SalesWorkbook = ActiveWorkbook.Name

Range("A8").Copy
Workbooks(ThisWorkbook.Name).Activate
Sheets("Sheet1").Select
Cells(Index, 1).Select
ActiveSheet.Paste

Workbooks(SalesWorkbook).Activate
Range("B7").Copy
Workbooks(ThisWorkbook.Name).Activate
Cells(Index, 2).Select
ActiveSheet.Paste

Workbooks(SalesWorkbook).Activate
Range("A22:H24").Copy
Workbooks(ThisWorkbook.Name).Activate
Cells(Index, 3).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True


As you can see, all I want to do is copy and paste chunks of data from the text files to a single Excel file.

Thanks,

Roger

 

[francesca]

If your column width varies, then fixed width is out. Rather use separated values and have them separated by 2, 3, etc. spaces. You can record a macro with the custom separator to see how to code it.

 

[rnordquest]

Column width varies but it is consistent from file to file.

 

[ab123456]

Have you tried starting the macro recorder and then opening the file and modifying the code. Ive done this previously when my text file has been comma delimited and extracted the following code

Workbooks.OpenText sfilename, DataType:=xlDelimited, Comma:=True, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 4), Array(9, 1), _
Array(10, 4), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _
Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array( _
29, 1), Array(30, 1)), TrailingMinusNumbers:=True

Im sure the macro recorder would give you the basics of the required code for your fixed width data

 

[rnordquest]

I did but I don't think it recorded the changes I made to the array values; it records what it generates the first time around. I went back and reset the array values by hand and it works perfectly.

Many thanks,

Roger