Excel 2003 VBA In directory Open Spreadsheets Extract data
 

Can this be done in MS Excel in VBA.

A macro which extracts two column data from numerous workbooks located
in a directory or sub folders.


How the macro would work.

1. Go to directory C:\\ and examine or open all spreadsheets called
with a name containing the string "CBA".

For example 12th_CBA.xls, W_CBA_thd.xls

These spreadsheets might be read only.

2. For each spreadsheet labelled with a part string 'CBA' in its name,
there are numerous worksheets which in this case i'm only concerned
with workbooks with the string "cbtt" in its name.

3. When it has found the particular worksheet with the string "cbtt"
in its name., in this worksheet there is two columns of records that
need to be extracted and place into a central workbook, to create a
two column table of results from the numerous spreadsheets and
worksheets.

4. With in spreadsheet, worksheet, in the table and the first column
header is called "namesftp trd" and the column records below may
extends to 2000 records which may contain blanks. The second column
header is called "cfop" and the records below may extends to 2000
records which may contain blanks. I can not determine which column or
row the two column headers are but the names are fixed.

5. Once located the data for the two column data, copy the data into a
central excel workbook to build a master central table.

6. go to the next work sheet with the string "cbtt" in its name and
repeat point 5 again.

7. when all worksheets have been examined, it closes that workbook and
proceeds to the next workbook.

7. next, it loops the whole process again until all particular may be
read only workbooks and worksheets have had their two column of data
extracted and centralised into a central worksheet containing a master
table of two columns with all the data.

Is this doable? My macro skills are limited and appreciate in advance
if this problem can be solved automatically than manually.

Many thanks...

Excel 2003 VBA In directory Open Spreadsheets Extract data
 

On Sep 17, 1:17*am, asxastro wrote:
Can this be done in MS Excel in VBA.

A macro which extracts two column data from numerous workbooks located
in a directory or sub folders.

How the macro would work.

1. Go to directory C:\\ and examine or open all spreadsheets called
with a name containing the string "CBA".

For example 12th_CBA.xls, W_CBA_thd.xls

These spreadsheets might be read only.

2. For each spreadsheet labelled with a part string 'CBA' in its name,
there are numerous worksheets which in this case i'm only concerned
with workbooks with the string "cbtt" in its name.

3. When it has found the particular worksheet with the string "cbtt"
in its name., in this worksheet there is two columns of records that
need to be extracted and place into a central workbook, to create a
two column table of results from the numerous spreadsheets and
worksheets.

4. With in spreadsheet, worksheet, in the table and the first column
header *is called "namesftp trd" and the column records below may
extends to 2000 records which may contain blanks. The second column
header is called "cfop" and the records below may extends to 2000
records which may contain blanks. I can not determine which column or
row the two column headers are but the names are fixed.

5. Once located the data for the two column data, copy the data into a
central excel workbook to build a master central table.

6. go to the next work sheet with the string "cbtt" in its name and
repeat point 5 again.

7. when all worksheets have been examined, it closes that workbook and
proceeds to the next workbook.

7. next, it loops the whole process again until all particular may be
read only workbooks and worksheets have had their two column of data
extracted and centralised into a central worksheet containing a master
table of two columns with all the data.

Is this doable? My macro skills are limited and appreciate in advance
if this problem can be solved automatically than manually.

Many thanks...

Yes, it can be done using DIR within a loop.
"If desired, send your files (master and 2 source) to dguillett
@gmail.com I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."