Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I open Excel 2000 spreadsheets in Excel 2003? | Excel Worksheet Functions | |||
Extract Data From Active Directory | Excel Programming | |||
Excel - Extract data from one worksheet to many spreadsheets ... | Excel Programming | |||
excel macro to extract data from one worksheet to many spreadsheets | Excel Programming | |||
excel macro to extract data from one worksheet to many spreadsheets | Excel Programming |