Question forCustomer Hi James, You may recall assisting me with an .xls workbook in a previous thread which was a VBA based query resolved on the 14th January 2014. Hopefully, you can help with another challenge. Since our last contact the workbook has evolved with a series of charts and increased functionality. However, one issue which has arisen is having a user form which can pull data from two or more different sources. In this instance a colleague may require data from the 15th February 2014 to the 7th March 2014 for example. Therefore, pulling data from: Workbook one: 15th February 2014 to the 28th February 2014 Workbook two: 1st March 2014 to the 7th March 2014 The concept is this will be pulled into a separate summary or master sheet. The initial user form had a start date and end date which represented 1 to 31 being the maximum number of calendar days. Therefore, I would require a drop down for example which links to the file path of February or March 2014 possibly using aspects of the existing code named ‘Change Calc’. I can e-mail the current template if this would be useful. Any help, would be most appreciated. Best Regards Rik
I would be fine, either way. However, I just felt it would be easier for James to address since he had a understanding of the initial issue and the VBA code in Excel 2003.
I could attach the current version and provide more detail with the hope of providing a resolution.
Let me know your thoughts.
I have inserted the .xls workbook. I have also removed the passwords, on the workbook and the VBA code, then unprotected the formula/structure.
In Module 7 there is code named 'ChangeCalc' which extracts data from any combination of sheets 1 to 31 which represents the calendar days of the month into a hidden sheet named 'Monthly Summary'.
I can confirm this is linked to the userform in the sheet named Colleague Performance named 'Day/Weekly View' which transfers offset data into cells C4:N18, G20:J20 and Q11/Q13.
My question is whether I could apply a userform in a similar format with a drop down in a master sheet, which links to a filepath and could pull data from different dates within two different workbooks which represents different months.
Any help, would be most appreciated.
The user form is accessed by pressing the macro button 'Day/Weekly View' as a pop up. This provides an option for the Start Date, End Date and Analyse.
These dates relate to the individual worksheets where for example sheet 1 is the 1st of the month and sheet 7 is the 7th of the month.
I have a master sheet which transposes data as an array (since I can not use the offset formula on a closed workbook) through two workbooks one for February 2014 and one for March 2014 which is the combination of data in the Colleague Performance sheet.
However, this is stand alone data and not dynamic in the sense a colleague may require data from for example the 24th February 2014 to the 15th March 2014.
Therefore, I was seeking to establish whether this is feasible. I could provide the master sheet if this would be helpful.
I can confirm colleagues would typically search for less than 31 days, albeit due to a recent change in reporting periods they do have a habit crossing over months which has prompted this request [pulling from two workbooks].
In the future a more significant range maybe requested for example two months. However, at least in the short term this is what I would be looking for.
Any assistance, would be most appreciated.
Sure, no problem. I can wait a few days.
In this instance the completed files are saved in the same file path and the .xls workbooks linking together in the same folder at the moment, named in a consistent format with the only difference being the month and year. So this part is fine.
I can confirm the Userform 4 'Complex/Multi month and code in module 10 appears to be on the correct lines, so the progress made appears to be positive. In relation to the time, I was asked to provide a solution by Wednesday morning 9th April 2014 and appreciate some of the more complex requests take time, so this is no problem.
Hi Rik, OK I think I have a solution for you and will post the file here.
I have tested for within 1 month, multiple months in the same year and for multiple months across multiple years. All seem to work to me but I only have copies of the 1 months data that I have re-named to form the old months.
Please take a look and let me know if this is OK as an answer for you.
I was having a few challenges testing this due to a 'Error 429', ActiveX component can't create object. However, I have been able to use another laptop where it will function.
Just to confirm, to ensure the code works effectively do I need to make any adjustment's apart from the file name itself '2014-03-30_193916_template_april_2014.xls' which will change within the sub routine GetTabs?
Did you get it working?
Re the Error 429 I did load some older MS tools when I was looking at calendar forms but did not use them so should not have had an impact. I have un-installed and re-saved the .xls to see if it was that and if this clears it.
Is there anything else you are looking for on this? If you can let me know so I can work or we can close that would be great.
Best regards James
The error message which appears is unable to find file path (Run-time error 1004) prompting me to de-bug the code related to Module 10. The highlighted line is below:
Workbooks.Open Filename:=myPath & mthFile
I can confirm I renamed the previous workbook to be pulled as the yyyy-mm.xls format, which is sat in the same folder. However, the error remained.
In this instance I keyed the following details in userform 4:
From: 2014-03 (free format), second box drop down: 22
To: 2014-04 (free format), second box drop down: 5
There was no option to select a specific format in the From or To combo drop down boxes, which is why I keyed free format. However, cell AO in the Lookup Tables did recognise the dates keyed 2014-03-22 to 2014-04-05.
I'm currently using Microsoft 2003. I will wait for your response on Thursday.
I apologise for the delay responding. I was away over the Easter Break.
I can confirm I access the main file by double clicking on the folder and subsequently the file in question. In relation to the version I used, this is the most recent file provided on the 12th April, with March's data displaying information with the same cell alignment.
I have attached the screen capture of the error message which reflects the specific file path and the file names used to see if there is any stand out issues.
I now see the problem and sorry if I was not clear. The filenames for the old months can only have yyyy-mm.xls and no other characters in them. If you need to have more detail in the filename it would require a further adjustment to the code and I would have to do quite a bit more work depending on what else you needed to be able to have in the name.
Are you able to work with just the date for the filename? as I have in attached screenshot
This would be perfectly fine if I was the only user. However, since multiple teams will use templates of this .xls document I would probably need unique text.
In this instance the previous month was named 2014-03 Defender March 2014. There would be no requirement for March 2014, since this is effectively duplicating 2014-03 in a different format. However, the words 'Defender' for example would have value.
Is this feasible, where the text characters could extend to 20 with consideration of spaces, albeit I could underline _ to represent a space.
Please find updated version which should now cope with any filename so long as it starts with yyyy-mm.
I hope this completes the solution for you.
I have provided positive feedback since it is clear core aspects of the code work. However, in this instance I have come across another Run-time error '91 within the private sub routine CalcMth_Click. This refers to the line of code 'toMthFile = rng.Value'.
I have attached the screen capture above.
Apologies, I think I have found the error. I had some If Then statements and have now changed to If Then ElseIf as some sections were being run twice and causing problems. I have also adjusted the code so that it will created the Named ranges so this should not be an issue now either and might correct the earlier run time error.