Extract specific column data from specific sheet from multiple files in a folder

**piggyfox** · 05-30-2013, 07:10 AM

Greetings,
Tried to search the forums and yet to find the solution for what I want..meanwhile posting my requirement here to get a faster response or direction toward the solution.

I have multiple workbooks ( same name with different version numbers) in a folder from which I need to extract data to a master file. The requirement is

1. whenever a new version (workbook) is added
2. The summary-workbook to have the 'command button' and on click the changed data has to be extracted into summary-sheet in the summary-workbook.
3. The data from the new-version to be compared with the summary-sheet and only the changed data to be extracted
4. The request to have extract only changed data because the workbook version is maintained in the summary-sheet to identify the corresponding version from which the data is extracted.
5. There is ID to identify the records.

About workbooks.

1. The workbooks will have same name with version number
2. All the workbooks are stored in one folder
3. Each workbooks have multiple sheets including the Target-sheet from which the data to be extracted
4. The names of the sheets are maintained constant in all workbooks
5. These workbooks are password protected but can be opened or read the data in 'Read only' mode.
6. These workbooks not to be opened while extracting the data into the summary-sheet

Attaching sample workbooks (with names WorkbookV1.0,WorkbookV1.1 AND WorkbookV1.3) from where the data has to be extracted and Summary-workbook which gives the expected result.

Please help with the macro/script or direct me to the thread if this was solved in another f

**tehneXus** · 05-30-2013, 08:13 AM

Hi,

I've started to write this and have some questions:
1. What happens if in a new version a new ID appears which is not in the SummarySheet? Should it be added?
2. What happens if an ID is missing in a new version, should it be removed from the summary?
3. What happens if in a new version only one value has changed, not the whole record, overwrite the version number anyway?
4. The version numbers in your summary sheet do not correspond to the file names. Files are 1.0, 1.2, ... while sheet is 1, 2, 3, ...
5. Are there any other columns that should be checked apart from A:D ?
6. Are there any other files in that folder that should be skipped?
7. Should all versions be checked again and only copy updated values if the version number is higher, or (faster) skip "old" versions, means we will check the highest version in summary and skip everything that is below according to the file name.

Cheers

**piggyfox** · 05-30-2013, 08:26 AM

@tehneXus
please see my responses

1. What happens if in a new version a new ID appears which is not in the SummarySheet? Should it be added?

NO NEW IDs WILL BE ADDED. THE IDS ARE FIXED

2. What happens if an ID is missing in a new version, should it be removed from the summary?
IDS WILL NOT BE REMOVED

3. What happens if in a new version only one value has changed, not the whole record, overwrite the version number anyway?

YES IF ANY VALUE OF IN THE EXTRACTED COLUMN CHANGED THEN THE VERSION NUMBER HAS TO CHANGE

4. The version numbers in your summary sheet do not correspond to the file names. Files are 1.0, 1.2, ... while sheet is 1, 2, 3, ..
APOLOGIZE FOR MY MISTAKE. INSTEAD OF VERSION NUMBER ITS SHOULD BE THE WORKBOOK NAME (WHICH INCLUDES THE VERSION NUMBER)

5. Are there any other columns that should be checked apart from A:D ?
NOT ALL COLUMNS ONLY SPECIFIC COLUMNS TO BE CHECKED

6. Are there any other files in that folder that should be skipped?
THE FOLDER WILL HAVE ONLY THESE WORKBOOKS. WHENEVER A NEW VERSION IS ADDED IT WILL BE STORED IN THIS FOLDER. NO OTHER DOCUMENTS/FILES WILL BE STORED IN THIS FOLDER

7. Should all versions be checked again and only copy updated values if the version number is higher, or (faster) skip "old" versions, means we will check the highest version in summary and skip everything that is below according to the file name.

NO NEED TO CHECK ALL VERSIONS. WHENEVER A NEW VERSION IS ADDED AND THE BUTTON IS CLICKED IN THE SUMMARY-WORKBOOK THE VALUES TO BE COMPARED FROM NEW-VERSION ADDED TO THE SUMMARY-SHEET AND THEN CHANGE THE DATA IN SUMMARY-SHEET AS REQUIRED.

Hope I made little clear now...Thank you.

**piggyfox** · 05-30-2013, 08:28 AM

@tehneXus
please see my responses

1. What happens if in a new version a new ID appears which is not in the SummarySheet? Should it be added?

NO NEW IDs WILL BE ADDED. THE IDS ARE FIXED

2. What happens if an ID is missing in a new version, should it be removed from the summary?
IDS WILL NOT BE REMOVED

3. What happens if in a new version only one value has changed, not the whole record, overwrite the version number anyway?

YES IF ANY VALUE OF IN THE EXTRACTED COLUMN CHANGED THEN THE VERSION NUMBER HAS TO CHANGE

4. The version numbers in your summary sheet do not correspond to the file names. Files are 1.0, 1.2, ... while sheet is 1, 2, 3, ..
APOLOGIZE FOR MY MISTAKE. INSTEAD OF VERSION NUMBER ITS SHOULD BE THE WORKBOOK NAME (WHICH INCLUDES THE VERSION NUMBER)

5. Are there any other columns that should be checked apart from A:D ?
NOT ALL COLUMNS ONLY SPECIFIC COLUMNS TO BE CHECKED

6. Are there any other files in that folder that should be skipped?
THE FOLDER WILL HAVE ONLY THESE WORKBOOKS. WHENEVER A NEW VERSION IS ADDED IT WILL BE STORED IN THIS FOLDER. NO OTHER DOCUMENTS/FILES WILL BE STORED IN THIS FOLDER

7. Should all versions be checked again and only copy updated values if the version number is higher, or (faster) skip "old" versions, means we will check the highest version in summary and skip everything that is below according to the file name.

NO NEED TO CHECK ALL VERSIONS. WHENEVER A NEW VERSION IS ADDED AND THE BUTTON IS CLICKED IN THE SUMMARY-WORKBOOK THE VALUES TO BE COMPARED FROM NEW-VERSION ADDED TO THE SUMMARY-SHEET AND THEN CHANGE THE DATA IN SUMMARY-SHEET AS REQUIRED.

Hope I made little clear now...Thank you.

**tehneXus** · 05-30-2013, 08:41 AM

... please repair your caps key...

Originally Posted by piggyfox

...
APOLOGIZE FOR MY MISTAKE. INSTEAD OF VERSION NUMBER ITS SHOULD BE THE WORKBOOK NAME (WHICH INCLUDES THE VERSION NUMBER)
...
NO NEED TO CHECK ALL VERSIONS. WHENEVER A NEW VERSION IS ADDED AND THE BUTTON IS CLICKED IN THE SUMMARY-WORKBOOK THE VALUES TO BE COMPARED FROM NEW-VERSION ADDED TO THE SUMMARY-SHEET AND THEN CHANGE THE DATA IN SUMMARY-SHEET AS REQUIRED.

So I'll add the workbook name in column E of the summary.
Then there is the following problem: You say there is the version number in the workbook name, where is it exactly? To determine the most recent workbook, as all other will be skipped, I need the version number from the workbook name, means I need the place where the number is.
Another option would be to check the creation/last modification dates of the files and take the newest.

Which one do you want?

**piggyfox** · 05-30-2013, 10:00 AM

The Version number is at the end of the name example xxxxxxxxxxxxv1.01.xls
whichever way its easy and faster to pick the new version its fine with me, prefer checking the version number which will be sequentially increasing though.
Cheers

**tehneXus** · 05-30-2013, 01:59 PM

Adapt the path in the code and hit the button: Summary-Workbook.xlsm

"Workbook v1.03.xlsx" used to update A2

**piggyfox** · 05-31-2013, 04:19 AM

its giving error with "Please check the filenames of the update files" .... i stored 4 workbooks in a folder and changed the path in code to point to the folder. created a new "Workbook V1.04.xlsx" and changed the value A2 to test. went into debug mode and observed that the stmp is getting file name (without .xlsx) but still it failing... can you tell me what could be the reason/ anything i need to take care .

2. Tested with new summary-sheet and having same 4 workbooks with data ..the result is that it pulled only column 'B1' and 'Version' not 'C1' & 'D1'.

Let me know any corrections to be made. Thank you.

**tehneXus** · 05-31-2013, 05:33 AM

hehe, I checked for "v" in filename but not for "V", fixed it and another issue which prevented the last record to be updated.
(Note: in case you notice a problem please upload at least 1 test update file)

Please try (Summary + Testing Sheet): support.zip

**piggyfox** · 05-31-2013, 08:09 AM

Working on the current one you sent ... appears that its writing version number instead of the document name under 'VERSION' column - summary-sheet you sent has that .
Thank you

**tehneXus** · 05-31-2013, 11:51 AM

Here with file names, no extension: Summary-Workbook.xlsm

Extract specific column data from specific sheet from multiple files in a folder

LinkBack

Thread Tools

Rate This Thread

Display

Extract specific column data from specific sheet from multiple files in a folder

Re: Extract specific column data from specific sheet from multiple files in a folder

Re: Extract specific column data from specific sheet from multiple files in a folder

Re: Extract specific column data from specific sheet from multiple files in a folder

Re: Extract specific column data from specific sheet from multiple files in a folder

Re: Extract specific column data from specific sheet from multiple files in a folder

Re: Extract specific column data from specific sheet from multiple files in a folder

Re: Extract specific column data from specific sheet from multiple files in a folder

Re: Extract specific column data from specific sheet from multiple files in a folder

Re: Extract specific column data from specific sheet from multiple files in a folder

Re: Extract specific column data from specific sheet from multiple files in a folder

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions