Revised: 02/16/2021 


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/Forms. 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.

 

 

 

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.

 

  1. Click FileFile.

  2. Click OptionsOptions.

 

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

 

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

  2. Click  GoGo.

  3. Remove the mark in the checkboxcheckbox next to Jrnlmcro.

  4. Click OKOK.

 

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.

  3. Name the new folder "Journal Upload."
  4. Double-click on the Journal UploadJournal Upload folder just created to open it.

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

  6. NameName this new sub-folder "Budget Journal Spreadsheet Upload."

  7. Click New FolderNew Folder  again.

  8. NameName this new sub-folder "GL Journal Spreadsheet Upload."

 

Download New Spreadsheet and Macro Files

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

Budget Journal Spreadsheet Upload (BudgetJournalUpload.xlsm) AND

Budget Journal Spreadsheet Upload XLAM File (Jrnlmcro2.xlam)

 

GL Journal Spreadsheet Upload (JRNL2.xlsm) AND

GL Journal Spreadsheet Upload XLAM File (JRNLMCRO.xlam)

 

The Excel files contain a macro that the user will use to create, edit and save a journal, and the XLAM files are used to run the Excel macro. Both of these files must be stored together within the same directory. In other words, the Excel macro and XLAM file for the Budget Journal Spreadsheet Upload will be stored together in the same sub-folder and the Excel macro and the XLAM file for the GL Journal Spreadsheet Upload will be in their own sub-folder.

 

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. Click SaveSave.

  9. CloseClose the window.

 

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.

 

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.

 

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

  2. Go to FileFile.

  3. Click OptionsOptions.

  4. Click Add-InsAdd-Ins.

  5. Click GoGo.

  6. 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.

  7. Click BrowseBrowse.

  8. Go to the Budget Journal Spreadsheet file
  9. HighlightHighlight the Jrnlmcro2.xlam file.

  10. Click OKOK.

 

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. Click on the XLAM file link XLAM file link for the "GL 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 GL Journal Spreadsheet UploadGL Journal Spreadsheet Upload.

  8. Click SaveSave.

  9. CloseClose the window.

 

Downloading the XLSM GL Journal Spreadsheet

Steps

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

  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 GL Journal Spreadsheet UploadGL Journal Spreadsheet Upload

  6. Click SaveSave.

  7. CloseClose the window.

 

Installing the Macro

Steps

  1. Click on the GL Journal Spreadsheet UploadGL Journal Spreadsheet Upload sub-folder to open.

  2. Double-click on the JRNL2.xlsm fileJRNL2.xlsm file.

 

 

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

  2. Go to FileFile.

  3. Click OptionsOptions.

  4. Click Add-InsAdd-Ins.

  5. Click GoGo.

  6. 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 does not appear, follow the next step.

  7. Click BrowseBrowse.

  8. Go to the GL Journal Spreadsheet file
  9. HighlightHighlight the JRNLMCRO.xlam file.

  10. Click OKOK.

 


Click here to request updates to this topic.