Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel 2010 VB lookup question

Posted on 2014-09-15
8
Medium Priority
?
138 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 27

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

670 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