Just how do you get invoices loaded into payables using microsoft excel
This presentation is the property of its rightful owner.
Sponsored Links
1 / 27

Just how do you get invoices loaded into Payables using Microsoft Excel? PowerPoint PPT Presentation


  • 147 Views
  • Uploaded on
  • Presentation posted in: General

NZOUG Conference 2008. Just how do you get invoices loaded into Payables using Microsoft Excel?. EW Interfacing Requirements. Accurate and easy to use Ability to handle multiple layouts Full validation of data Flexible Quick to install. Options - Custom Built Interface.

Download Presentation

Just how do you get invoices loaded into Payables using Microsoft Excel?

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Just how do you get invoices loaded into payables using microsoft excel

NZOUG Conference 2008

Just how do you get invoices loaded into Payables usingMicrosoft Excel?


Ew interfacing requirements

EW Interfacing Requirements

  • Accurate and easy to use

  • Ability to handle multiple layouts

  • Full validation of data

  • Flexible

  • Quick to install


Options custom built interface

Options - Custom Built Interface

  • Should be easy to use

  • Multiple layouts from different suppliers difficult to design for, as not known at the time.

  • Full validation of data would need to be built

  • Requires significant development and testing resource to allow for all invoice scenarios

  • Design and build would be a medium sized project and expensive


Options payables invoice gateway

Options – Payables Invoice Gateway

  • Not able to electronically import data. Must enter the data into the Oracle Gateway forms

  • Has validation after data entry

  • Already available and free

  • No significant time saving or accuracy advantage


Options more4apps spreadsheet

Options – More4Apps Spreadsheet

  • Already built and widely used

  • Full validation on demand and forms to assist with data entry if required

  • Extremely flexible, full use of spreadsheet functionality including macros and formulas

  • Inexpensive software solution which is quick to install and able to be used in minutes.

  • Upgradeable to new Apps releases and to obtain benefits of new features


Processing flow invoice gateway

File Received

Processing FlowInvoice Gateway

File opened

Validate

Data

Submit Payables

Import Process

Data Keyed into Payables

Invoice Gateway forms

Review Import status

& resolve interface exceptions


Processing flow example custom solution

Processing FlowExampleCustom Solution

File Received

Submit Payables

Import Processes

Run custom Program

to load temp invoice data

into payables interface

File opened

& reviewed

File saved in

$MODS_TOP

Validate

Data

Review Import status

& resolve interface exceptions

Run custom Program

To load invoice data

into temp tables


Processing flow more4apps excel solution

File Received

Processing FlowMore4Apps Excel Solution

File opened

& reviewed

Submit Payables Import Process

Upload Data

into payables interface

File saved in

Spreadsheet

Validate

Data

Review Import status

& resolve interface exceptions

Run EW MACRO

To prepare data


Suppliers data

Suppliers Data


Ap invoice wizard

AP Invoice Wizard


Now what

Now what?


Just how do you get invoices loaded into payables using microsoft excel

Convince the supplier to change

the format of their file to meet

your needs.


Just how do you get invoices loaded into payables using microsoft excel

Convince the supplier to change

the format of their file to meet

your needs.


Just how do you get invoices loaded into payables using microsoft excel

Copy and paste the data from

their file into the template...


Just how do you get invoices loaded into payables using microsoft excel

Copy and paste the data from

their file into the template...


Just how do you get invoices loaded into payables using microsoft excel

Use formulas in the template

and your own Excel macro to do all the hard work for you....


Just how do you get invoices loaded into payables using microsoft excel

Use formulas in the template

and your own Excel macro to do all the hard work for you....

Yeah right!


Macro translations

MACRO translations

  • I don’t know where or how to start!

- Use the Excel “Record Macro” functionality for clues on Macro coding language

How on earth do I do that?

- Use Cyberspace, it’s already been done and is on the internet somewhere!

I can’t save the macro in the wizard...

- That’s a good thing, save in a separate file

What if I have to change it?

- Use comments to clearly identify each step the macro performs


Macro advantages

MACRO Advantages

  • All macros for each supplier format can be stored in the same workbook on your LAN

  • Quick and are controlled by you, not developers.

  • Allows EW to accept electronic files in any format a supplier chooses, meaning better cooperation and wider roll-out.

  • No restriction on electronic format received, as long as the data can be pasted/copied into Excel


More4apps ew s processing steps

More4Apps – EW’s Processing Steps

  • Copy the supplier invoice electronic file into the last sheet of the More4Apps Excel workbook.

  • Run the “Prepare Data” macro

  • Check control totals and other EW validation fields

  • Submit the More4Apps Upload lines process

  • Submit the Payables Invoice Import from the spreadsheet

  • Review Interface Status of the invoices


What does our macro do

What does our MACRO do?

  • EW have a “Prepare Data” worksheet that is used by the Macro to control the placement and formatting of data in the More4Apps template.

  • The macro performs the following (in seconds):

    • Formats and pastes the supplier’s raw data into the “Prepare Data” worksheet

    • Uses formulas in the More4Apps template to populate and format the data from the “Prepare Data” sheet into the desired columns in the template sheet

    • Removes previous spreadsheet interface load results

    • Prepares control totals


Ap invoice wizard1

AP Invoice Wizard

  • Quick demo of actual EW invoice load.


Macro code examples

Macro code examples

  • These pieces of code are examples of what can be done...

To turn off and on screenupdating:

Application.ScreenUpdating = False/True

To clear data from spreadsheet cells:

Range("A10:AC500").Select ‘select the range

Selection.ClearContents

To copy values to the clipboard

Range("A7:H500").Select 'Select the data on this page

Selection.Copy 'Copy it to the clipboard


Macro code examples1

Macro code examples

To paste values

Range("A8").Select 'Go to the first cell ready for pasting

Selection.PasteSpecial Paste:=xlPasteValues, _

Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

To sort data

Range("A8:N500").Select

Selection.Sort Key1:=Range("A8"), Order1:=xlDescending, _

Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _

Orientation:=xlTopToBottom

To find the last used row in the worksheet

ActiveSheet.UsedRange.Rows.Count

To find the last worksheet in the workbook

Worksheets(Worksheets.Count).Activate


Macro code examples2

Macro code examples

To create a blank row for each supplier for the TAX row...

Dim lastrow1 As Long, r1 As Long

lastrow1 = ActiveSheet.UsedRange.Rows.Count

For r1 = lastrow1 To 9 Step -1

If Cells(r1, 1).Value <> Cells(r1 - 1, 1).Value Then Rows(r1).Insert

Cells(r1, 1).Value = Cells(r1 - 1, 1).Value

Next r1

To delete rows based on data in cells

Dim lastrow2 As Long, r2 As Long

lastrow2 = ActiveSheet.UsedRange.Rows.Count

For r2 = lastrow2 To 8 Step -1

If Cells(r2, 1).Value = "" Then Rows(r2).Delete

'If Left(Cells(r2, 2).Value, 1) = "#" Then Rows(r2).Delete

Next r2


Macro code examples3

Macro code examples

To create formulas in the template sheet

Range("S7").Select ‘formula to add all dist rows to row 500

ActiveCell.FormulaR1C1 = "=SUM(R[2]C:R[493]C)"

Range("O7").Select ‘formula to add all invoice rows to row 500

ActiveCell.FormulaR1C1 = "=SUM(R[2]C:R[493]C)"


Questions

Questions?


  • Login