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?
Rrave26Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 HensonFinance AnalystCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Rob HensonFinance AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.