Assitsance with writing macro to import text report into Excel

I have a text report that I would like to import into Excel. The problem,
however, is that the report consists of a header and then detail rows. Below
is a sample of 1 section from of the text report (each report will contain
many sections similar to this):

BATCH REPORT 2002/12/31 08:15:45 PM
Formula: 1234A (PRODUCT ABCDEFGHIJK) Client: J.SOAP MixInstr: 456
B.Run ID: 890 Dest.Bin: 01 P.Code: M Density: 600 Mol: 1.0%
No.Batches: 6/6 B.Size: 2.00 Tons Operator: ORIE-998
Material Bin SP ACT
5800 INGREDIENT 1a 3.00 3.00
1050 INGREDIENT 7a 2.40 2.40
975 INGREDIENT 6a 3.10 3.10
923 INGREDIENT 4a 4.00 4.00
929 INGREDIENT 41 13.00 13.00
1 3 INGREDIENT 42 1295.44 1292.00
203 6 INGREDIENT 43 215.00 217.00
910 7 INGREDIENT 6b 189.00 187.00
211 8 INGREDIENT 8j 252.00 251.00
823 ML INGREDIENT 6h 20.00 20.00
1006 L2 INGREDIENT 3f 2.26 2.30
5900 L3 INGREDIENT 2x 0.80 0.83
------- -------
TOTAL BATCH WEIGHT: 2000.00 1995.62

I would like to import the above information into an Excel spreadsheet,
using a macro that will parse the data into columns in the following format:
The header information for each section would be imported into columns A - M
(and would be the same for each of the detail rows). The data in the detail
rows would then be imported into columns N - S.

Is this possible using Micrsoft Office and Windows XP? If so, please can
someone provide me with what functionality I should be using.
The problem with the text is that the header row/(s) need to be parsed based
on keywords (e.g. Formula, P.Code, etc) and the detail
needs to be parsed on a fixed-width basis.

Any assistance/pointers would be greatly appreciated.

Many thanks in advance for all your help

Record a macro to import the file regardless, and re-organise the header in
the recording session.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mark" <[email protected]> wrote in message
news:[email protected]...
> I have a text report that I would like to import into Excel. The problem,
> however, is that the report consists of a header and then detail rows.
Below
> is a sample of 1 section from of the text report (each report will contain
> many sections similar to this):
>
> BATCH REPORT 2002/12/31 08:15:45 PM
> Formula: 1234A (PRODUCT ABCDEFGHIJK) Client: J.SOAP MixInstr: 456
> B.Run ID: 890 Dest.Bin: 01 P.Code: M Density: 600 Mol: 1.0%
> No.Batches: 6/6 B.Size: 2.00 Tons Operator: ORIE-998
> Material Bin SP ACT
> 5800 INGREDIENT 1a 3.00 3.00
> 1050 INGREDIENT 7a 2.40 2.40
> 975 INGREDIENT 6a 3.10 3.10
> 923 INGREDIENT 4a 4.00 4.00
> 929 INGREDIENT 41 13.00 13.00
> 1 3 INGREDIENT 42 1295.44 1292.00
> 203 6 INGREDIENT 43 215.00 217.00
> 910 7 INGREDIENT 6b 189.00 187.00
> 211 8 INGREDIENT 8j 252.00 251.00
> 823 ML INGREDIENT 6h 20.00 20.00
> 1006 L2 INGREDIENT 3f 2.26 2.30
> 5900 L3 INGREDIENT 2x 0.80 0.83
> ------- -------
> TOTAL BATCH WEIGHT: 2000.00 1995.62
>
> I would like to import the above information into an Excel spreadsheet,
> using a macro that will parse the data into columns in the following
format:
> The header information for each section would be imported into columns A -
M
> (and would be the same for each of the detail rows). The data in the
detail
> rows would then be imported into columns N - S.
>
> Is this possible using Micrsoft Office and Windows XP? If so, please can
> someone provide me with what functionality I should be using.
> The problem with the text is that the header row/(s) need to be parsed
based
> on keywords (e.g. Formula, P.Code, etc) and the detail
> needs to be parsed on a fixed-width basis.
>
> Any assistance/pointers would be greatly appreciated.
>
> Many thanks in advance for all your help

There's no easy way to do this. You will have to write a fairly
detailed VB(A) program to parse your headers (and possibly ignore the
footers). Maybe, someone will oblige but it would appear you need to
hire someone for the job.

One thing to consider is how you want the data organized. I would
strongly recommend that you not use a single table as per your current
plans. Instead, put the header information in one table making sure
there is something unique that identifies the batch, adding a new column
if necessary. Put the details in another table using that same unique
batch identifier. For some more see
RDBMS in Excel
http://www.tushar-mehta.com/excel/ne...cel/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article <[email protected]>,
[email protected] says...
> I have a text report that I would like to import into Excel. The problem,
> however, is that the report consists of a header and then detail rows. Below
> is a sample of 1 section from of the text report (each report will contain
> many sections similar to this):
>
> BATCH REPORT 2002/12/31 08:15:45 PM
> Formula: 1234A (PRODUCT ABCDEFGHIJK) Client: J.SOAP MixInstr: 456
> B.Run ID: 890 Dest.Bin: 01 P.Code: M Density: 600 Mol: 1.0%
> No.Batches: 6/6 B.Size: 2.00 Tons Operator: ORIE-998
> Material Bin SP ACT
> 5800 INGREDIENT 1a 3.00 3.00
> 1050 INGREDIENT 7a 2.40 2.40
> 975 INGREDIENT 6a 3.10 3.10
> 923 INGREDIENT 4a 4.00 4.00
> 929 INGREDIENT 41 13.00 13.00
> 1 3 INGREDIENT 42 1295.44 1292.00
> 203 6 INGREDIENT 43 215.00 217.00
> 910 7 INGREDIENT 6b 189.00 187.00
> 211 8 INGREDIENT 8j 252.00 251.00
> 823 ML INGREDIENT 6h 20.00 20.00
> 1006 L2 INGREDIENT 3f 2.26 2.30
> 5900 L3 INGREDIENT 2x 0.80 0.83
> ------- -------
> TOTAL BATCH WEIGHT: 2000.00 1995.62
>
> I would like to import the above information into an Excel spreadsheet,
> using a macro that will parse the data into columns in the following format:
> The header information for each section would be imported into columns A - M
> (and would be the same for each of the detail rows). The data in the detail
> rows would then be imported into columns N - S.
>
> Is this possible using Micrsoft Office and Windows XP? If so, please can
> someone provide me with what functionality I should be using.
> The problem with the text is that the header row/(s) need to be parsed based
> on keywords (e.g. Formula, P.Code, etc) and the detail
> needs to be parsed on a fixed-width basis.
>
> Any assistance/pointers would be greatly appreciated.
>
> Many thanks in advance for all your help
>