How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask JamesFGriff Your Own Question

JamesFGriff
JamesFGriff, Other
Category: Microsoft Office
Satisfied Customers: 2
Experience:  ~30 yrs in product mgmt/mktg with leading OEMs. Proficient with HW & SW.
76324600
Type Your Microsoft Office Question Here...
JamesFGriff is online now

Question forCustomer

Customer Question

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

Submitted: 3 years ago.
Category: Microsoft Office
Expert:  Brandon M. replied 3 years ago.
Hello, Rik. My name is XXXXX XXXXX I would be happy to assist you. Do you mind working with me or would you rather wait for James?

Best regards,
Brandon
Customer: replied 3 years ago.

Hi Brandon,


 


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.


 


Thanks.


 


Rik


 


 

Expert:  Brandon M. replied 3 years ago.
Rik,

It's entirely up to you. I would be happy to take a look but if you would rather wait for James, I have no problems with that. Just saw this question sitting for awhile and thought I would offer my assistance.

If you would like, I can still take a look at it and see if I can easily provide a solution or if it would be better to wait for James.

Best regards,
Brandon
Customer: replied 3 years ago.

Attachment: 2014-03-30_193916_template_april_2014.xls

 

Hi Brandon,

 

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.

 

Rik

Expert:  Brandon M. replied 2 years ago.
Looking at the code, I don't see any userforms - Colleague Performance appears to just be a sheet.

Anyway, what file paths would be in the drop down? And what are the names of the actual worksheets in those directories?

Brandon
Customer: replied 2 years ago.

Hi Brandon,


 


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.


 


Thanks


Rik

Expert:  Brandon M. replied 2 years ago.
Rik,

I think this might be a little over my head. I think it would be best to have James take a look at this instead since he already has a familiarization with it.

I will opt out and send him a message.

Best regards,
Brandon
Expert:  JamesFGriff replied 2 years ago.
Hi Rik, sorry for the slow response and thanks Brandon for prompting me to check in. Let me take a look and see if I can help again.

Question. What is the maximum number of days that could be asked for? If 31 or less then it might be possible to use a duplicate of the current sheet and copy into it the relevant sheets from the other files to make them as tabs 1 to x and then use the exiting methodology to report on them.
Customer: replied 2 years ago.

Hi James,


 


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.


 


Best Regards


Rik

Expert:  JamesFGriff replied 2 years ago.
OK, I will take a look. It might take a few days so I hope that is OK.
Customer: replied 2 years ago.

Hi James,


 


Sure, no problem. I can wait a few days.


 


Thanks.


 


Rik

Expert:  JamesFGriff replied 2 years ago.

Hi Rik,

 

Attachment: 2014-04-04_154053_2014-03-30_193916_template_april_2014.xls

So, not done full testing and also possibly not the most elegant solution but have a method that I think works so wanted to check with you if it is OK. It depends upon the completed month data files being in the same folder as the current month file and named in yyyy-mm.xls format. Added extra button (Complex/multi month) to launch new form to process the request and also more code in module 10. Please can you take a look and see if you think this is progressing on the right lines? I can then do some more testing and clean up some of the screen flicking back and forth etc. PS it can take a little while to complete some of the requests.

 

Regards James

Customer: replied 2 years ago.

Hi James,



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.



Thanks


Rik


 


Expert:  JamesFGriff replied 2 years ago.

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.

 

Best wishes

Attachment: 2014-04-07_230953_2014-03-30_193916_template_april_2014.xls

James

Customer: replied 2 years ago.

Hi James,


 


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?



Thanks


Rik



Expert:  JamesFGriff replied 2 years ago.
Hi Rik,

No, should not need to make any changes other than that and to rename the old months into the yyyy-mm.xls format. The current file will get ignored by the lookup code and just the completed ones be used for the complex query.

Regards
James
Expert:  JamesFGriff replied 2 years ago.

Attachment: 2014-04-12_110451_2014-03-30_193916_template_april_2014.xls

Hi Rik,

 

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

Customer: replied 2 years ago.

Hi 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.


 


Any help, would be most appreciated.


 


Rik

Expert:  JamesFGriff replied 2 years ago.
Hi Rik.

I'm away on holiday a the moment but will look when I am back Thursday. Which version of excel are you using?

James
Customer: replied 2 years ago.

Hi James,


 


I'm currently using Microsoft 2003. I will wait for your response on Thursday.


 


Thanks.

Expert:  JamesFGriff replied 2 years ago.
Hi Rik,

So I have now installed Excel 2003 onto another PC and tried the file there. It works without giving the error you see.

Do you launch the main file by double clicking in the folder it is in or another way?

If the problem keeps happening are you able to share the version of the files you are using (Dropbox folder perhaps) so I can see if there is something else going on?

Regards
James
Customer: replied 2 years ago.

Hi James,


 


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.


 


Attachment: 2014-04-21_113100_user_form_error_message.doc

Expert:  JamesFGriff replied 2 years ago.

Hi Rik,

 

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

 

Regards James

Customer: replied 2 years ago.

Hi James,


 


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.


Expert:  JamesFGriff replied 2 years ago.

Attachment: 2014-04-22_222106_2014-03-30_193916_template_april_2014.xls

Hi Rik,

 

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.

 

Regards James

JamesFGriff, Other
Category: Microsoft Office
Satisfied Customers: 2
Experience: ~30 yrs in product mgmt/mktg with leading OEMs. Proficient with HW & SW.
JamesFGriff and 2 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.

Hi James,


 


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.


 


Attachment: 2014-04-24_195147_error_message.doc


 


 

Expert:  JamesFGriff replied 2 years ago.
Hi Rik,

Many thanks fore positive feedback it is much appreciated.

On the runtime error I am just wondering if it is related to some defined name ranges I use on the "Look up Tables" sheet. I realise that I have not specifically told you I created them. Do you copy the file I send back? If not then it might explain the errors.

I will have a look at the code and see if I can create the named ranges there.

Regards
James
Expert:  JamesFGriff replied 2 years ago.

Hi Rik,

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.

 

Regards

James

Attachment: 2014-04-26_182830_2014-03-30_193916_template_april_2014.xls

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
< Previous | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    23
    A+ Comptia Certified computer repair
< Previous | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    23
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    241
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/JA/JACUSTOMERf8udkdxk/2013-8-3_15150_323738101505074393259301621172992o.64x64.jpg KamilAnwar1's Avatar

    KamilAnwar1

    Office Specialist

    Satisfied Customers:

    120
    8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    111
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/WI/Windowspcfix/2013-8-19_153826_ja12.64x64.jpg James K.'s Avatar

    James K.

    Consultant

    Satisfied Customers:

    110
    Technical Director of IT Company
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    56
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions