Help with Importing Part of a text readable file into Excel worksheet via Macro

[Jeremy42]

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.

[mehmetcik]

This is basic.

Read all your data into excel

a. Find the bottom of your data

b. Find "Bar" in your data

Delete between a and b

c. Find "PROBABILITY OF FAILURE" in the data

Delete between the first row and c.

Ok that is the process now i will write the Macro for you.

Please Login or Register  to view this content.

[Jeremy42]

Thank you for the response, unfortunately there are a couple problems I am having with the above solution:
1. The line Workbooks.Open Filename:=MyPath & "\" & "temp.input.out.csv"
should read Workbooks.Open Filename:=MyPath & "\" & "temp.input.out"
the complete file name and extension is "temp.input.out"
2. The macro is creating a new workbook with the name temp.input instead of importing the text I want in the Output Summary sheet of the excel file I already have open.
3. I am getting a Run Time error 91: Object variable or With block variable not set and when I debug it, it shows this line as causing the error:
BR = Columns("A:A").Find(What:="Bar #", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
It also throws up the same error with the line:
BR = Columns("A:A").Find(What:="PROBABILITY OF FAILURE=", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
4. The text that is in the same row is all in the first cell instead of being in separate cells - for all the different rows. I would like them to be delineated.

Any help you could give would be greatly appreciated.
Thanks

[mehmetcik]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Jeremy42]

Here is a sanitized version of the excel file and the file I am trying to read the text from.
Please note that I had to change the file name from "temp.input.out" to "temp.txt" because the site would not let me upload the other file. I made the appropriate changes to the macro.
I would like what appears on the "What I want it to look like" sheet (this is a mock up that I had to manually create) to be on the Output Summary sheet by using the commandbutton1 on sheet 1 and the macro encoded there.

[mehmetcik]

You had me pulling my hair out,

Duh..................

Right.

You created an activex command button and assigned my macro to it.

So......................................

activex macros are sheet specific. You know that because you right click on the sheet name and select view code to edit them

Sheet Specific macros can only operate in the worksheet that they sit in.

So hence all the debug messages

If you want to use your existing command button, then you have to move most of the code to a normal macro module and call it as a subroutine

[Jeremy42]

Thanks for the continued help!
It is always the simple things....
However, the macro is still not doing what I need it to do. I do not want it to open any new excel file at all; I want the macro to populate the Output Summary sheet in the test2.xls file that is already open NOT open a new excel file called temp.xls and enter the data there. Also the data that is getting transferred is still not formatted right - meaning that all the data for row 2 is still all in cell B2 and it is the same for the other rows as well. I need it to be separated into the different cells as shown on the "What I want it to look like" sheet of the test2.xls file.
Thanks again

[jindon]

try the attached.