Solved

Excel 2010 VB lookup question

Posted on 2014-09-15
8
121 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
  • 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 32

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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 32

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 25

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Repeating shading every other row for 160 rows 2 25
Excel User Form VBA Help 18 30
Excel Cell Total 3 20
Excel Drop Down List 13 30
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

785 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