Consolidating excel files into one workbook (VBA project) - updated 9:10pm
$30-100 USD
Abgesagt
Veröffentlicht vor etwa 16 Jahren
$30-100 USD
Bezahlt bei Lieferung
Step 1
? Take 1 page from each of a group of XLS files in a directory and create a workbook.? Each Tab in the new workbook will be named with the file name it was taken from (actually the last half of the file name which is a date).
So if there are n files, there will be n tabs in 1 workbook.
?
Step 2.
Find and Replace procedures
Add a column to each tab
Step 3
Create summary tab
## Deliverables
?
**Step 1**
?
-? ? ? ? ? ? ? ? Take 1 page from each of a group of XLS files in a directory and create a single workbook.? Each Tab in the new workbook will be named with the file name it was taken from (actually the last half of the file name which is a date).
-? ? ? ? ? ? ? ? files may all be in 1 directly, or in subdirectories of file.? So code should check all subdirectories.?
-? ? ? ? ? ? ? ? VBA should prompt to choose folder.
-? ? ? ? ? ? ? ? Most of the time the excel files will only have 1 tab, but in some cases there will be a tab labeled “cover sheet??.? I do not want this one, I want the other one, which will be named “None??
?
**Step 2**
-? ? ? ? ? ? ? ? Add a column in each tab with a “unique key?? as shown in template.? Constructed from 3 columns in the following manner:
o? ? ? ? ? ? 1st 6 digits from CUSIP Column (convert to all UPPER CASE)
o? ? ? ? ? ? Append a “C?? on the end (e.g. 123456C) if either the Class column or Put/Call column contains “Call?? or “CALL??, else if
o? ? ? ? ? ? Append a “P?? on the end (e.g. 123456P) if either the Class column or Put/Call column contains “PUT?? or “PUT??, else if
o? ? ? ? ? ? Append “E??, class contains “COM??, “COMMON??, or “ORD?? (note: COM NEW, would be a positive match), else
o? ? ? ? ? ? Append “X?? on the end, 123456X, if none of the above are true.
o? ? ? ? ? ? **** this ‘table’ should be clearly marked in could so I can add/edit these conditionals.
?
-? ? ? ? ? ? ? ? Conform all columns to the layout in template.? (They wont always be in the same place and you’ll have to search for the heading).? Data used for step 3 wont always start in the same row.
?
?
Step 3
-? ? ? ? ? ? ? ? Create a Summary page that takes all the data from each of the pages synthesizes them into one file.? ? Each KEY will have a unique row, and then there will be 2 columns for each tab.? In the event that there are two datas with the same KEY in the same period, they should be added together.
-? ? ? ? ? ? ? ? I’ve used vlookup in my test, but I don’t expect you to do this.? I expected all the ‘work’ was going to be done in the code.? But I really don’t care how you do it as long as the data is right.
-? ? ? ? ? ? ? ? Make it so it’s possible to click on data and have it jump to the source tab.? i.e. an “on-event?? type thing, where I can either click, or hit a key combination (ctrl-shift-x) and then have it jump to source tab for that data.
?
Step 4
-? ? ? ? ? ? ? ? Second summary page.?
-? ? ? ? ? ? ? ? Data starts in row 15
-? ? ? ? ? ? ? ? First column should be first 6 of KEY
-? ? ? ? ? ? ? ? 2nd Column Should be last digit of KEY
-? ? ? ? ? ? ? ? 3rd Column Blank
-? ? ? ? ? ? ? ? 4th Column first “date_Shares?? column,
-? ? ? ? ? ? ? ? 5th… etc.
?
?
To run file, I would like to:
1. load excel workbook that contains your VBA code
2. Worksheet will have a button to RUN.? And table for find/replace as described above.? ?
?
Zip includes:
Template
4? Groups of files that the macro should run on (this is not them all, just an example)
After it’s completed, I will have you test VBA on a few more groups of files, which I will need your help debugging.
?
?
Some known bugs that you’ll probably encounter:
-? ? ? ? ? ? ? ? CUSIP numbers may contain leading 0s.? I need these.? Keep as text, so excel doesn’t cut them of.
-? ? ? ? ? ? ? ? In my sample data, no result returns #NA.? please return 0 instead.
?
?
?