Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

Excel 2010 VB lookup question

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
Rrave26
Asked:
Rrave26
  • 3
  • 2
  • 2
  • +1
1 Solution
 
MacroShadowCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Rrave26Author Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rob HensonIT & Database AssistantCommented:
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
 
ProfessorJimJamCommented:
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
 
Rrave26Author Commented:
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
 
MacroShadowCommented:
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
 
Rrave26Author Commented:
Got the addin macro to work perfectly for the managers.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now