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

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

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

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

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

Don,

Copy the macro below into a new workbook, then run it. Navigate to and
select the file whose name starts with the same as the same as the
alphanumeric sheet name that all the files have - the ABC123MyCompany.xls
in your example. This assumes that all the files are in the same folder.

I think this meets all your requirements and parameters. If not, post back
with any problems.

HTH,
Bernie
MS Excel MVP

Sub ConsolidateSimilarNamedFiles()
Dim myBook As Workbook
Dim mySht As Worksheet
Dim myCell As Range
Dim myName As String
Dim myShortName As String
Dim myVeryShortName As String
Dim myNumName As String
Dim myPath As String
Dim i As Integer
Dim j As Integer

myName = Application.GetOpenFilename
myPath = Left(myName, InStrRev(myName, "\"))
myShortName = Replace(myName, myPath, "")

i = 1
On Error GoTo NotNumber
j = CInt(Mid(myShortName, i, 1))
myVeryShortName = Left(myShortName, i - 1)
GoTo Found
NotNumber:
i = i + 1
Resume
Found:

On Error GoTo Found2
FindLetter:
j = CInt(Mid(myShortName, i, 1))
i = i + 1
GoTo FindLetter
Found2:
myNumName = Left(myShortName, i - 1)

With Application.FileSearch
.NewSearch
.LookIn = myPath
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
On Error Resume Next
Set mySht = ThisWorkbook.Sheets.Add
mySht.Name = myVeryShortName
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) Like (myPath & myVeryShortName & "*") Then
Set myBook = Workbooks.Open(.FoundFiles(i))
myBook.Worksheets(myNumName).Select
mySht.Range("A65536").End(xlUp)(2).Resize(7, 1).Value = _
myBook.Worksheets(myNumName).Range("B2").Value
Set myCell = myBook.Worksheets(myNumName).Range("A:A"). _
Find("Section 3 - Further information to be completed", _
, xlValues, xlWhole)
If Not myCell Is Nothing Then
myCell.Offset(1, 0).Resize(7, 10).Copy _
mySht.Range("B65536").End(xlUp)(2)
End If
myBook.Close False
End If
Next i
End If
End With
End Sub

"Don" wrote in message
...
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

Thanks Bernie, when I ran it I got subscript out of range, but I will
step thru the code and sort that out. I see the logic in the
programme flow now and should be able to overcome any minor changes
myself.
Its been a great learning curve.

Thanks again for pointing me in the right direction.

Don



On Thu, 3 Jun 2004 14:30:57 -0400, "Bernie Deitrick" <deitbe @
consumer dot org wrote:

Don,

Copy the macro below into a new workbook, then run it. Navigate to and
select the file whose name starts with the same as the same as the
alphanumeric sheet name that all the files have - the ABC123MyCompany.xls
in your example. This assumes that all the files are in the same folder.

I think this meets all your requirements and parameters. If not, post back
with any problems.

HTH,
Bernie
MS Excel MVP

Sub ConsolidateSimilarNamedFiles()
Dim myBook As Workbook
Dim mySht As Worksheet
Dim myCell As Range
Dim myName As String
Dim myShortName As String
Dim myVeryShortName As String
Dim myNumName As String
Dim myPath As String
Dim i As Integer
Dim j As Integer

myName = Application.GetOpenFilename
myPath = Left(myName, InStrRev(myName, "\"))
myShortName = Replace(myName, myPath, "")

i = 1
On Error GoTo NotNumber
j = CInt(Mid(myShortName, i, 1))
myVeryShortName = Left(myShortName, i - 1)
GoTo Found
NotNumber:
i = i + 1
Resume
Found:

On Error GoTo Found2
FindLetter:
j = CInt(Mid(myShortName, i, 1))
i = i + 1
GoTo FindLetter
Found2:
myNumName = Left(myShortName, i - 1)

With Application.FileSearch
.NewSearch
.LookIn = myPath
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
On Error Resume Next
Set mySht = ThisWorkbook.Sheets.Add
mySht.Name = myVeryShortName
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) Like (myPath & myVeryShortName & "*") Then
Set myBook = Workbooks.Open(.FoundFiles(i))
myBook.Worksheets(myNumName).Select
mySht.Range("A65536").End(xlUp)(2).Resize(7, 1).Value = _
myBook.Worksheets(myNumName).Range("B2").Value
Set myCell = myBook.Worksheets(myNumName).Range("A:A"). _
Find("Section 3 - Further information to be completed", _
, xlValues, xlWhole)
If Not myCell Is Nothing Then
myCell.Offset(1, 0).Resize(7, 10).Copy _
mySht.Range("B65536").End(xlUp)(2)
End If
myBook.Close False
End If
Next i
End If
End With
End Sub

"Don" wrote in message
.. .
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