Uploading Cash Receipts from Excel

1
Uploading Cash Receipts from Excel
2
Uploading Cash Receipt Entries from Excel

• We now have a way to create cash receipt entries
  in Excel and upload them to the AS400. This
  uploaded file will create a batch if one does not
  exist, and will have will have full edit
  capability, just as it would if you had entered
  it using Menu Option 1 Enter Cash Receipts!

3
The set up in Excel to download to/from the
AS400 is easy.

• In Excel
• Go to the TOOLS pull-down menu
• Select Add-Ins
• Check iSeries Access Data Transfer
• Check the Browse Button
• Navigate to
• C\Program Files\IBM\Client Access\Shared\cwbtfxla
  .xll
• Click OK

You only have to do this once!
4
When youve finished this process, you should
have two new Icons on your Excel toolbar.
This one is used to transfer files from the AS400
into Excel on your PC
This one is used to transfer Data from Excel to
the AS400 (Well cover this another time.)
5
Uploading Cash Receipt Entries from Excel
Uploads to the AS400 all start with an Excel
template that we provide you. This template has
all the fields in the cash receipt entry file
defined. There are a few rules involved
when using file templates.
6
Excel Template Rules

• Columns may be reordered to the users preference
  for ease of entry.
• So if you wanted to move the date field (column
  I), to column A you can.
• Unwanted columns may be deleted, however, the
  absence of certain
• information may lead to errors on the edit
  listing. For example, if you want delete
• the SSN (column N), the file will upload
  OK, but you may get warnings/errors
• on your edit listing if you have entries
  that contain payroll objects.
• The column headers cannot be modified!!! The
  AS400 depends on these
• field descriptions for placement of the
  data when you upload the file.

7
Where do I find these templates?
Well send them to you and you put them on your
PC here C\OSAS\Programs
Here they Are!!!!
8
Heres how it works.
You use the template named Cash_Receipt.xls in
your C\ drive to make your Journal entries.
Heres how you would enter a negative figure.
Figures should be entered as you would enter
them in Excel (with decimals), not without, as
in you would in OSAS.
9
Data Selection for Upload
Next highlight the cash receipt data within the
spreadsheet that you want to upload. Be sure to
include Row 1 (Column Descriptions).
10
Then click this icon to upload the data to the
AS400.
11
Transfer to AS400 Window

• When you see this window-
• Check the First row is
• Column Heading(s) box.
• Verify that the data you
• selected to upload
• corresponds with
• the cell positions.
• Click Create from File
• then click the Browse
• button.
• Navigate to C\OSAS\
• Programs\Cash_Receipt.dtt

12
Transfer to AS400 Window messages
Youll see this message every time you transfer
unless you uncheck this box. Just click OK
And OK here, too.
13
To see your entries and run an edit listing, use
Option 5 Import Cash Rcpts from Excel
14
Decisions.Decisions.
Heres where you decide whether to add your
entries to an existing Cash Receipts batch or
replace an existing batch with your new entries
by overwriting them. You also have the option to
clear any entries in the file and start over.
15
Upload Error Listing

• Once youve uploaded your file, a report will be
  sent to your outq called a Cash Receipts Upload
  Error Report. It will list any entry containing
  errors and details about the error. This
  listing will be blank if there are no errors in
  the uploaded file.
• In the example given here, the budget account is
  either closed or is not valid.

16
How do I know if the upload actually happened?
This message will confirm that your entries
were successfully imported to an OSAS Cash
Receipts file! Now youre ready to run an edit
listing. Run 2 off the menu Cash Receipts
Edit
17
Heres a sample of the edit listing from the
files we uploaded.
18
Making Changes to Uploaded Cash Receipt Entries

• From this point, you can access/edit the uploaded
  entries using Option 1 Enter Receipts..or..

19
Making Changes to Uploaded Budget Journal Entries
Dont forget that you can your edit entries in
Excel, upload again to the AS400, and
(O)verwrite the original entries here!
20
Questions?

• If you have questions about the upload process,
  or if you want to try uploading Cash Receipt
  entries to the AS400, call us. We have the
  template (and the answers) you need.
• OSAS Technical Support 541-266-4041