Importing a Journal Spreadsheet (Import Now)
Purpose: Use this document as a reference for utilizing the Spreadsheet Import tool (Import Now) to create and import a multi-line journal spreadsheet in ctcLink. This process requires a pre-configured User ID and Password to run this process.
Audience: Financial staff/supervisors.
You must have at least one of these local college managed security roles:
- ZZ GL Journal Entry
You must also set these User Preference Definitions:
If you need assistance with the above security roles or User Preference Definitions, please contact your local college supervisor or IT Admin to request role access.
Use the Spreadsheet Journal process to streamline journal importing and to simplify journal data entry using Microsoft Excel. Spreadsheet Journal Import uses a front-end interface that provides you with an easy-to-follow menu for entering data, specifying defaults, and importing journals.
After you have created your spreadsheet, the Spreadsheet Journal utility uses one of the two import methods:
- IMPORT NOW: This online import method uses XML link technology and sends data over the internet, processes the import request, and then replies to the Microsoft Excel interface. This method requires a user ID and password and directly uploads your journal(s) into ctcLink.
- WRITE TO FILE: This batch import method requires that you write the data to a file, submit and upload one or more files through the browser, and then initiate the Spreadsheet Journal Batch Import process to process the import. This method does not require a user ID/password however, there is a few additional steps.
Journal Spreadsheet Setup & Defaults
PREREQUISITES: To utilize the Journal Upload process the following files must be present on your workstation (For Microsoft Excel 2007 and subsequent versions) and be saved to the same folder on your computer. Please see QRG Journal Spreadsheet Template for step-by-step directions how to download the files to your workstation and refer to QRG Customizing Journal Import Template for help with setting up user defaults.
- JRNL1_WS.xlsm – This is the journal workbook that you use to create and import journals.
- JRNLMCRO_WS.xlam – This is a Macro; the Visual Basic code library and dialog control.
- GLLOG.XLT - This is a Macro; the Visual Basic code log control.
Importing a Journal Spreadsheet
Note: Use the JRNL1_WS.xlsm file to enter your journals. You must set up Microsoft Excel to accept macros, by navigating to File > Options > Trust Center > Trust Center Settings > Macro Settings and selecting either Disable all macros with notification, or Enable all macros.
- Select the New Sheet button on the Spreadsheet Journal Import control page to access the New Journal Header page.
- Enter an appropriate name for the journal entry spreadsheet you want to create in the Journal Sheet Name field.
- Select the OK button.
- A new Journal Entry Sheet displays. In the Journal Header section, select the plus [+] icon to open and edit the new journal header.
- Update the criteria as needed, i.e. add a Description.
- Select the OK button to close out the New Journal Header dialog box and general a Sys ID or Journal ID number.
- In the Journal Lines section, select the plus [+] icon to manually add a line(s). Enter field values to complete a valid ChartString for each line(s);
- Non-Grant/Project Criteria: Business Unit, Amount, Account, Oper Unit, Fund, Approp Ind, Class, Dept ID, State Pur. Optional: Program.
- Grant/Project Criteria: Business Unit, Amount, Account, Oper Unit, Fund, Approp Ind, Class, Dept ID, State Pur, PCBU, Project, Activity, Analysis.
PRO-TIP: Use the locally defined 'Reference' and/or 'Description' fields to enter in your comments about the specific line item or the entire journal description.
- To add multiple lines, select the paper with a plus [+] icon in the Journal Lines section to add multiple lines to the spreadsheet journal. The Insert Multiple Lines box will open. Enter the number of lines to add and select the Insert button. Optional to select checkbox to 'Copy data from line' and enter the line number in the box.
- The lines will be added below. Copy and paste values/data into appropriate added columns fields or use the copy down function. Delete any unused lines by using the Journal Lines 'X' icon or to delete multiple lines, select the paper with the 'X' icon.
For help with valid ChartStrings, please see QRGs Common Combo Edit Rule Errors and Common Budget Exceptions and Corrective Actions.
- After your ChartStrings are cash-balanced and your journal is complete and ready to import data, select the Import Now (table with arrow) icon located to the left of the Journal Home icon to access the 'Import Journals Now' dialog box.
- Select the spreadsheet file for upload in the Select Sheet field.
- Enter your password in the Password field.
- Select OK to import the journal.
OR from the Spreadsheet Journal Import control page, select the 'Import Now' button to select sheet(s) to import. Select the OK button to run the process.
- If the import is successful you will receive the following system message that lists the Journal ID, i.e. 0000529232. Select the OK button to close the window.
If the import was not successful, the system Import Log will provide a message(s) with a brief description of the error. Correct the sheet data and re-import.
If successful, the next step would be to review/edit the Journal ID (i.e. 0000529232) and submit for approval (or post journal). Please refer to QRG Viewing and Updating Journal Entries.
Video Tutorial
The video below demonstrates the process actions described in steps listed above. There is no audio included with this video. Select the play button to start the video.
Video Tutorial via Panopto
View the link to Importing a Journal Spreadsheet (Import Now). This link will open in a new tab/window.
0 Comments
Add your comment