Bernie,

The manipulation will be done in Excel before passing to a Database
so its a non issue. I take your point though.

Don,

Actually, since you will be doing further manipulation of the data in
Access, it is important that your database be set up as a database. Your
structure

Enq123Fred
question 1 Item1 Item2 Item3 etc
question2 Item1 Item2 Item3 etc
to question7...................................

would probably be better entered as

Enq123Fred question 1 Item1
Enq123Fred question 1 Item2
Enq123Fred question 1 Item3
Enq123Fred question 2 Item1
Enq123Fred question 2 Item2
Enq123Fred question 2 Item3

etc., etc.

Have you tried your data manipulation with your current proposed data
structure? Either can be produced with the macro - it's your choice, since
the customer is always right ;-)

HTH,
Bernie
MS Excel MVP


"Don" wrote in message
.. .
Bernie,

Thanks for the prompt reply. The answer is 7 items of data plus the
identifier (x to X + 6).
Its not too important if the identifier is in column A as further data
manipulation, probably within Access will take place.

I had envisaged something like that below.

A B C D ...j
Enq123Fred
question 1 Item1 Item2 Item3 etc
question2 Item1 Item2 Item3 etc
to question7...................................

Don,

It should be fairly easy to write this macro, but I have one question
before
attempting it: Do you want the identifier from cell B2 in the first cell
of
the seven rows (column A), and the data copied to columns B to K? Also,
when
you say rows X to X+7, that is actually eight rows - do you want seven
rows
(X to X+6) or eight rows (Z to X+7)?

HTH,
Bernie
MS Excel MVP


"Don" wrote in message
.. .
Hi All,

I would appreciate some help or advice with this problem please.
I have about 500 workbooks all named e.g. ABC123MyCompany, or
DEF234MyOtherCompany etc. The numerical part of the name may be 3 to 5
digits.

The sheets (only one per book) named with the AlphaNumeric part of the
workbook e.g ABC123 I need to append to a new table (not yet defined)
Cell B2 as an identifier and then the cells that contain certain data
which is in a consistent format in rows x to x+7 and A to J inclusive.

First problem is that Row X is variable in every sheet but the row
above always contains the string "Section 3 - Further Information to
be completed"

In my laymans term the logic is as follows-

Start at Cell A1- Move to A2 - Does this cell contain the
String "Section 3 - Further Information to be completed" -
If yes then copy the next 7 rows A to J to a
new worksheet
If No then move to cell A3 and repeat the process moving down the
sheet until the String is found

Then open the next workbook and repeat but appending the data to the
newly created sheet

The objective would be to create a single sheet containing the 7 rows
with the identifier of the 500 workbooks.
If it is easier to use the filename as an identifier instead of CellB2
then that would be acceptable. There is no problem with deleting the
workbook after importing as I would be using a copy of the original
data.

Further processing on the sheet would tidy any anomolies

Thanks in advance

Don