1 / 27

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

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.

aric
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. NZOUG Conference 2008 Just how do you get invoices loaded into Payables usingMicrosoft Excel?

  2. EW Interfacing Requirements • Accurate and easy to use • Ability to handle multiple layouts • Full validation of data • Flexible • Quick to install

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

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

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

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

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

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

  9. Suppliers Data

  10. AP Invoice Wizard

  11. Now what?

  12. Convince the supplier to change the format of their file to meet your needs.

  13. Convince the supplier to change the format of their file to meet your needs.

  14. Copy and paste the data from their file into the template...

  15. Copy and paste the data from their file into the template...

  16. Use formulas in the template and your own Excel macro to do all the hard work for you....

  17. Use formulas in the template and your own Excel macro to do all the hard work for you.... Yeah right!

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

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

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

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

  22. AP Invoice Wizard • Quick demo of actual EW invoice load.

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

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

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

  26. 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)"

  27. Questions?

More Related