Excel macro task

abdb469
abdb469 used Ask the Experts™
on
I have series of spreadsheets containing fund information with each spreadsheet having a file name in the format “2012-xxxx [space] fund name” e.g. “2012-0001 ABC Fund.xls”, “2012-0101 Emerging Markets Fund” etc.  These spreadsheets will share a similar template but not all the funds are of the same type and therefore there may be less or more field names.  Also the location of the field names may not necessarily be in the same row (but will be in the same column) for the different category of funds.

OBJECTIVE
To try and have a “summary spreadsheet” which will open [or if more efficient, somehow “read”] a select range of cells in a range of spreadsheets in a folder between [where the file name begins “2012-xxxx”] and [“2012-yyyy”] and copy certain cells of information to the target spreadsheet.  

RULE
Regardless of the name of the fund after the [reference number] in the file name, look at all spreadsheets in a folder called “Z:\Dump\Test_Funds” (with underscore) that meet the above filter criteria i.e. that begin with [whatever prefix is in cell A3] followed by [B3] and ignoring the fund name after that, open each spreadsheet upto [whatever prefix is in D3] followed by [E3.

Each file that is opened/read according to the above filter, should then copy and paste the value of the adjacent right cells containing the words “Fund’s name” in column B to the target spreadsheet starting under cell B10 and also “Fund's NAV (MN EUR)” starting under C10.  The formula should be programmed to the macro button please.  I know this task is best done in a database of some sort, but this is not an option.

Hope that’s clear, if not please ask.

Many thanks.
summary-macro-test.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
This Macro is very possible as long as each file that you are reading in has the same column names. Any chance of supplying us with a few input files?

That way it is easier to make it work.

Next to that - is my assumption correct that you give as input the files to start with and end with and the Fund's name to search for in the input files?

Author

Commented:
Hi

Unfortunately, because there are different types of funds, the columns differ slightly.  In fact, the source template is such that the source fields are in rows going down rather than columns going across, if that makes sense?

Could you rephrase your question?  The name part of the filename should be ignored as its just for humans to understand the name of the fund associated with the reference number at the start of it.  The reference number is for the macro.

Thanks.

Author

Commented:
Example source spreadsheet attached.

It needs to be based on reading the exact cell row (not column) names to avoid the problem of different style templates being used.

Thanks.
2012-0013-Emerging-Markets-Test-.xls
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Commented:
I will have a look at it - but if the columns are different it will be hard to write an automated script for this. Especially if you want it to make sense in the summary excel sheet.

The rest makes total sense... (as in the naming of the files etc)... and that should not be a problem.

Commented:
So if I got this straight:
You would like the files in folder “Z:\Dump\Test_Funds” that match filter criteria mentioned above.

You would like to only copy the Fund name from these files (in the example that would be Emerging Markets Fund) and the number associated with the row starting with Fund's NAV (MN EUR) (in the example that would be 193).

So that it would look like:
Fund Name1    Fund's NAV (MN EUR)1
Fund Name2    Fund's NAV (MN EUR)2
etc

And you would like it to happen when the Macro button is pushed.

Is that correct?

Commented:
Is it also correct that every file can have different row names (field names), so a search is needed for Fund's NAV (MN EUR), as this will not always be at C26?

And last point to note, is the fund name always at C9? or does that differ too?

Thanks.

Commented:
I have started writing the macro.

First point to note - have you considered Pre-fix 1 in the format 2012 (2011, 2013) so no "-". This is so that it becomes easier to select more years in your range - as in, if your FROM is 2012 and your TO is 2013 - you will get all files from 2012 and 2013 with the Pre-fix2 in the right range.

Let me know if you wish this - otherwise you will only be able to select 1 year prefix even though you have two selection boxes - or I will have to take the "-" out again in the macro.

Commented:
OK last question and then I'll wait for answers before I continue:
The second part of the ID is that always 4 numbers? (as in the part after 2012-)

Author

Commented:
Hi

Ok, I'll answer in the order of each of your last conversations since my last post.

1. You can safely assume that column B will always have the row headers in there i.e. Fund's name etc (Col A will always be a topic category and Col C will always be the result of the Col B criteria)

2. >>You would like the files in folder “Z:\Dump\Test_Funds” that match filter criteria mentioned above.
Yes, all the source files that need to be opened or looked at according to the filter, are in that folder path.

>>You would like to only copy the Fund name from these files (in the example that would be Emerging Markets Fund) and the number associated with the row starting with Fund's NAV (MN EUR) (in the example that would be 193).

Yes, that is correct.  But in truth, I actually want to copy about 10 possibly 15 different fields of information.  These fields could be any of those listed in Col B.  I have only used  Fund's name and Fund's NAV as an example otherwise it would make this question very long and complicated.  If you think there is an easy way of making a table in which one of the other worksheets contains the list, this would allow me to create a summary worksheet without editing VB code myself, that would be awesome, but I didn't want to ask too much in one question.  If you feel you can do that, I would be happy to open another question and award another 500 points.  In that situation, would you use a "MATCH" command or something?

>>So that it would look like:
Fund Name1    Fund's NAV (MN EUR)1
Fund Name2    Fund's NAV (MN EUR)2

That is correct.  Column headers starting from B9 to whatever X9 depending on the number of field criteria I need e.g. Fund's name.

>>And you would like it to happen when the Macro button is pushed.
Yes please.

3. >>Is it also correct that every file can have different row names (field names), so a search is needed for Fund's NAV (MN EUR), as this will not always be at C26?

That is correct.  The field's name will be standardised regardless of the fund type or template type but the field name could be in a different row, hence why I cannot base this macro on looking up a single cell in each of the spreadsheets but rather the exact name.

>>And last point to note, is the fund name always at C9? or does that differ too?
Like the above, the fund name will not always be at C9 because it could be in a different row due to a different fund type source template.

4. >>First point to note - have you considered Pre-fix 1 in the format 2012 (2011, 2013) so no "-". This is so that it becomes easier to select more years in your range - as in, if your FROM is 2012 and your TO is 2013 - you will get all files from 2012 and 2013 with the Pre-fix2 in the right range.

I'm open to suggestions here.  I figure that until I'm allowed to hire a systems developer, this method will only be a temporary system.  The reason why I put a (-) in the prefix 1, is that I thought it was easier for the human eye to read a file that says "2012-0001 Emerging Markets Fund" than "20120001 Emerging Markets Fund" (and easier to choose the next sequential number for a file).  Unless you are saying that you are able to create it without the (-) but with a space so that it appears as "2012 0001 Emerging Markets Fund", but I always thought it is best not to use spaces in file names?  

At present I have approx. 70 files with the (-) in the prefix which I would manually have to rename, but I am happy to consider a different system if you think it better but I would like to know how the file name when saved would look like.

>>Let me know if you wish this - otherwise you will only be able to select 1 year prefix even though you have two selection boxes - or I will have to take the "-" out again in the macro.
I'm not sure I fully understand why if the (-) remains, why the range wont work?

5. >>The second part of the ID is that always 4 numbers? (as in the part after 2012-)
I've deliberately created the file name in such a way that I do not think that in one year, I will reach more than 9999 funds, so I think it is safe to assume that the ID will only have 4 numbers.

I have attached an example file of what the output summary sheet would typically look like if the macro ran correctly.  The field names would obvious change depanding on what was chosen from a table in another worksheet.

Many thanks for this, I appreciate it.  Would you be happy to continue this task over several fresh questions if I need to add to it?
Summary-template-example.xls

Commented:
Just a point:
4. You will not need to change the file names - just the names in your selection box (so instead of 2012-, 2011- and 2013- you would have 2012, 2011, 2013.

And yes I would certainly be happy to work on the task over several questions.

But let me first get this one working! I will do my best to get it working today.

Wies.

Author

Commented:
Oh I see. Yes, in that case I'm ok with the suggestion for the filter criteria to have the (-) removed.

Many thanks again.

BTW - I'm based in the UK and will be able to try any macros whilst in the office until 6pm but should be able to answer any further question from home later in the evening if required.

Commented:
Nearly got it working - the funds name appears in the correct place, the Funds NAV is being a little stubborn ;)

A working version will be supplied in a bit - if you want to add extra fields later, that will be possible. I will try and make it as simple as possible at that point - but now I will just get this to work.

Commented:
Right done - it was a silly typo on my end why the NAV didnt work.

I have only tested it with the one file - but added it a dozen times to check it worked.
As long as the Fund's name is always typed exactly like that and same with the NAV row name, it should work.

When filling the prefix in the excel sheet: The first prefix always has to be a year (4 digits), the second prefix can be any number upto four digits - so you can just use 1,2 or 3 digits as well - this is because of how I check it.

The files names do always have to be in the format YYYY-PPPP - so 4 digits for year and prefix.
summary-macro-test.xls

Commented:
I have added quite a bit of comments in the code.
Two to note are the ones with ***.

The first one shows you where you can change where the files are stored.
The second one shows you where the fields are added.
Adding them manually here is possible - you will need to repeat the four lines for each new field, as well as create variables for them. You will then need to add them to the summary worksheet by using the offset.

I am sure it is possible to do this in some sort of loop as well, by adding the field names in an array. If you would like me to look at that let me know.

Commented:
PS do make sure you have no spaces in the folder names leading up to the excel documents - otherwise it will not work.

PPS I am based in the Netherlands so if you have any further questions I will be around till you leave work (handy having only a one hour time difference!)

Author

Commented:
Hey,

Ok it stops at the following debug point

   ' Activate the merge workbook
            Windows("summary-macro-test.xls").Activate

thx.

Commented:
What do you mean by stop?

When I test it here with that one sample file - it just runs.
Do you get an error?

I have changed the path location when I test it - but that should not make a difference.

Author

Commented:
Sorry yes, it returns an error and the dialogue box says Run time error "9".  Subscript out of range.  This is when I try and run with 1 as the prefix 2 for the start date and 40 for the prefix 2 for end date.

Commented:
Strange I do not get the same error - but I do not get the corrrect results either.

I'll just debug it!

BTW: What version of excel are you using?

Author

Commented:
I'm using Excel 2007.  But the spreadsheet will be used on versions 2003 and 2007.

Thx.

Author

Commented:
Just to be sure I'm not doing anything wrong, I've attached a screen shot of the path folder and file names.  Pls see if you can see anything obvious that is wrong.  Thx.
19-04-2012-15-31-52.jpg

Commented:
I have fixed the bug that was in my version (the fact it did not filter the files properly).
I do use Excel 2010.
If you still get the same or different error code, do let me know.

Just saw your image as well.
I have a folder with only the excel files in them. Do you need it to scan through the other folders too?
If you have other files in the folder, that may be causing the issue. The error would most like be for files that do not have the matching file name convention (YYYY-PPPP text).

I will see if I can add some checking of file names before continue in code...

Another issue could be the spaces in the names of the files. I will check if VBA has a way of handling those spaces.
summary-macro-test.xls

Author

Commented:
hmmm...I still get the same error and code (before and after I have moved the other folders out).  I've even remove the name of the fund as part of the file name so that only the numbers are there for two test files, but still no luck so not sure even if the spaces are the problem when I remove them.

No, I dont need the sub folders scanned.  I've kept all the xls files in the main test folder to make it easier...if that matters.

Author

Commented:
I'm just wondering whether the problem is before it gets to any of that stage because the error comes before I even see anything happen on the screen i.e. no swishing around of actions as the macro is performed?  Could it be some more simple like the linking of the prefix 1 and 2 for either the source of end file range?

Commented:
I have tested it here with spaces in the file name - and that does seem to be the issue.

I will try and fix it so that it works with spaces in the file name.
Could you try it with a couple of files, all without spaces in their name?
That way we can see if the problem lies there.

Some time later: Oddly enough I do not get errors with spaces now. But I havent changed anything in the code. So there must be another issue. Most likely incompatibility with 2007 (I have 2010).

What is the exact error you get and on what line does it stop?
Thanks,
Wies.

Commented:
Could you also send me a screen dump of how you fill in your summary-macro-test before hitting the button?
Thanks!

Author

Commented:
attached.
2.jpg

Commented:
Oh could you also send me a screen dump of what you see when you click on Debug :D

Author

Commented:
I just tried it on my colleague's Excel 2003 version and the same problem.

Commented:
And have you kept the file name of your summary sheet the same as in summary-macro-test.xls?

If this is changed it will give you the error 9 - as I call it specifically in the code in this line: Windows("summary-macro-test.xls").Activate (twice).

Author

Commented:
attached.
3.jpg

Author

Commented:
I'm not sure I understand.  Why does the summary sheet need to be named summary-macro-test.xls - how is that relevant to the macro?  I have changed it and it works a little better but then crashes again.  See 2 screenshots.
4.jpg
5.jpg

Commented:
The reason it needs to be named summary-macro-test is because I've hard coded that in the macro (as you need to change the sheet that is active after having copied the details from the excel files). The quickest way to get back to the summary sheet is to use the hard coded name. I can, of course, change the hard coded name to anything you wish - but currently it is hard coded to "summary-macro-test.xls". I could see if there is a way to grab the name of the .xls file when the button is pushed - before it goes to another excel sheet to grab the data.

Your next error is exactly what I was scared of - that all the files are not exactly named starting with YYYY-PPPP. I will add a check to ensure that the Year and Pre are both numbers before it changes it to a number - it does mean you have to check your file names.

_______

See file below - now the file name does not matter.
summary-macro-test.xls

Commented:
I have now ensured that it ignores all excel files that do not have numbers at the start of the file as in the pattern YYYY-PPPP.

And of course - you now change the file name of the summary file!
summary-macro-test.xls

Author

Commented:
Guess what......IT WORKS! :-))

Not a single problem.  This is awesome.  Top man (assuming you're a guy!).

Thanks so much.  Just doing a manual summary by extracting 10 fields form each spreadsheet takes me a few minutes often 15 min if I delay and procrastinate multiplied by 30 funds a week....well you can do that math :)

If you dont feel taunted by this exercise and now that you have a feel for what I am trying to achieve, I would love to add to this by having a table of criteria (in a new question of course).  

If you are still happy to do it, would you prefer me to extend the question here in small steps and when working open a new question and then immediately post it, giving you the ability to work on it?  if not, I will close this one and then open a fresh one (but at risk of someone else commenting).

Thx again!

Author

Commented:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27681764.html

Could you please save a copy of the same spreadsheet to the above link and I will close that question and award the points there too as that one was more of a simplified version of this one (I opened it because nobody was replying to this one).

************

Basically the next task would be for me to have a table on a second worksheet listing all the possible field names in all the different fund templates there and maybe have a check box to select which one of those fields I would like in the front worksheet.  I'll try and update the template to make it clear for tomorrow.

Have a good evening.

Commented:
Sounds great... and I'm not a guy :D

I will save a copy of the spreadsheet to the other question. And I look forward to working on it some more... but I have a feeling I already understand what you want it to do.

Till tomorrow!
Wies (which is a dutch equivalent of Louise).

Author

Commented:
Having worked for ING Bank in London for 8 years until 2010, you would have thought I should have picked up on that :)

Ok, please let me know if you are happy to continue the question here and then I open a new question after the solution is prepared one by one or open a new question and closing this one?

Smaller additional tasks (I regard each as its own question so will post 500 points for each):

1. New Line count column to be automatically inserted to the far left and line count to correspond with the number of funds listed (we can remove the blank column)

2. New "Application ref" number column to be inserted between line count column and Fund name column.  The application reference (only the number prefix part) could be taken either from the name of the file or from a field I can create from one of the "criteria selections" on worksheet 2.  I haven't decided whether the macro takes this from the file name or from the content of the worksheet itself.  What do you think - or does it not matter?


Bigger additional tasks

1. The spreadsheet attached has a worksheet called "Criteria selection" and contains all the possible field names I may want the macro to pull information on from all the files.  I would like it such that new fields could be added to Col B (of the criteria selection worksheet) in the future and if the corresponding tick box is selected I would like the macro to pull only those fields e.g. if no. 1, 8,19 and 29 were selected, the maco would produce a table with the fields Application reference, Fund's name, regulator and Rating into the spreadsheet.  So this is an extension of the original request in that it now allows me to select more criteria from a table.  Once again it should be based on exact field names and not row numbers when searching because of the same problem i.e. it might not be on the same row in each template

2. Some of the source spreadsheets in the Z:\ path have multiple worksheets with sub-fund information in the other sheets.  At present the macro will open each spreadsheet and look at the first worksheet.  How easy is it to include other worksheets if we put in a condition (because some worksheets may just have rubbish or unrelated stuff - do you recommend each "relevant" worksheet has a prefix in the worksheet tab name so that macro knows to search it too?

Many thanks.
summary-macro-test-20-04-2012.xls

Commented:
This same question is fine!

2. Taking the reference from the file is just as easy - as I grab it from the file name seperately in the code already so I can check it against the criteria.

I will start those two points after I've done some shopping with my wee daughter.

Bigger tasks
1. This is definitely possible, I may even get it done this afternoon. I had already written pseudocode last night for this.
2. I will have a look at the best way of doing this. It will need to be in some kind of naming of the worksheet - or that only worksheets with the specific field names asked for are taken into account. Actually, now that I think about it - that last would be easiest. If the Fund's Name is a standard one that will only occur in worksheets which need to be considered - I could write the code that ensures all worksheets in a workbook with Fund's Name in column B somewhere will be recorder. How would you like the summary sheet to differentiate between the different workbook worksheets? Perhaps workbook name, worksheet name as two different columns in the summary sheet?

Author

Commented:
With regard to the last worksheet question, it will not be necessary to differentiate (*he says thinking hard*) between the different sub-funds on each worksheet.  

Basically, my rule is this.  All funds which are "a stand alone funds or single funds" belong to only one worksheet in one spreadsheet.  Where it is an "umbrella fund" (that is esentially a symbolic company that owns sub-funds), then each sub fund will be on its own worksheet but in one spreadsheet.  There will never be a "umbrella fund worksheet".  In simple terms, think of it as a parent/child relationship.  Where there are children with no parents, the children are on their own and have their own worksheet.  Where there are children with parents, the parents are referenced in the contents of the spreadsheet ("Umbrella fund name").  Kind of like a hierarchy.

With that strange long winded explanation out of the way, the summary worksheet will treat ALL funds, regardless of whether they are umbrella funds or single funds, as one and list them.   Only the filter criteria of "umbrella fund" if selected, will highlight the type of fund it is.

Not sure if that was clear?  :)

thanks you and enjoy your shopping.  Hope the weather is better there than here!
If you want to save each version of the worksheet with each part of the above questions tested and completed, it will be easier for me to post the new questions on EE and for you to reply to it and for me to close it.  I can see you get your ranking moved up a lot this month!

Thanks so much.

Commented:
The weather was good - it was a nice strall to and from the shops. My daughter is having her afternoon nap (she is only 9,5 months), so I have plenty of time to work on it now.

Your explanation is clear - I will now work on the two smaller points and then get to this one. I will post each finished worksheet here, so you can test it. See if it does what you expect it to do.

_____________________

First one done:
summary-macro-test.xls

Commented:
Quick question about the second part - does it need to be in the format PPPP?
I mean - do you need the leading 0's? So if the prefix was 1 it would show 0001.
Or is showing 1 ok?

The attached file does not show the leading 0's - so you get the idea of what I mean.
If you need the leading 0's I will add them.
summary-macro-test.xls

Author

Commented:
Nothing is fixed in this project, as I only just started this a few days ago so things can be changed if you feel a better system/way of organising my files and working the macro is justified.

I just assumed that by having the PPPP in that format with the zeros, it makes it easier to sort or order the files in Windows explorer because they should then all be in sequential order otherwise sometimes the orders get mixed up if you know what I mean?  As long as 2012-25 is always above 2012-443 [space] filename then it shouldnt be a problem.

*******
task result

task 1 - yup that's fine.  Could we just add a tiny cosmetic change.  Can the macro also maximise the width of the fund column to the longest length of the underlying fund name and can the # number column be left justified?

Please post the spreadsheet to the following place
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27685161.html


task 2 - Almost there, but could we pls get the full application reference, that is the pre-fix 1 and 2 i.e. 2012-1.  Also, it seems to be reading other files other than those specifically prefixed with "2012-0001".  I have some other temporary files that I have not yet had time to change and sequentially add a number to from 2011, so I left them in the same folder and called them "2012-X001".  The x at the beginning of prefix 2 was just so that I can distruingish between the files, but did not expect them to be read.  

i can do one of two things, either move those files out which will solve the immediate issue, but is there a way of forcing it to only look at files when only the prefix 1 and 2 conditions are met and ignore anything else?

Thx.

Thanks.

Commented:
Ok. task 1 - done - I've added the cosmetic changes to that version (and to the version I'm working on right now for task 3).

Task 2 - I will put the complete prefix in there, that shouldnt be a problem.
I will also look into why it sees X001 as a number!

Commented:
Task 2 completed.

Busy with Task 3. Half done. Will try to finish it before 6pm your time so you can see if it is what you wanted.

One question: If there is already data in the Summary table, I now have it add a new row of column names under the existing data - with the new data under that.
Is that what you want?

I could also override the data. But that is upto you.

If you do want it to continue (with a new row headers) - do you want the numbering to start back at 1 or to continue from the previous?

Author

Commented:
>>One question: If there is already data in the Summary table, I now have it add a new row of column names under the existing data - with the new data under that.
Is that what you want?

Yes, I did notice that too, and actually that's perfect and was pleased when I saw that.  Its unlikely I will run a series of ranges in the same spreadsheet, but its useful to have it that way and I did smile when i saw it happen as I would have expected it to either crash because the immediate row was occuped with data already or overwrite it.  So, no please keep as it is.

If we can continue the numbering that would be great, so no restarting of the numbering.

I cant keep up with your speed :)

Commented:
LOL programming is a hobby of mine (although my study was focussed on programming), I prefer the human aspect of the IT world above programming as a real job :D

But as a hobby I love doing it well and fast :D

I only need to ensure the ticked items get in an array - but the rest of the code is written - so as long as my wee girl naps till I'm done... I should be done before 6pm.

Author

Commented:
No worries if it doesnt all get done today......I will be here until a little after 6pm today, Friday is when everyone else leaves and the quietness of the office allows me to finish any weekly admin chores (as sad as I am!).

To be honest, I actually find this perversion of "macro porn" quite exciting as I know it will impress my Spanish boss who does everything manually :)

When I was at ING, I hired a young Dutch developer on secondment from our Amsterdam office and he was great.  Working for a different organisation now and we cant afford even a intern student at university otherwise I would have developed all this plus a more in a SQL database with asp.net or C# front end with some ajax controls and telerik MIS reporting tools!

Do you mind if I ask what you do i.e. self-employed programmer / consulting etc?

Commented:
My daughter woke up and spend an hour playing, so I havent been able to completely finish it. You will see it tomorrow.

LOL "macro porn" hadnt thought of it that way before! :D
And I'm glad it will impress somebody. Nothing worse than doing some programming and it has no effect.

Currently I am unemployed. Well I lie, I am self-employed but I earn nothing doing it.
I do a lot of volunteer work, next to experts-exchange I teach senior citizens the basics behind using a computer.

For real work, I am looking at becoming an IT teacher at a polytechnic. I would love to teach people all the useful stuff I know in IT. And as I heard, the teachers in IT are all going grey and very manly - I'm sure I could make a difference. I just need to get my papers to prove that I'm allowed to teach here. ( I have dual citizenship  - New Zealand and The Netherlands - in NZ I could teach at a polytechnic with my degree... grizzle).

Author

Commented:
Hey,
No worries, it can wait until next week, spend the time with your family.  My wife and I are expecting our first child in Nov, so looking forward to that.

So are you actually Dutch or Kiwi?  Apart from the link to my previous Dutch bank, I was trading GBP v NZD currency as a hobby.  So any economic tips I can award more points :))

I still have a lot of contact with my colleagues at ING in Amsterdam.  Depending on where exactly you are and if you are looking for work (albeit at a bank), I can dig out some old contacts on my LinkedIN over there who are closely associated with the bank's risk systems and who work in the system developing area.  It's always a long shot, but strange things have happened.

I also do volunteering.  I recently joined IT CAN HELP (http://www.itcanhelp.org.uk/) and fix old people's computers.  I had a hobby since I was young building computers, taking them apart etc.  So a few times a month I help blind or disabled people when they've corrupted the operating system or needing to replace hardware its.  Its quite satisfying.

I also career coach socially deprived youth for one other charity which I always keep forgetting the name of and also from a strategic side, am a volunteer director for Camden Credit Union (lend money to those in socially deprived backgrounds who cant get credit from mainstream banks).

So I think you'll do well in your search.  I hope you find something soon.  The job market is still pretty tough over here, I'm not sure how [Amsterdam/Rotterdam?] is right now.

Anyway, let me know if I can put you in touch with anyone.  Thanks for your help today.  I will login to EE over the weekend from home but will not be able to fully test any work as I wont have access to the shared path folder.

Have a great weekend.  :)

Commented:
I am actually both! That's the fun of dual citizenship - two passports.

But I was born in the Netherlands and lived most of my life in New Zealand (from my 7th till I turned 25). I have lived in the Netherlands since 2003.

And economic tips on New Zealand hmmm let me see if I can get my mates over there to tell me some gossip lol.

I live in Groningen. Which is about as far away from Amsterdam as you can get.
I do plan to move to Den Haag (the capital) in the near future, mainly because my parents in law live there - our daughter would do well living closer to her grandparents I believe (especially as my parents still live in New Zealand - so having 1 set of grandparents close by is better than none).

Volunteer work is so rewarding. So glad I started doing that in 2009. And yes the job market is rather tough here too - although not in the IT world. I am sure I could get a job if I really tried... I just really want to see if I can head into teaching before I take on another "normal" IT job :D

I have actually nearly finished the next bit with the checkboxes - but the checkboxes are being a pain. VBA is being stupid with what value the checkboxes return - as in when something is checked it does not recognize it as such. I may just have to remove the checkboxes and replace it with something else... but I will leave it for now - give my daughter some dinner and worry about it again when she is sleeping.

You have a great weekend too. And before the end of the weekend I will have it completed. (My husband works a lot this weekend and my daughter sleeps a lot - so with that combination I have a lot of spare time to spend on my hobby: Experts-Exchange :D).

PS congrats with your upcoming first child!! This is also our first... and it is sooo wonderful. It's like she has never not been around! Just a bit of advise... never listen to advise of others lol - each child is different and you as parent know best - follow your gut :D (That was the best bit of advise I received, so I thought I'd share!).

Author

Commented:
Thanks, I'll remember that :)

Commented:
Checkboxes = there were many many many there!

Under every checkbox were another 3 or so. So I've deleted them all.
I now have the cell changing to a tick when it is clicked on... and when you click again the tick disapears. That way I have more control over it AND the big bonus is - the excel file is smaller! :D

Anyway... working on the last things for this one. Next week I'll start the last change you wanted.

Author

Commented:
Hi

I've emailed myself home some of the test files.  Do you know how I can make myself a mapped z drive on my home computer?

If you can attach the file I'll give it a go.

Thx.

Commented:
It's done - and you do not need to map yourself a Z drive. (I have added it to the other question - someone else had a go at it too).

All you need to do is change the line in the code that says MyPath = "Z:\..."
If you look under that line - you see my standard test folder for your files too.

I will attach the version I have here too.

I could in fact create a field on the summary sheet above the Prefix criteria in which you put where the files are. That way you can always change it whenever you like. Let me know if you would like this amendmend. It is easy enough to implement.
summary-macro-test.xls

Commented:
I have created a field name up the top, that way you can just point the excel sheet to the right directory. Do ensure to use backslashes and not forward slashes - but you can see that in the example of the attached file.

Oh and the reason you do not see any column headers, is because these are created when you hit the button.

PS if you dont want the file name where I placed it and you would like to see it on the criteria page or somewhere else - do let me know, moving it isnt an issue - just need update cell where the file name is in the code.
summary-macro-test.xls

Author

Commented:
Hi

Sad I know - Friday night at 1am and I'm looking at this.....anyway :)

Yup, your latest spreadsheet again, is spot on.  Thank you.  And good call with putting the file location in the spreadsheet, that was something I was going to ask down the line so you pre-empted me and same with the column headers.

Dont know what happened with respect to the tick boxes...was trying that for the first time, must have copied over there a few times.  For my knowledge, what is the tick style you created and how do you insert that as I couldnt find that in Form Controls?

******
Just some minor cosmetic changes for the last task 3 before you move onto 4.
a) Where the criteria selection refers to dates, could we have the macro also format the date related fields such that it appears as e.g. "30 January 2012"  (at present they get produced as numbers e,g, the 30 January 2012 appears as "40938".  From what I can see, there are three in the criteria selection: "Date of credit limit request", "Credit Authorisation date" and "Fund inception date".

b) Could the website link be produced in the typical blue URL type colour and font?

c) Could all the columns' widths automatically be set at the max length of the longest underlying fund's criteria like we did for fund name length column?

Just a question - but how difficult would it be to add a new column in the criteria selection worksheet which allowed me to also select a number for the column order (I cant change the order of the selection criteria because my boss has kept them in that list because they are ordered by cateogry (in fact I will need to add a higher level cateogry for them later).

I will again post a new question link for each of the smaller questions too once completed.  Mabe save each one here as you do the part a b and c which will make it easier to open a question and close it soon after :)

Thanks.

Commented:
LOL 1am :D

The tick style is actually just a font. It's called Marlett. The letter "a" is a tick.
I added code to that worksheet, that when you click in column C from row 2, it will place a tick there (the letter a) - it then places the focus on column B (just so that you can click again to remove it - otherwise you would have to click elsewhere to remove it, which I would find cumbersome).

In the code under the button it just looks for where there is a letter "a" in column C, and puts the value in column B into an array.

If those three are the only three dates, I can hard code them in. Later it may be a good idea to add a new column to Criteria selection with type of criteria - with choices like Date, Number, Text. That way you can do other great things (cant think of any examples but I'm sure it will be useful later on). Let me know if you would like that too. For now I will hard code the three dates in.

b)  You mean fund website? OH OH - just had a brilliant idea - add that to your Criteria Type column! (See a Criteria Type column sounds more and more attractive as we go!) :D

c) Definitely possible. I thought the code I wrote would automatically do all widths. Will look into why it is not doing this.

It is definitely possible to add an index for the columns. I will need to check write some extra code to ensure you do not have overlapping numbers in that column - otherwise you will have to go about checking it yourself if say you add one item to display in column 5, and then you add another (accidentally) to display in column 5 too. The behaviour of this we will need to discuss... should everything move up one place? Should the one you select last at 5 stay at 5 and the other move to 6 etc? And if you untick one line the others should of course fill the gap. And adding a higher level category is easy to add. Just a new column in the Criteria page.

Let me know if you would like a "Criteria Type" column otherwise I will hard code the date and website columns (which of course is never a grande solution... nudge nudge).

Commented:
I found a few more dates, and there are quite a few websites in the list - so I have added a Type column, with only two choices - date or website (as those are the only two we are working with right now).

Later, if needed, we can add different types if you want things formatted differently.

Now I will get the code working to format it properly!

Commented:
Right - the date and website formatting works.
See the attached file.

The code actually changes all widths of the columns to the longest length.
Which column wasnt it working for? The sample data I have seems to work for all so far.
summary-macro-test.xls

Author

Commented:
Hi

Yes, you are right it is working correctly i.e. column widths.  I think I must have opened a wrong version or one posted on the other questions, but your last one works fine for this purpose.  For the date, could I have the long form format please i.e. "30 January 2012" (so that our American cousins dont read the format with the month first?

Thanks again.  Please post the solution to:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27686431.html

Regarding the column index thing, actually dont worry about it.  What I am proposing is perhaps a bit of an overkill as the main task for this whole project is to collect the data from 100s of spreadsheets and then slice and dice the data as needed into one of my formatted templates.  Instead, perhaps we can force it so that #      Application ref      , Client Code and       Fund's name are always first.  The rest I can live with.  (This will be a separate question before the start of the final task 4).

Thanks again Wies!

Commented:
Right that is done. There is a slight issue with the Hyperlinks (the blue lines) after I delete the test data, it sometimes lingers. I have tried to improve that in the attached version - so hopefully it doesnt happen again - lol.

Tomorrow (as it's 1:24am here now) I will have a look at the 3 you would always like to be first - do you want them to ALWAYS display, or only display first if selected?
summary-macro-test.xls

Author

Commented:
That makes two of us up late still :)

The three criteria can always be displayed first as all subsequent column data all refer to those three essential things like Fund name.

Thx.

Commented:
Ok. I have added a hidden sheet called CriteriaType. That way I can add CriteriaTypes later - easily, without having to change all the selection criteria in every line in column C of Criteria.

I have added a Criteria Type called Fixed. This is for Fixed Text.
It ensures the tick cannot be taken away from that row.
It also ensures those are always displayed first in the table.
When you set a Criteria to Fixed it will automatically add a tick (if it doesnt exist already).

The order of the Fixed Criteria is still done in the order that it is displayed on the Criteria page.

Only use Text Criteria as a Fixed criteria! If you would also like to Fix a Date/Website (URL) criteria - I will have to make a Fixed Date and Fixed Website(URL) Criteria Type - otherwise they will not format correctly to a date or blue underlined URL.

Just so you know: The Application reference line is ignored (so even if you take the Fixed and the tick away it will still be displayed) - as this is not taken from the excel sheet, but from the excel name.

Hope you have a great rest of your Sunday! And let me know what you would like changed next!
summary-macro-test.xls

Author

Commented:
A spot of shopping for the new Iphone 4S and the first hospital visit for my wife.....a good Sunday overall apart from the weather :)

For some reason in the last version, row 3 appears in bold and the "date of credit limit request" appears a number rather than a date even if I select the date from the drop down criteria selection.

I'll post a new question if I see you reply shortly (so I can select your answer first), otherwise will do so first thing tomorrow.

Merci!

Author

Commented:
Morning,
I hope you had a restful Sunday.

Thanks again for all your efforts on this.  You've exceeded my expectations as to what can be achieved on this task and it has really made me task of NOT having to open a range of spreadsheets any more, so much more enjoyable (and productive!).

I'll post a new question link sometime this morning when I know you're online, thereby giving you a chance to post the solution.

For the last (bigger task) relating to the sub-fund data collection (which essentially is one spreadsheet containing more than one fund on each of the worksheets), I was thinking how exactly this can be done i.e. to try and ascertain unique identifiers that will allow the macro to know that the spreadsheet contains more than one fund.

This morning, I've realised that my boss has named such speadsheets with the name "umbrella" as part of the long file name.  I will have to rename all those files such that it includes the prefix 1 and 2 at the beginning of the file name.  That is one way.  The only other way I can think of, is if the macro automatically searches all other worksheets to see if there are similar "Criteria selection" fields on the sub-worksheets.  My only concern with this second method, is if there is "rubbish" there or stuff that is not relevant, but then I'm probably over guessing as we wouldnt put non-related stuff in there anyway and in any case the macro would probably ignore as it wouldn't meet the rules that we setup to extract that data.

So, if you're happy to give it a go, maybe we can trial it by getting the macro to:

a) See if filename has the word "umbrella" in it (the word may or may not be in capitals - so need to make non case sensitive),  then

b) search all worksheets and extract the data (as if each worksheet was its own separate spreadsheet, BUT keep the same application file reference number (as the prefix 1 and 2 in the file name).  It is also possible that the umbrella fund may indeed only have the one worksheet i.e. one fund, and we add more funds to the spreadsheet in the future.

I also thought about an extension to this task (another question) that will allow for error checking i.e. because my boss has created several fund templates before, if the macro identifies a line which it cannot match, it generates an exception list at the bottom of the generated report showing what the exception was?  Also another new column in the generated report which shows how many sub-fund worksheets there were (this will allow me to double check the output in case any spreadsheets for non-umbrella funds are picked up).

How does this sound - I hope I havent complicated it too much?   :-)

Commented:
Glad to hear you had a good Sunday!
The Iphone 4S sounds grande - I'm on an Android phone at the moment (the Sony Ericcson Xperia Arc S) but the iphones are becoming more and more attractive.
Hope the hospital visit went well.

The bolding of row 3 etc, is because I was lazy and I did not delete the rows I used to test with. (Silly me). - Which means that if I had headings on row through - it does not delete the bold. I will now add that bold is taken off for those lines, that way I dont ever have to watch out what I delete lol.

I had a great movie night with my husband last night (our daughter went to bed early!). So I did not get a chance to go online. I will fix the bold thing now and then I will look at your following reply.

Commented:
Right done - that went quicker than expected lol. Now I can be as lazy as I like when I delete my test data (lol).
summary-macro-test.xls

Commented:
Right onto your next task...

Putting umbrella in the name would be the easiest solution. Currently all files that do not have the unique identifier get ignored (with a slightly silly just quit out if it doesnt match error handling - yes yes, sometimes I am a lazy programmer). So I will get to work on a) and b).  
Am I right to assume that the pre-fix is still in the name (the YYYY-PPPP) of the umbrella files?

LOL and no - it isnt complicated as such. It just ensuring I know exactly what you want before I start to program. So many roads lead to Rome and if the user requirements are not clear, the programmer (me) will definitely program it wrong lol.

Author

Commented:
Hey,

Yes, the prefix 1 and 2 will also be the same for the umbrella funds.  

lol - I know what you mean about the user requirements.  I spec'd out a system at ING and actually loved doing that and seeing the the results from my 2 system developers and that wasn't even my day job.  I guess I just get great satisfaction seeing this come to life and working as they should do.  I sometimes wish I because a software engineer, but gave up computer science at A'level as I couldnt hack it (no pun intended).  Despite that I did do various VB courses and HTML web site in later years that I subsequently loved, but can remember 0% now.  :(

My old age of 35 is now confined to spreadsheet and risk analysis of the dirty world of hedge funds and highly leveraged banks :(

BTW - have you ever developed any apps for your Android phone or done any app programming for iphones etc?

Commented:
My old age of 34 is confined to (luckily) nothing yet. Except I must admit when I was younger I was a lot faster (yes, yes, faster than even now lol - hard to believe I know ;)). Except of course, now I have a lot more experience :D

And yes, I have looked into development for Android and Iphone. Currently working on a way to display useful information from a webpage on a phone using the m.webpage method. Fun to try out - as I mentioned earlier I do a lot of volunteer work for the elderly - and one fo the things I've made are webpages with useful links (see www.watsimpel.nl/links and www.watsimpel.nl/herkennen). It is in dutch - lol but you get the general idea.

I am currently working on making those two pages easily readable on a phone/tablet.

Eventually I will make the www.watsimpel.nl/herkennen into an app for their mobile phone. It is a page with the most occuring illnesses for the elderly and how they can recognize it quickly. I link to official red-cross and hart foundation pages so they always get the most up to date information. Currently however, it is easier for the information to be on the wegpage - as I can make a link on their phone to open straight up to this page (and I do not need it integrate with the phone (yet)).

Commented:
Could I have an example Umbrella fund file?

And also, does an Umbrella fund workbook have empty worksheets?

Author

Commented:
Sure, please see an example attached.

Generally speaking, any spreadsheet which is an umbrella fund, will only have completed worksheets.  But, someone in the team might create one and accidently leave a blank worksheet in there...but generally speaking I think its safe to assume that only full/completed worksheets will be in an umbrella fund worksheet.  If its easier to program, I can make sure we remember not to leave an empty worksheet in there.

Maybe as part of the of the later error reporting questions, the macro can report how many other worksheets were not "worked on" because they did not appear to fit the rule.  That way, agian I can manually check to be 100% sure (and also delete the blank worksheet if identified)....but I'm guessing to program the macro to check a "blank worksheet" might be difficult because how would you define blank i.e. blank with absolutely zero on it or blank meaning "not relevant to the macro" but still containing some rubbish /non related info.
2012-0023-SICAV-Axa-World-Funds.xls

Commented:
That was what I was thinking too - how do you determine that the sheets are "blank" or "rubbish".

Currently it will just not display a line if a worksheet contains no information (it will however up the line number - must change that too :P). So currently we can see if a sheet has been missed... but that is more because I'm in the middle of writing this code! :D

PS the workbook you gave as example does not have the name umbrella in the title of the file name. I will add it for testing purposes. Or does the umbrella in the title appear somewhere else (as in not in the file name)?

Author

Commented:
Apologies, yes you are abolsutely right.  In that case, depending on the (mood?!) of my manager, the rule should be either if the word SICAV or Umbrella is in the name then the spreadsheet is likely to have more than one fund or worksheet in it.

Another way to identify it, is in the spreadsheet, one of the Selection Criterias has the word "Umbrella fund name" in it.  If the corresonding field has the data in it, then we know it is an umbrella fund.  Only problem with this method is that it is not until the spreadsheet is opened and analysed by the macro, will it know if it is an umbrella fund or not, rather than looking at just the file name.

has that made it a tad bit harder?

Author

Commented:
SORRY.  Ignore the first bit of my last comment.  I have just spoken to my boss and he made the mistake of not adding the word umbrella to some of the funds.  So dont use the word SICAV to search on because you can get some SICAVs which will not have more than one worksheet.

Lets stick with the word umbrella, and from my end I will make sure we rename those files, including the one I previously attached.

Sorry!  :)

PS - is it much harder to work on an error handling if we base this on the contents of the "Umbrella fund name" criteria otherwise?

Commented:
Right that works too. One point to note - I have not tested if there is actually data in a worksheet... so it takes all worksheets in the umbrella workbooks and looks for data.

(To be honest I havent actually tested it either with a blank worksheet - cause I'm a little lazy today!). :D
summary-macro-test.xls

Commented:
Your PS by the way: I'm not sure I understand what you mean. So far nothing you have asked is exactly hard - it is just that the code will take longer to run. I already notice now that I have 5 test files, it will take a second or show to display. The more checks etc you do - the longer it will take to run.

If we do checks on things we have already (like in the name of a file) it goes quicker than to check a particular cell in every worksheet.

PS a blank sheet will create a line with only a line number and Application reference field. That way you can easily see what files still have a blank page (and you can then delete that blank page).

Commented:
On an explanation of what I find "hard":

I once had a boss (I hate the word boss, but he acted like one - bossing people around, so I call him that. I prefer the word leader/mentor/coach) - who asked me what I found hard to do. I told him I found nothing hard except people. Any kind of logical task like programming or even building a spaceship is in the essence easy.

My reasoning - if you take building a spaceship and you break that down to the smallest part, having researched exactly how each single part needs to be built to the best requirements (even assuming no one has researched it before, it is possible to research it as long as funds and time permit) - I could build a spaceship by starting building the smallest parts (for example the screws) and building it from there.

That is how I code too - I get from you exactly what you would like to see. I note it down, break it down into small parts, code it, test it - done.

Initially your project is big, but breaking it down you have one liners of code. And one liners are easy ;)

Anyway, that boss of mine found me idiotic and eventually ensured that we came to a mutual agreement that I left the company. (Later I heard from his boss that it was out of fear of me, because I could do so much more than he could - especially with logic).

Which makes my only challenge to date: People :D (As they are sooooo unpredicitable, like my ex-boss).

Author

Commented:
Absolutely superb!  :)
This works 99% fine.  it is capturing the sub-funds nicely.  To be honest, ignore my "PS" above.  Some things in life are best kept simple.  I've tested it with a blank worksheet in one of the umbrella funds and all it does is it inserts a row but contains the line number (#) and application reference.

So from this worst case scenario (assuming we ever by mistake leave a blank worksheet in an umbrella fund spreadsheet), we can either delete that worksheet and rerun the report or have the macro delete the blank line after testing to see if it is blank but then the line # will not be in sequential order......so I think the easiest thing is that if we see a blank row, we just run the report again after deleting the blank or rubbish worksheet.

The ONLY problem I see with this, is what if we want to have another worksheet in the umbrella fund worksheet but have other notes/information there....but I think I have time before I approach that scenario.

Here is the new question link!
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27687669.html

Author

Commented:
Oops, I just read your last PS now....which I have then repeated in my last comment :)

Are you happy to keep extending these tasks a little further (the major stuff is out of the way)?

Commented:
I have pasted the file in the question.

I am definitely happy, just let me know what needs doing - and I will do my utmost to do it!

I will not be around from wednesday till saturday (a wee mini-break to celebrate our one year wedding anniversary). Just so you know!!

Author

Commented:
Cool.  How about a quick one.  So that I can test these spreadsheets with all the criterias, coulld you please add a "select all" tick box on the criteria selection at the top which will either add all ticks down or remove all ticks?

Also, what is the situation if I want to add more "criteria selections" on the second worksheet going forward, will the macro only look at certain number of rows or willit  look at the whole of column B of the criteria selection worksheet to see if there is a match in the underlying files?

Thx.

Commented:
I just checked my score, in another 10 questions I'll be master excel lol.
So I thank you for this fine opportunity :D

I will now look into an "select all" button.
I have made the "criteria selections" stop at 99. I can, of course, extend this if you like (currently you are using only 72).

Author

Commented:
Here are some other possible tasks that would be great to incorporate (as separate questions).

5?. In the umbrella fund template I previously posted, would it possible to also have Column A in exactly the same format and layout, displayed in Col A on the Criteria Selection worksheet?  I could do this myself as it should only be a copy and paste, but I am just wondering if this will mess up the macro because columns B,C,D might be referenced to by the macro and I will end up shifting them?  My manager would like an easy way of locating the criteria by looking at the cateogories.

6.  How about a new column which when the report is generated, outlines how many sub-funds there are when the "selection criteria" umbrella fund's name is completed in a spreadsheet i.e. this is the same as counting how many same instances of the same application reference number there, but I want to base it on that rather than the title of the spreadsheet name.  This will help me with error checking.  if we are going to create a category type (question 5), the name of this criteria can be something like Configuration

7. Location of file (again under the Configuration category) - this will be useful if I can have the actual hyperlink to the file outlined in the column so that if I see any of the generated results being incorrect or having additional blank worksheets etc, I can just open the spreadsheet directly from within here

Thanks for the heads up on date.  I cant think of much else to add as this is more than what I was expecting.....but I'll let you know.

How sweet first anniversary and a baby to go with that in the first year :)  I regret not having kids earlier during our 10 years of marriage, but we married very young (my wife was 19 and at college studying abroard at the time) and we chose to wait until she could convert her dental qualification here.....life passes by so quickly and regrets catching up so fast!  I'm used to having so many nephews and nieces around me since I was a kid so that also makes it much harder as kids are adorable.....when they're nice :)

Commented:
That took a bit longer than I first thought it would take - but the select all is now working. See the attached file.

5. As you predicted this is not an easy copy paste. As the columns will indeed shift.
But I will add this if you like.

6. So you would like a new category at the bottom of the categories called Configuration - with a criteria called "# sub-funds" or something equally exciting?

7. This one is easy to add, after 5 and 6 are done. (I will make it a clickable - blue - link as well)

And about the wedding anniversary - I had decided never to get married again - but then I met this great guy, got pregnant to him (planned) and then whilst pregnant decided that it was most fair on baby and guy that we were married - so that he would have the same rights concerning our child. Plus he is a great guy - and I wouldnt have it any other way now :D I had a rather big belly in my wedding dress - but it was a stunning day! And yes, kids are great as long as they arent being a pain lol. (Says I, who is listening to my child winge because she is tired and I have put her in bed - she believes she shouldnt be there yet - *sigh*).
summary-macro-test.xls

Commented:
Actually, before I continue with number 5 - can I just take the No. column away in the Criteria Selection sheet? I will replace that with the category - and then there would not be an issue with columns etc displacing?

Let me know! :D

Could I also have a full subset of all categories and their related Criteria?
I do not seem to have a file that has all of them.

Thanks.

Author

Commented:
>>I just checked my score, in another 10 questions I'll be master excel lol.  So I thank you for this fine opportunity :D
No, its definitely, a thank YOU.  This whole thing has helped me like I cannot explain.  I actually feel guilty asking all these questions because, even though this is a hobby for you, the value of me awarding a few points for the cost of an annual membership does not easily give credit for what would ordinarily cost someone a lot of money for their time if this was a job.  I hope you take some comfort in at least moving up the rankings and gaining a T-Shirt of some sort along with the public credibility of a higher ranking :)

>I have made the "criteria selections" stop at 99. I can, of course, extend this if you like (currently you are using only 72).
Let's take it to 200 then, just in case I want to add a lot more to it, I dont need to come back to EE in the future.  

thx.

Commented:
LOL I will up it to 200 then :D

See the attached file for a start to a nice layout in the Criteria Selection sheet.

I will do the upping to 200 now... while you decide if those Categories are correct.

And I feel so inspired to get more points! LOL
You are right - to get someone to make a sheet like this, you would be looking at many hours IT consultancy and programming time. Especially as they up the time it takes to program it by about 200%. The way I see it is - I do a good deed in one place, I'm sure it will repay some where else :D

Or you could see it as a Pay It Forward - I do something grande for you - you find someone to do something grande for as well. That way not only you benefit ;)
summary-macro-test.xls

Author

Commented:
>Actually, before I continue with number 5 - can I just take the No. column away in the Criteria Selection sheet? I will replace that with the category - and then there would not be an issue with columns etc displacing?

I suppose we could yes, but that would mean no line count of any sort right or I could refer to the excel line number even though the categories will be added right?

>>Could I also have a full subset of all categories and their related Criteria?  I do not seem to have a file that has all of them.
Sure, see the criteria selection worksheet attached.  Could we also have the category in the same blue format and horizonal slant as the umbrella fund structure?

Thx.
summary-macro-test-15.xls

Commented:
I have now upped it all to 200. Beyond that the ticks will not work.

PS have a look at the criteria page please :D
And yes - the line numbers can be taken from the excel numbering - because mainly, we do not use the numbers for anything anyway.
summary-macro-test.xls

Author

Commented:

Commented:
I'm sorry. I will slow down :D

Author

Commented:
No...dont :)

- Criteria pages looks good.
- small change - could you please make the "date of credit limit request" criteria type to "date" on your next version (as you're working on the master file)


>> Or you could see it as a Pay It Forward - I do something grande for you - you find someone to do something grande for as well. That way not only you benefit ;)

I really like this principle and it is this that has driven me for a long time.  Slightly off topic, but I remember when I was a kid, maybe 6 years old, and a rich friend of my dad's from a poor country gave me £100 as a gift (when you're normally used to receiving £5 or £10 at max for a birthday/festive season in those days and when it was a lot of money).  I still remember now that feeling, it was like £1000 to me.  I now have no problems with charity giving for such amounts and payback is always nice whether by money or time devoted to a cause and make me remember that one single day :P  I did say it was off topic!  But I agree with you.  

>>6. So you would like a new category at the bottom of the categories called Configuration - with a criteria called "# sub-funds" or something equally exciting?

Correct, just a criteria called "Number of sub-funds in worksheet" in the configuration category which counts the number of worksheets in that underlying spreadsheet.

Thx.

Commented:
Right - that is done. I have added a new CriteriaType called Configuration - as these work completely differently from the rest (as in - they get the data as a calculated field). These will not be able to be added, unless you know what you are doing in the code!

The File location is a hyperlink now too - but I hardcoded that - in other words, do not change the name of the Criteria "File location" to something else. Same with the "Number of sub-funds in worksheet" - again hard-coded.
summary-macro-test.xls

Author

Commented:
If I try and run a vlookup on the report generated with another excel file that I open within Excel, the vlookup doesn't action, instead it displays the whole vlookup query in the cell.  Any ideas why?

The last spreadsheet works great (as expected), thanks.  here is the new question link:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27688188.html

Commented:
Have you included an "=" sign before the vlookup?

Author

Commented:
yup.  I'm just wondering if its something to do with one of the settings in excel i.e. calculations are off or something.  I'm not used to 2007 compared to 2003 so not sure where I would need to look.

Also I realised now that I made the mistake of including two criteria field names, exactly the same name but for a different category (row #16 and row #23).  As a result, I think its copied the data for the first one but not for the second duplicate i.e. #23.

I will need to go back and rename some 100 or so spreadsheets where it says "Legal structure" for row #16 to "Umbrella fund legal structure".  It's quite possible that I might need to also replace names which because as you may remember I have several different fund type templates, I need to harmonise them.

Is there a quick and dirty macro that can be developed in a new worksheet called "Data maintence" which will allow me to undertake such data maintence tasks of replacing one word with another in range of spreadsheets (using either the range set on the summary worksheet or using a new range on this "data maintence worksheet"?

I will need to start harmonising a lot of different templates and by using the "Criteria selection" worksheet list, this will help me enormously.

Thanks.

Commented:
If you send me an example - I will test it here and find the bug in the vlookup for you.

Which one do you want to keep #16 or #23? Or do you not care?

If it is easier to change "Umbrella fund legal structure" you can just do that. Only the Configuration items cannot have a name change.

It is certainly very easy to create a macro to do search and replaces of text.
You will need to be careful with things like the word "fund" - so it is only advisable to change whole Criteria names - not part of them. As in - if you wanted to change "Red oranges" to "Blood oranges" do not do a search and replace for "Red" to "Blood" but do the whole string. (Unless of course every occurence of Red needs to change to Blood) - but you get my drift!

Where would you like this functionality? In a new sheet in the Summary Workbook?

Commented:
Right I have made a Data maintenance page... and added some fields and a button.
Please: MAKE A BACKUP OF YOUR EXCEL FILES, before playing with it.
LOL

Sorry for the yelling. But I cannot stress the importance of this enough!

I replaced "Fund" with "Wiesje" and it worked on all excel files in the right folder with the correct naming convention (YYYY-PPPP). It will only replace stuff in column B.

Looks great with "Wiesje" everywhere :D

PS I did replace "Wiesje" with "Fund" afterwards, as I do need good test data - but hey, it looked good for a while :D
summary-macro-test.xls

Author

Commented:
Hi

Ok just tried this at home just now, really late,  but hey it also works.

>>Which one do you want to keep #16 or #23? Or do you not care?
I need both but I will just rename #16 to "Umbrella fund legal structure" and this tasks completes that.

I'll post a new EE question in the morning (giving you a chance to post the solution).

As part of a b) for this task, is it possible to create a preview run of the data maintenance macro so that I can see the proposed changes, again in a generated report first before it makes all the final changes and then accept the changes?  

And for c) for this task, could we insert a range like the main frontsheet macro where I can choose which file range to base it on (rather than using the whole folder path) as this will give me better control of selecting a few out of the hundreds that will eventually be there

>>You will need to be careful with things like the word "fund" - so it is only advisable to change whole Criteria names - not part of them. As in - if you wanted to change "Red oranges" to "Blood oranges" do not do a search and replace for "Red" to "Blood" but do the whole string. (Unless of course every occurence of Red needs to change to Blood) - but you get my drift!

Yup, agreed and understood.  Your analagy sounds all too similar when I have to describe to my boss in not-so-technical terms :)

>>Where would you like this functionality? In a new sheet in the Summary Workbook?
As you have correctly done, thx.

>>Please: MAKE A BACKUP OF YOUR EXCEL FILES, before playing with it.
Yup, hence why this whole project is based on all my files currently in z:\dump\test_funds rather than the final output place :-))) (not that I dont trust your code in anyway!)

I'll post three new EE questions tomorrow morning.  Time to sleep...have to be at work in 6 and half hours from now!  :)

Author

Commented:
Morning,

I must have been half asleep when I wrote the last msg - I just checked something now and realised a mistake.  Could we create a function whereby if there is more than one instance of a criteria selection, I can choose either the first one or the second one that needs to be replaced?

In this example, I would like to replace the first instance of #16 with "Umbrella Fund Legal Framework" but not touch the duplicate in #23?

Thx.

Commented:
Good morning!
I have to head to volunteer work right now - so I will not be able to make the changes you requested till I get home (in about 5/6 hours - your time around 2pm).

I should be able to get all of the above done then (as they are all possible, although your request for a report before actually replacing and the one about only replacing one of the two are the hardest)! (And then I get 4 days break ;) LOL)

I'm having a good time on EE at the moment, answered another question yesterday and they found my answer one of the most comprehensive answers they've ever received since on EE (since 2006)... I feel so chuffed! :D

PS what I will probably do is make another Data Maintenance button underneath the one now, which you can chose a category and then only replace in that category - that will probably work better - and give you more freedom in your replacement needs.

PPS see the attached for a file with #16 replaced with correct text.
I will be around for another 10 minutes, if you get the chance to post the question then I will post it in there also.
summary-macro-test.xls

Author

Commented:
Well, if it inspires you, I have been using EE since it launched and when I was at university in 1996 (although I have gone through various free and premium user names during that time!) and I think you're efforts are one of the most generous experts I have come across :)

Commented:
Yes :D totally inspired now ;)

Ok... where was I!

Author

Commented:
solving the world's problems....quite literally, one programming step at a time :-)

Commented:
LOL... if only!

Author

Commented:
>>what I will probably do is make another Data Maintenance button underneath the one now, which you can chose a category and then only replace in that category - that will probably work better - and give you more freedom in your replacement needs.

Depending on how much time you have (did you say you not available from tomorrow morning?), if we can prioritise the data maintenance based on the category type first over the reporting of the results, I'm fine with that as that would be helpful for me.  The reporting is just nice to have if my boss ever does this (but I would make sure I made a backup for him first before he did it!).

Is it still realistic for this to be done today along with the range setting (part c)? :P

Commented:
LOL, I will do my utmost, sir.

I have been toying around with a way to automatically get the Category names.
(Which I've now spend 2 hours on, mainly because I just could not find an answer).

I have now found an answer - and I discover it is not compatible with versions of Excel earlier than 2007 *sigh*. You  need this to work for 2003 as well right?

I have another formula which I can try too... otherwise I'll just add them by hand (lol).

And yes - I am working on Category first. I found that more important too. And as my daughter is having some kind of growth spurt (and hurting a lot) it will most likely be the last thing I get done.

And also yes - the range part is easy... I will just do that in a minute.

Author

Commented:
>LOL, I will do my utmost, sir.
Sorry I didnt mean that to sound pushy in anyway, I've just become acustomed to your efficiency that suddently realising you are going away for a deserved break I'm pushing more tasks your way...not good!

>I have now found an answer - and I discover it is not compatible with versions of Excel earlier than 2007 *sigh*. You  need this to work for 2003 as well right?
Ideally yes, but if it means that only I do the maintenance tasks on my 2007 computer, then so be it.  I'll just ask my manager not to touch that page.  But the spreadsheet should still load and the macro on the first worksheet will still work I presume?

Thank you SO much.  I promise no more bugging you...until you're back and if you're happy to be bugged after that  :)

Commented:
Yes, everything will work - except for the Data Maintenance Page. You could just hide the Data Maintenance Page from all other viewers ;) (A VERY WISE PLAN).

LOL I had to read your last sentence a few times... I accidentally read a different word in place of bugged... and thought eh?? LOL  *tired brain*

Right I nearly have the category thing working - but I have come across one major problem... as your categories are of a different length on every page you have, it is rather difficult to find out what row belongs to what category. I will solve this issue... before I stop lol... but at least you know what I'm working on right now.

PS the range stuff (YYYY-PPPP) already works.

PPS my head is hitting a brick wall, I'm off for dinner. I will have it finished before tomorrow hits us.

Author

Commented:
>>LOL I had to read your last sentence a few times... I accidentally read a different word in place of bugged... and thought eh?? LOL  *tired brain*
I say nothing :-)

thx.   If it continues to be a problem, dont worry about it, I'll try and figure a work around....not fair for you to spend hours on this for me.

Hope you're going somewhere nice for the next few days.

Commented:
Right, I've fixed it - and... by the way... if I've started something and I run up against a wall - there is NO WAY, I'd drop it till I've solved it lol.

If I had just stopped... I think I wouldnt be able to sleep. Hahaha.

Right... because I'm done I'll just attach it here... please do test it (the category thing) - cause to be honest I only changed a few Funds to Wiesje and back again for a laugh - and it worked. I may have bugged it up some how :D

So to be clear: This has a Category data maintenance thing added AND the range choices. :D

PS I'm going somewhere they have a swimming pool for me little one to swim in - and a Zoo so my little one can see beasties. Now just hoping the weather is nice for it, as I'm staying in the Netherlands (rainy land).
summary-macro-test.xls

Author

Commented:
wow - you have truely surpassed yourself in my eyes :)
As far as I can tell and have checked, this also works.

PLease let me know if you will be online tomorrow morning and util what time so I will post 2 new EE questions for your to post the solution to twice (as there were 2 questions answered here).

If I dont hear from you, I'll wait until your return (just reply to this msg after 4-5 days) and I will post to you then.  Have a great and relaxing break with your family.

Kind regards,

Dan.

Commented:
:D I am so glad it worked well!

I'm online now, for about an hour or two (I'm being a kind wife and letting my husband sleep in lol).

Author

Commented:
Ok.....EE have locked my account until I answer some previous questions that I have neglected, so goto do that before I propose a new q!  I hate neglecting question, but sometimes people dont have the clarity of how you provide your solution which makes responding difficult sometimes....otherwise I dont neglect!

Give me 30 min or so...I will post back here.

Commented:
I'll be around - I still need to pack!

Good luck with answering those questions.

Author

Commented:
Sorry for the delay.  here is the first q for the range part
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27691049.html

I'll post another for the other part of the question once I know you are still here and posted the solution.

Commented:
Done :D

And now I'm nearly ready to go.

Have a great few days and I will let you know when I am back.

Author

Commented:
Fantastic, many thanks for all your help :)
Enjoy your get-away from EE!

Author

Commented:
I'll just add some cumulative notes here to remind myself as I start using this macro of some further minor improvements:

Report generation (summary worksheet related tasks)
- freeze frames (on both axis) after the first 3 fixed criteria types (4 if you include the # column)
- Date of credit limit request title appears in blue for some reason
- When the macro is run and it copies the "Selection criteria" from the underlying worksheets, is it possible to copy the formatting with it to the destination or is it better to somehow set the format on the destination somehow?

Commented:
Hope you had a great weekend... we had a great time away.

Tomorrow its the Queens Birthday here (dutch Queen)... rather looking forward to the markets.

But at least you know I'm back... and I can perhaps do a question or two tomorrow as well :D

And your last question above - the formatting needs to be set in the destination depending on the column. We can make new types - with set formatting as you like.
Each take a wee bit of programming but easy enough - cause I know where ;)
Just let me know what column needs what type of formatting and I'll set it up tomorrow.

Author

Commented:
Hey,

Welcome back.  Glad you had a nice break.  It's been raining here for the last 4-5 days consecutively that it has been making the news.

Well, my premium membership expires on 4th maybe 5th May, so I only have a few days left.  I will most probably take out a new subscription and hopefully expense it to my boss....let's see.  :)

I'll have to check which columns need the certain formatting, but I think the obvious ones are the number columns associated with Euros which can have commas (,) between thousands and millions i.e. 10,000 / 100,000 and 1,000,0000 etc.  I'll see later when I get to the office in more detail.

Thx.
P.S. I thought the word "wee" was only used by the Scottish.  I didnt realise the Dutch use it too?!

Commented:
LOL, I speak kiwi-English... so I guess "wee" is allowed there too! :D

I look forward to the next 4-5 days then ;)
And yes - your boss should pay... he's getting gold for naught!

Author

Commented:
Good morning,
If we can start with each of the more simple 3 tasks above, that'll be the quick wins for the moment.  I'll post each as a separate q as always.

If you haven't already started, could you please work of this attached version as I added one or two lines of notes for myself in the future, but if you have started, not to worry....

Thx.

PS Re: boss should pay - like blood out of a stone :)
summary-macro-test.xls

Commented:
LOL - if you squeeze hard enough you may just get that blood! :D

I will start this afternoon - I spent the morning at the markets... we bought lots of wooden toys for our little girl - for really really cheap 25 euros and we bought HEAPS!

Queen's Birthday markets are just fab!

Right - I will start with the 3 simple tasks above... with the file you just attached.


Because you added the commentary - the field values of the category data maintenance were not right anymore - I have changed that in the code.

Do be careful with adding new rows etc - as I hard coded which fields to use (As in B14 etc etc).

Commented:
Fixed: Date of credit limit request title appears in blue for some reason

I have ensured that every new line is first cleared of all formats. That way it will not happen again.

Now I'll work on the freeze frames bit.
summary-macro-test.xls

Commented:
The following version has a set frame added.

Those were the only two changes I could find, that were above - except for adding a Euro comma to the fields. I will add a Cateogry Type called Euro.
summary-macro-test.xls

Commented:
And here is the third sheet - comma's added to EUR Criteria at each thousand.
I have also ensured the EUR columns right align (so they look neater under each other).
summary-macro-test.xls

Author

Commented:
So is it a public holiday in Holland today because of the queen's birthday?

For a while there, I was so preoccupied in my little world, I couldn't figure out when you kept referring to markets,  you were not talking about financial markets.  I sit near a trading floor so I was trying to link the two....!  What kind of things are they selling in the markets?  Household items or cakes and sweets etc?  Any "special" cakes with "special" ingredients?  :D

Ok I will start posting new question for the above answers in the next few minutes....be ready for some points explosion :)

Commented:
Yes it is a public holiday :D - and soooo sunny that it was a perfect day.

LOL markets - they sell all sorts. Cakes and sweets - but mainly toys and clothes and kitchenware.
We bought heaps of wooden toys - a whole train set with track that covers the entire living room floor (for only 8 euros!!), a wooden swing, a larger wooden train, an old camera (my father in law collects them), baby sleeping bags (these are great, I hope for you they have them in England!!), kids DVDs, a set of wooden farm animals including the farm, some more wooden house blocks (I love wooden toys), a board game, a childs wooden desk and chair set with blackboard on one side, a baby float ring... and a few other little bits and pieces - we only spent 30 euros - but came home with heaps.

Author

Commented:
>>Because you added the commentary - the field values of the category data maintenance were not right anymore - I have changed that in the code.

I'm missing some of your msgs, I just realised I didnt read all of them!  This thread is getting a bit long now anyway, will have to move to a new one soon.

Ok, it might have been quicker for you just to move the comment, it wasnt important.  If anything is critical not for me to touch, maybe just mark it with a yellow highlight or the "Insert comment" command telling me not to touch it or amend etc and I will try and remember that.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27697581.html

Ok this q is for part 3 - commas

Thanks!

Author

Commented:
I hope they all came from renewable forests :)

Sounds lovely.  I would love to see the train set for Euros 8 if you manage to take a picture of it and post it here.  Wood has a nice warmth to it.  I just hope for your sake you dont have any matches lying around as your daugher grows up around all that wood!  :D

That seems quite a reasonable price, are they brand new?

How happy are you to continue to extend this project...actually start a new project.  I was not planning on taking it this far before but your skills have really helped me out and I can easily think of some related (but not linked to this spreadsheet work) tasks.  It will something new but excel macro related.

It will take me some time to explain, but as per the beginning of this question (doesnt it feel like such a long time ago now!?), I will do it it "bit steps" to make it easy to follow........only if you're happy to do it, I still feel guilty about this almost free resource.

Thank you again SO much.
Commented:
LOL my pleasure... and I'd be happy too!

Let's close this question though - it's getting amazingly long and tedious hahah.

Poor moderators who have to check this!!

I'm not sure on the forests though - they were all second hand... so that's a bit of recycling already :D
DSC-0324.JPG
DSC-0325.JPG

Author

Commented:
That's so sweet!!  What is your daughter's name name?  You might have mentioned it above somewhere but I dare not scroll up, it might short circuit!

I think you got a bargain there with the train set.  We're just waiting for my wife's first trimester scan in mid May and then all being well a few months before our due date in Nov, before we start some serious shopping....but we dont want to know the sex of the baby ;)

Let's continue any ongoing discussion on the last closed thread - 13.  Off to drop the family at the airport and will see if I can write a new project description at home this evening, if not tomorrow morning...whilst I still have this site access :)

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27697581.html

Thx.

Author

Commented:
urghhh....which comment do I accept as the answer?!!! lol

oh...I know :)

Author

Commented:
:-DD

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial