Initial Setup: Troubleshooting General Ledger Spreadsheet Uploader
Purpose: The purpose of this article is to assist users with the initial setup of the new General Ledger Spreadsheet Uploader that was implemented on November 6, 2023. This article goes through the common issues and how to troubleshoot them. Below are the topics listed:
- System Security Limitations
- Delete Old Uploader Files and Download New Files
-
Two Macro Issues
- Additional Unblock of Macro Security
- Visual Basic: JRNLMCRO Reference
- Username/Password Credentials Frequently Change
- Do Not Force Input Values
- Other Import Issues
A. System Security Limitations
If there are system security limitations when downloading the files (i.e. virus scanner renames file and/or file extension), please reach out to your Department’s IT Team to address file download issue.
Example 1: File extensions and type overwritten
Example 2: Filename and file extension overwritten
Refer to “Section B. Delete Old Uploader Files and Download New Files” for correct file name and file extensions.
Common Issue: Due to this system security limitation, this prevents users from using the file due to the main template (JRNL1_WS.xlsm) not able to read the macros.
B. Delete Old Uploader Files and Download New Files
Step 1. Delete all and any of the old uploader files from your computer.
Step 2. Download all four files from the job aid “Journal Spreadsheet Entry and Uploader - November 2023” and all four files must be in the same folder.
Step 3. Make sure all four files have the correct filename and file extension/type.
Step 4. If there are any issues, refer to additional troubleshooting steps below.
Journal Spreadsheet Entry and Uploader - November 2023
(Topic 1, Step 1)
1. JRNL1_WS.xlsm
2. JRNLMCRO_WS.xlam
3. GLLOG.xlt
4. JrnlLog.xlsx
Screenshot of Correct Files (with filename and extensions)
List of common issues if new files are not downloaded:
a. You may see “Import 0 Journals” on the message log (Jrnllog.xlsx), however the journal has successfully imported to PeopleSoft and there exists a journal entry in PeopleSoft. For this, double check directly in PeopleSoft for your journal entries.
i. If it is not in PeopleSoft and the Journal ID is not referenced on the Uploader, then you may have invalid or issues with your inputs.
ii. If it is in PeopleSoft and the Journal ID is not referenced on the Uploader, you need to follow steps from above for this “Section B. Delete Old Uploader Files and Download New Files.”
b. Variety of possible macro error messages upon usage of the template (JRNL1_WS.xlsm), which prevents users from using the template to import journal entries. This could also be one of the Macro Issues below.
c. On the Journal Header section, Source will not be viewable.
d. The Sync Template window prompts do not show when first using the template.
C. Two Macro Issues
C1) Additional Macro Security Unblock
Step 1. Additional Macro Unblock as described in Microsoft Support
This will need to be repeated for all the 4 files in the following order:
Step 2. Right click on JRNL1_WS.xlsm, go to Properties, checkbox Unblock in security under the General tab. Press Apply and OK.
Step 3. Right click on JrnlLog.xlsx, go to Properties, checkbox Unblock in security under the General tab. Press Apply and OK.
Step 4. Right click on JRNLMCRO_WS.xlam, go to Properties, checkbox Unblock in security under the General tab. Press Apply and OK.
Step 5. Right click on GLLOG.xlt, go to Properties, checkbox Unblock in security under the General tab. Press Apply and OK.
Step 6. Open JRNL1_WS.xlsm. When prompted, click the button “Enable Macro.”
a. If it doesn’t work, refer to Macro Issue C2) below. If assistance is needed for these steps, please open a FreshService ticket.
C2) Visual Basic Jrnlmcro Reference
* Note: We do not recommend users tampering with the macros in the GL Spreadsheet Uploader, however this is one exception to troubleshoot initial setup of the Uploader. If you require assistance with these steps, please open a FreshService ticket to User Support.
Step 1. Ensure that the JRNL1_WS.xlsm is pointing to the correct macro file. The following steps describe how to verify the location of the JRNLMCRO_WS.xlam being used by JRNL1_WS.xlsm.
Step 2. Open JRNL1_WS template. Make sure you're not in debugger mode. If you are, close the debugger window.
Step 3. On the keyboard, press Alt + F11 to open Visual Basic.
Step 4. Highlight ‘JournalBook (JRNL1_WS.xlsm) from left panel and then go to the menu bar to navigate to Tools > References…
Step 5. The References – Journal Book window will pop up. Find and highlight ‘Jrnlmcro’ and verify Location.
If the file location is long, hover the mouse cursor on it to see the full navigation. Note, you may need to take a screenshot to refer back to the navigation, if there’s many folder paths.
Step 6. The next steps are describing how to change the Journal Macro location.
a. Following the navigation in Step 5 above, delete the file. The idea is for the system not to find the old file.
b. Fully close the JRNL1_WS.xlsm template.
c. Reopen JRNL1_WS.xlsm and follow steps C2) Step 3 – 4 to verify the JRNLMCRO location.
d. When you highlight ‘Jrnlmcro’, verify the file location is now showing in the correct directory (the same place as where the JRNL1_WS template is open from)
e. If you see ‘MISSING jrnlmcro’, use the Browse button and find the correct location, click OK, and Save.
f. Retest uploading a journal.
Step 7. If the steps for C2) and or C1) does not work, please open a FreshService ticket to User Support, provide the GL Spreadsheet Uploader files you’re attempting to use, and the troubleshooting steps taken.
D. Username/Password Credentials Frequently Changed
Common issue that occurs with changing passwords frequently is that the template says login credentials are not valid, but the same credentials can be used when logging into PeopleSoft.
a. If you have recently changed your password, your password may not be in sync with the Active Directory. The Active Directory Password needs to be in sync with the IAM password. Users, please reach out to your internal IT staff that supports Active Directory and request advice on username/password changes. Inform them you have changed your desktop login. Once confirmed your user password is in sync, try re-importing your GL Journal Entry.
E. Do Not Force Input Values
Step 1. When navigating through the GL Spreadsheet Uploader, make sure you are using the button icons on both the homepage and journal sheet. Users should not force input values to the uploader tool. Refer to screenshots below, for example. If their template journal sheet looks like this, it’s likely they may have unprotected the sheet and/or pasted the values directly.
Incorrect Input of Values:
- Sys ID is blank
- Journal ID is blank and doesn’t reference NEXT from the Journal Header section
- Journal Lines background is white and not gray
- Formatting doesn’t show (i.e. Negative values are not red)
Correct Input of Values:
Step 2. If you are attempting to force input values to the uploader, then you should see this warning message: “The cell or chart you’re trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password.”
a. Although it says to unprotect sheet, users should not tamper with the settings of the template(s).
b. If you have unprotected the sheet, please download a new copy. Refer to "Section B. Delete Old Uploader Files and Download New Files."
Step 3. When inputting data to the Journal Header of the sheet, click the 1st icon [+] to add the Journal Header details to the template
Step 4. When inputting data to the Journal Lines, click the 1st icon [+] or 4th icon to add Journal Lines to the template
Below are common issues when values are force input to the journal sheet.
a. Variety of possible macro error messages upon usage of the template (JRNL1_WS.xlsm), which prevents users from using the template to import journal entries. This could also be one of the Macro Issues below.
b. Journal sheet becomes corrupted and a new sheet has to be created or new template files need to be downloaded.
F. Other Import Issues – (Not Linked to Initial Setup)
Step 1. Server may be down. Internet connection issue or VPN issue. Check with IT Support
Step 2. Message Log errors associated with ChartField issues. Refer user to information about Chartfields. Verify CF are valid or open.
G. Create a FreshService Ticket
If after going through the scenarios documented above and there's there's still setup issues for the user with the GL Spreadsheet Uploader, please open a FreshService ticket.
User should have the following roles to use the GL Spreadsheet Uploader:
• FSP_GL_CITY_JOURNAL_PROCESSOR
• FSP_GL_CITY_UPLOAD_PROCESSOR
Audience: User Support
Author: Sheila Saromo
Version: Version 2.5