User:Rewood/Excel setup for PeopleSoft

From WolfTech
Jump to navigation Jump to search

Installing the Excel setup_daily Macro for PeopleSoft QueriesMacro PS_FIN.mcr for setting up the following queries:

  • cals_actuals_by_fas
  • cals_actuals_by_ouc
  • cals_actuals_by_project
  • cals_actuals_by_bctf

The personal.xls is used to store macros for Excel. If you have not already created a personal.xls spreadsheet to store your own macros, then go directly to step 4.

1. Copy this query to your PC by either "shift click" on the macro named above and save it to your PC or just a single click to save it to your PC. Which method you use is dependent upon you. (Make note of where on your computer the file is being stored for later use.)

2. Open a spreadsheet in MS Excel called personal.xls. It should be hidden at all times but you'll need to unhide it in order to add the macro to it. To unhide personal.xls, off of the Menu Bar select "Window, Unhide". Note that the Title Bar now shows "Personal" next to the Microsoft Excel name.

3. Update personal.xls on your system.

  • 1. Off of the Excel Menu Bar, select "Tools, macro, Visual Basic Editor" (The editor will be displayed)
  • 2. On the Editor's Menu Bar, select "File, Import File"
  • 3. Browse your computer and find the file ps_fin.mcr, highlight it and click open (Note, you have to change "Files of Type" option to "All files" in order to display mcr files.)
  • 4. On the editor's Menu Bar, select "File, Save personal.xls" 5. Close the Visual Basic Editor by selecting "File, Close and return to Excel"off of the Editor's Menu Bar.

4. Copy PERSONAL.XLS to Program FilesMicrosoft OfficeOfficeXLSTART directory. Depending on how your computer is setup, this could be found on the "C:" drive, the "D:" drive or another drive.

Congrats! You have now created a macro named setup_daily in your personal workbook for use with any of the above CALS PeopleSoft queries. Once you have used Excel to run one of the CALS queries, you can use this Macro to sort and query the worksheet. For information on how to use this macro, see PS-005: Using the setup_daily macro with PeopleSoft queries