Spreadsheet Upload Installation Instructions

The Journal Spreadsheet Upload files are used to enter large amounts of journal information into OAKS FIN using a pre-defined spreadsheet template rather than manually entering journals online. Two separate Excel spreadsheets are used, one for uploading GL Journals and the other for Budget Journals. This topic illustrates how to copy and store the files needed to allow this process to work properly.

The Excel macros and XLSM files needed to create the budget journal file and GL journal file can be found on the OBM website under Forms/Spreadsheet Journal Upload Files at obm.ohio.gov. The Excel files contain a macro that the user will use to create, edit and save a journal, and the XLAM file is used to run the Excel macro.  Both of these files will be stored together within the same directory. For example, the Excel macro and XLSM file for the Budget Journal Spreadsheet Upload will be stored together in the same sub-folder and the Excel macro and the XLSM file for the GL Journal Spreadsheet Upload will be in their own sub-folder.

• Agency users must have the security authorization to install files on their computer. If an error message displays, the user should contact their agency's IT department for assistance. If an agency’s IT department needs further assistance, they need to contact OBM’s IT department at OBM.CustomerSupport@obm.ohio.gov

• The steps and screenshots shown below were created using Windows 11 as the operating system, Microsoft Office 365 software and Chrome as the browser. Because there are a variety of options on users computers, not everyone will match the screenshots shown. However, the process remains basically the same.

Delete Old Spreadsheet and Macro Files

Steps

Before obtaining the new spreadsheet files, the user should delete the existing spreadsheet files and journal macros that are saved to their computer.

1. Right-click the folderfolder containing the spreadsheet files.

   

2. Click deletedelete.

   

3. Click YesYes to confirm delete action.

   

4. Open any Excel spreadsheet.
   • The document will be used only for managing the add-ins for Excel.

5. Click FileFile.

   

6. Click MoreMore.

   

7. Click OptionsOptions.

   

• The Excel Options page displays.

7. Click on Add-InsAdd-Ins from the list displayed on the left of the page.

   

• The Add-Ins information displays in the Excel Options window.

8. Ensure that default "Excel Add-ins" is displayed in the ManageManage drop-down box.

   

9. Click  GoGo.

   

10. Remove the mark in the checkboxcheckbox next to JRNLMCRO_WS.

    

11. Click OKOK.

    

• The old journal folder and the journal macro have been removed.

Create a Primary Folder with Two Sub-Folders

Steps

1. Determine where the new files will be saved.
2. Click on the computer desktop or local drivecomputer desktop or local drive where the file will be stored.

   

1. Best practice: Use the documents folder on the PC.

3. Name the new folder "Journal Upload."

• Follow agency guidelines if there are agency folder naming conventions.

4. Double-click on the Journal UploadJournal Upload folder just created to open it.
   • Two additional folders will be created within this folder.

   

5. On the Home tab, click New FolderNew Folder.

   

6. NameName this new sub-folder "Budget Journal Spreadsheet Upload."
   • Within the new sub-folder "Budget Journal Spreadsheet Upload", create a new sub-folder.
   • Name this new sub-folder "Macro."

   

7. Go back to the Journal Upload folder level and click New FolderNew Folder  again.

   

8. NameName this new sub-folder "GL Journal Spreadsheet Upload."
   • Within the new sub-folder "GL Journal Spreadsheet Upload", create a new sub-folder.
   • Name this new sub-folder "Macro."

   

Download New Spreadsheet and Macro Files

Agencies must download two separate files from the OBM website for each process:

• Budget Journal Uploads require:

Budget Journal Spreadsheet Upload (BudgetJournalUpload.xlsm) AND

Budget Journal Spreadsheet Upload XLAM File (Jrnlmcro2.xlam)

• GL Journal Uploads require:

GL Journal Spreadsheet Upload (GL_Journal_Spreadsheet_Upload_JRNL2_WS.xlsm) AND

GL Journal Spreadsheet Upload XLAM File (JRNLMCRO_WS.xlam)

Download the Budget Journal Spreadsheet XLAM File

Steps

1. Click on the Spreadsheet Journal Upload Files link under OBM Forms on the OBM Website.
2. Click on the XLAM file link XLAM file link for the "Budget Journal Spreadsheet Upload" Excel Macro file description.

   

3. Click Save asSave as.

   

4. Click Enable MacrosEnable Macros if a security warning appears.

   

5. BrowseBrowse to the location where the Journal Upload folder is stored.

   

6. Double-click on the Journal UploadJournal Upload folder.

   

7. Double-click on the sub-folder Budget Journal Spreadsheet UploadBudget Journal Spreadsheet Upload.

   

8. Double-click on the sub-folder MacroMacro.

   

9. Click SaveSave.

   

10. CloseClose the window.

    

• The JRNLMCRO2.xlam download is complete.

Downloading the XLSM Budget Journal Spreadsheet

Steps

1. Click on the "Budget Journal Spreadsheet Upload" XLSMXLSM link for the spreadsheet template.

   

2. Click Save asSave as.

   

3. BrowseBrowse to the location where the Journal Upload folder is stored.

   

4. Double-click on the Journal UploadJournal Upload folder.

   

5. Double-click on the sub-folder Budget Journal Spreadsheet UploadBudget Journal Spreadsheet Upload.

   

6. Click SaveSave.

   

7. CloseClose the window.

   

• The BudgetJournalUpload.xlsm download is complete.

Installing the Macro

Steps

1. Open the Budget Journal Spreadsheet UploadBudget Journal Spreadsheet Upload sub-folder.

   

2. Double-click on the Budget Journal Spreadsheet Upload.xlsm fileBudget Journal Spreadsheet Upload.xlsm file.

   

• The Excel spreadsheet opens.

3. Click Enable ContentEnable Content on the Security Warning yellow bar at the top of the page to enable the macro.

   

4. Go to FileFile.

   

5. Click OptionsOptions.

   

6. Click Add-InsAdd-Ins.

   

7. Click GoGo.

   

8. A list of macro Add-ins availableAdd-ins available will display.
   1. If the add-in Budget Journal Upload Tool Macro does appear, put a check mark beside it.
   2. If the add-in does not appear, follow the next step.

   

9. Click BrowseBrowse.

   

10. Go to the Budget Journal Spreadsheet file
11. HighlightHighlight the Jrnlmcro2.xlam file.

    

12. Click OKOK.

    

• The budget spreadsheet template is ready to use.

Downloading the GL Journal Spreadsheet XLAM File

Steps

1. Click on the  Spreadsheet Journal Upload Files link under OBM Forms on the OBM Website.
2. Right Click on the XLAM file link XLAM file link for the "GL Journal Spreadsheet Upload" Excel Macro file description.

   

3. Click Save link asSave link as.

   

4. Click Enable MacrosEnable Macros if a security warning appears.

   

5. BrowseBrowse to the location where the Journal Upload folder is stored.

   

6. Double-click on the Journal UploadJournal Upload folder.

   

7. Double-click on the sub-folder GL Journal Spreadsheet UploadGL Journal Spreadsheet Upload.

   

8. Double-click on the sub-folder Macro Macro .

   

9. Click SaveSave.

   

10. CloseClose the window.

    

• The JRNLMCRO_WS.xlam download is complete.

Downloading the XLSM GL Journal Spreadsheet

Steps

1. Right Click on the XLSMXLSM link in the "GL Journal Spreadsheet Upload" (GL_Journal_Spreadsheet_Upload_JRNL2_WS.xlsm) file description under the Spreadsheet Journal Upload Files section of the OBM website.

   

2. Click Save link asSave link as.

   

3. BrowseBrowse to the location where the Journal Upload folder is stored.

   

4. Double-click on the Journal UploadJournal Upload folder.

   

5. Double-click on the sub-folder GL Journal Spreadsheet UploadGL Journal Spreadsheet Upload

   

6. Click SaveSave.

   

7. CloseClose the window.

   

• The GL_Journal_Spreadsheet_Upload_JRNL2_WS.xlsm download is complete.

Installing the Macro

Steps

1. Open a new Excel spreadsheet.

• The Excel spreadsheet opens.

3. Go to FileFile.

   

4. Click OptionsOptions.

   

5. Click Add-InsAdd-Ins.

   

6. Click GoGo.

   

7. A list of macro Add-ins availableAdd-ins available will display.
   1. If the add-in does appear, put a check mark beside it.
   2. If the add-in JRNLMCRO_WS does not appear, follow the next step.

   

8. Click BrowseBrowse.

   

9. Go to the GL Journal Spreadsheet Upload folder.
10. Double-click the Macro folderMacro folder.

    

11. HighlightHighlight the JRNLMCRO_WS.xlam file.

    

12. Click OKOK.

    

• The GL spreadsheet template is ready to use.