Solved

Excel 2010 VB lookup question

Posted on 2014-09-15
8
127 Views
Last Modified: 2014-09-21
I want to create a macro that will be distributed to several people who will need to save it locally and then run it when a report is sent to them.  Can this be done, and if so how do I go about setting that up i.e., do I create a new workbook and save the macro in that, do I save in a personal macro workbook, or does it even matter?
0
Comment
Question by:Rrave26
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40322987
The best option is to save the macro in each users Personal Macro Workbook.
The Personal Macro Workbook is located in: "C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART"
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40322996
It will have to be in a separate workbook, not the Personal macro workbook. Saving in the personal macro workbook puts it into your personal workbook, if you then send this to other people it will overwrite their personal workbook unless you tell users to copy and paste from the distributed workbook into their personal workbook.

Could the macro not be embedded into the report workbook?

A bit more explanation of the situation/scenario might be useful.

Thanks
Rob H
0
 

Author Comment

by:Rrave26
ID: 40323080
Thanks, for the responses but now I am more confused.  LOL.  This report is sent everyday as csv file to 3 managers who need to audit the report.  However, the report needs to be formatted for the managers to use, i.e., column titles put in, columns formatted, one column needs to be seperated into three columns (EE id, Last name, First name).  So I am working on writting a script that does this all for them so in the end when they open the report all they need to do is hit a macro shortcut that does this formatting for them, which will allow them to then filter the report by last name so they can audit their people.  So I want them to have the macro installed locally so they can run it each time the report is sent to them.

Hoefully that helps.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 40323370
What level of Manager are we talking about?

Are they going to want to be doing this process or will it be beneath them and they will expect the work to have already been done for them?

Does the CSV file go to any lower level that could process and then distribute the processed file(s) to the Managers?

If the CSV goes direct to the Managers, how about setting up a report file that they can just copy and paste the CSV data into. That report will then format or split the data as required.

For Example, in Report file have two tabs, Data & Report
Data
Copy and paste in from CSV file after opening

Report
Column Titles - can be pre-entered as hard coded values, no entry from Manager
Column Formats - preset, no entry from Manager
Column Values - linked direct to relevant columns on Data sheet
Column Values that need splitting - linked with text formulas (LEFT, RIGHT, MID etc) to data sheet

Actions required by Managers - Open CSV and Report files, copy and paste from CSV into Data tab of Report file. Check number of rows is sufficient for all data on Data tab; copy formulas down if required.

Does that sound like a suitable process?

Thanks
Rob H
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40323517
just like Mr. Henson mentioned , i see only the option that can do this, is  sending one time a excel file to be opened by users and then it will save the macro file in the "C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART"  but it has a risk that would overwrite the existing personal file. if you are sure that the users do not have personal excel file in thier start up folder then i can give you the code to deploy your macro in all users machines.
0
 

Accepted Solution

by:
Rrave26 earned 0 total points
ID: 40323684
We are talking a first level manager and they will be doing the work.  The directior wants one csv file and the managers will be auditing their direct reports daily.  They can overwrite the file during each audit as they are only concnerned with the new data.
These users are not excel wizards so the director wants this automated.  What if I was to do an macro and save it as a addin?
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40323688
I don't see why not to sent processed reports (regardless of the manager level), it would seem like the most logical thing to do.
As far as saving a macro in each users Personal Macro Workbook, aside from the hassle there is no problem doing it as you can see in this MS knowledge base article.
0
 

Author Closing Comment

by:Rrave26
ID: 40335085
Got the addin macro to work perfectly for the managers.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question