PDA

View Full Version : Help with Importing Part of a text readable file into Excel worksheet via Macro



Jeremy42
04-16-2015, 12:21 PM
I would like to import part of the text from the current workbench directory with the file always being temp.input.out into a new sheet called Output Summary in the open excel file by using a macro.
The text I want to import will be after the line beginning with "PROBABILITY OF FAILURE=" and end before the line starting with "Bar". (there is no "" in the text)
For example the text in the file looks like:
PROBABILITY OF FAILURE= 0.000000000000000E+000

Component, Nucleation , Short Crack , Long Crack ,Total
1 1.000E+10 0.000E+00 1.000E+10 1.000E+10
2 1.000E+10 0.000E+00 1.000E+10 1.000E+10
3 1.000E+10 0.000E+00 1.000E+10 1.000E+10
4 1.000E+10 0.000E+00 1.000E+10 1.000E+10
5 1.000E+10 0.000E+00 1.000E+10 1.000E+10
6 1.000E+10 0.000E+00 1.000E+10 1.000E+10
7 1.000E+10 0.000E+00 1.000E+10 1.000E+10
8 1.000E+10 0.000E+00 1.000E+10 1.000E+10
9 1.000E+10 0.000E+00 1.000E+10 1.000E+10
10 1.000E+10 0.000E+00 1.000E+10 1.000E+10
11 1.000E+10 0.000E+00 1.000E+10 1.000E+10
12 1.000E+10 0.000E+00 1.000E+10 1.000E+10
13 1.000E+10 0.000E+00 1.000E+10 1.000E+10
14 1.000E+10 0.000E+00 1.000E+10 1.000E+10
15 1.000E+10 0.000E+00 1.000E+10 1.000E+10
16 1.000E+10 0.000E+00 1.000E+10 1.000E+10
17 1.000E+10 0.000E+00 1.000E+10 1.000E+10
18 1.000E+10 0.000E+00 1.000E+10 1.000E+10
19 1.000E+10 0.000E+00 1.000E+10 1.000E+10
20 1.000E+10 0.000E+00 1.000E+10 1.000E+10
21 1.000E+10 0.000E+00 1.000E+10 1.000E+10
22 1.000E+10 0.000E+00 1.000E+10 1.000E+10
23 1.000E+10 0.000E+00 1.000E+10 1.000E+10
24 1.000E+10 0.000E+00 1.000E+10 1.000E+10
25 1.000E+10 0.000E+00 1.000E+10 1.000E+10

Bar # I.Type Tot.Life Ph Init.Size Tau K M MCs Site

*All of the numbers of this text will be different each time I run it.*

The text I want imported looks like:

Component, Nucleation , Short Crack , Long Crack ,Total
1 1.000E+10 0.000E+00 1.000E+10 1.000E+10
2 1.000E+10 0.000E+00 1.000E+10 1.000E+10
3 1.000E+10 0.000E+00 1.000E+10 1.000E+10
4 1.000E+10 0.000E+00 1.000E+10 1.000E+10
5 1.000E+10 0.000E+00 1.000E+10 1.000E+10
6 1.000E+10 0.000E+00 1.000E+10 1.000E+10
7 1.000E+10 0.000E+00 1.000E+10 1.000E+10
8 1.000E+10 0.000E+00 1.000E+10 1.000E+10
9 1.000E+10 0.000E+00 1.000E+10 1.000E+10
10 1.000E+10 0.000E+00 1.000E+10 1.000E+10
11 1.000E+10 0.000E+00 1.000E+10 1.000E+10
12 1.000E+10 0.000E+00 1.000E+10 1.000E+10
13 1.000E+10 0.000E+00 1.000E+10 1.000E+10
14 1.000E+10 0.000E+00 1.000E+10 1.000E+10
15 1.000E+10 0.000E+00 1.000E+10 1.000E+10
16 1.000E+10 0.000E+00 1.000E+10 1.000E+10
17 1.000E+10 0.000E+00 1.000E+10 1.000E+10
18 1.000E+10 0.000E+00 1.000E+10 1.000E+10
19 1.000E+10 0.000E+00 1.000E+10 1.000E+10
20 1.000E+10 0.000E+00 1.000E+10 1.000E+10
21 1.000E+10 0.000E+00 1.000E+10 1.000E+10
22 1.000E+10 0.000E+00 1.000E+10 1.000E+10
23 1.000E+10 0.000E+00 1.000E+10 1.000E+10
24 1.000E+10 0.000E+00 1.000E+10 1.000E+10
25 1.000E+10 0.000E+00 1.000E+10 1.000E+10

I would like all of the imported information in its own separate cell.
The problem is that this text will never be on the same line in the different times I run the macro, and there will be different number of lines each time I run it, so I can not use line numbers.
Any help would be greatly appreciated.

jonh
04-17-2015, 02:09 AM
Sub example()
importtxt ActiveSheet, "C:\data.txt"
End Sub

Sub importtxt(sht As Worksheet, txtfile As String)
With sht
.Cells.Delete
With .QueryTables.Add(Connection:= _
"TEXT;" & txtfile, Destination:=Range("$A$2"))
.RefreshStyle = xlInsertDeleteCells
.TextFileStartRow = 4
.TextFileParseType = xlDelimited
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
.Range("A1").Formula = "Component"
.Range("B1").Formula = "Nucleation"
.Range("C1").Formula = "Short Crack"
.Range("D1").Formula = "Long Crack"
.Range("E1").Formula = "Total"
.Rows(.Range("A1").End(xlDown).Offset(2, 0).Row).Delete
End With
End Sub

snb
04-17-2015, 02:53 AM
Rename the file to .csv. Open it in Excel.