extract specific data from a file into worksheet

**darkhorse4321** · 11-29-2010, 10:22 AM

For this thread would like to know once I opened a file instead of opening the entire contents from the file, I would like my macro to

1. Look for the 3rd line in the file and get the information inside “ “ into column A. look at the image below:
http://www.iimmgg.com/image/40eb28d0...08d5482e162f3f

2. Look for the line that starts with “silicon node” or “node” and get the word beside the word “Silicon node” or “node”, and put them in column B.

for e.g. if the line is something like this
silicon node "#%NC__U2.G5" test "#%u2.G5"
get the word #%NC__U2.G5 into column B, the rest of the details in the line can be discarded
take a look at the image below:
http://www.iimmgg.com/image/33211e2a...fd899c4f7a939a

The final outcome should look something like this
final.JPG

I have attached a zip file. Inside the zip file below,
I have attached a sample worksheet, it contains
Sheet 1, contains a button with my initial codes.
Sheet2, shows how the output should look like once extracted
I have also attached the file that needs to be extracted; this file can be opened with notepad/WordPad.
BScan.zip

**antoka05** · 11-30-2010, 05:12 AM

See attached file, where I used this code:

Please Login or Register  to view this content.

Regards,
Antonio

**darkhorse4321** · 11-30-2010, 09:16 AM

Thanks a lot Antonio for your time and help.

**darkhorse4321** · 12-08-2010, 02:11 AM

Hi Antonio,
Is it possible for you to do slight editing to the program. I will explain what I need to do and let you see whether you could provide any help.

The image below shows how the worksheet looks like once I click the button than contains your codes to open a file:
http://www.iimmgg.com/image/218dc0b6...b8231f6aafd99b

If I click the button again, then all the data in the sheet will be cleared. What I want to do instead of clearing data in the sheet, when I re click the button to open a file the data should continue to extract below the previous file.
Take a look at the image below.
http://www.iimmgg.com/image/784da99e...07b442eb3e843e

**JBeaucaire** · 12-08-2010, 02:35 PM

Change this line of code:

Please Login or Register  to view this content.

...to:

Please Login or Register  to view this content.

Then change this line:

Please Login or Register  to view this content.

...to:

Please Login or Register  to view this content.

**darkhorse4321** · 12-09-2010, 02:07 AM

Thanks JBeaucaire for your help. It works.

I made some changes to your codes. I wanted to extract some data into column C using the same file.

What I wanted to do is to look for line that start with either “! Silicon nodes” or “! Nodes” and extract the next word into column C.
I did manage to do this.

I inserted the following codes:

Please Login or Register  to view this content.

I also replaced the following codes

destRow = sh2.Range("B" & Rows.Count).End(xlUp).Row + 2

TO

destRow = sh2.Range("B" & Rows.Count).End(xlUp).Row + 2 Or sh2.Range("C" & Rows.Count).End(xlUp).Row

So once I extract the file my workbook look something like the image below.
http://www.iimmgg.com/image/fe049a38...43033fc4ca416f

But I want to remove the empty cells in column B and C. Take a look at the image below.
http://www.iimmgg.com/image/23f34c21...71ba30b31dd57f

Do you know what changes I must make?

I have attached a sample workbook with the edited codes and the file i used to extract
BScan_extract.zip