ERP Associates, Inc.

Suppose you have a spreadsheet full of invoices that you want to load to PeopleSoft.  If you’re running PeopleSoft 8.9 or later, that’s easy. You use the voucher load spreadsheet to get them into the system and then run Voucher Build to turn them into vouchers.  But what if you want to load Single Pay vouchers?    That’s something that the spreadsheet voucher load won’t handle.

Okay, so maybe use the ExcelToCI spreadsheet to populate the VCHR_EXPRESS component.  Good luck with that.  If you’ve ever tried then you’ll know all about the "First operand of .null" error.   I spent some quality time with a component interface built off of that component and the PeopleCode debugger.  Unless you’re ready to change some PeopleCode --  maybe a lot of PeopleCode --  I don’t recommend it.

I had one client create a couple of SQR’s to upload single pay vouchers as regular vouchers, then change them to single pay vouchers after voucher build runs.  That solution works well (and if you want to know the details let me know), but if you’re not looking for a high-volume solution it might be overkill.

I recently stumbled across a way to do spreadsheet uploads with a simple spreadsheet macro that doesn’t depend on component interface.   Basically you make an Excel macro open up a browser and key the data in for you.

Before I explain how it works, I just want to let you know that you need to be familiar with Excel macros and Visual Basic for Applications (VBA) code.  You also need to know a little about Application Programming Interfaces (API’s).  And a little knowledge about the HTML Document Object Model (DOM) wouldn’t hurt.  But if you meet most of these qualifications you shouldn’t have any trouble at all in making this work – no special PeopleSoft App Designer access or knowledge required.

And one more caveat:  This was built on PeopleTools 8.48 and Financials 8.9.  Due to how the script works it’s likely it won’t work for PeopleTools releases beyond 8.49.

Consider the following Excel VBA macro:

Sub IETest()

'Define the Internet Explorer (IE) object

Dim ie As Object

Set ie = CreateObject("internetexplorer.Application")

'Make Internet Explorer Visible, and Navigate to the signin page

ie.Visible = True

ie.navigate "http://demo1.erpassociates.com:10040/psp/EP91DMO/?cmd=login&languageCd=ENG"

Do While ie.Busy: DoEvents: Loop

Do While ie.readyState <> 4: DoEvents: Loop

'Enter a Username and Password, and click Submit

ie.document.forms(0).UserId.Value = "VP1"

ie.document.forms(0).pwd.Value = "VP1"

ie.document.forms(0).submit.Click

Do While ie.Busy: DoEvents: Loop

Do While ie.readyState <> 4: DoEvents: Loop

End Sub

This macro defines an Internet Explorer Object called ie.  Then it makes the browser visible, and navigates to the Sign in page of the web site.  The two “Do While” loops make sure the script waits until the page is finished loading and IE is ready.   Then it enters a Username and Password and clicks Submit.  Copy/Paste it into your own Excel macro and change the URL, Username and Password to match your environment and run it.  It’s pretty cool.

From there you can probably guess how to make it navigate to the Voucher Add page.   These lines will take care of that:

ie.navigate "http://demo1.erpassociates.com:10040/psp/EP91DMO/EMPLOYEE/ERP/c/ENTER_VOUCHER_INFORMATION.VCHR_EXPRESS.GBL"

Do While ie.Busy: DoEvents: Loop

Do While ie.readyState <> 4: DoEvents: Loop

OK so you get the idea.  Use ie.navigate to jump to URL’s.  Set values on forms with ie.document.forms(x)..value = “xxx”.  Use ie.document.forms(0)..click to click buttons and hyperlinks.  Use the Do While lines to wait until IE is ready in the mean time.

So now that we know how to manipulate Internet Explorer from an Excel macro, we just need to know how to make our macro read our spreadsheet data.    Then we can write a program to enter the data into PeopleSoft.  Fortunately that’s even easier.

Suppose we have this worksheet:

You can see that I have a worksheet called “Connection”.  Cell B2 which represents the ServerName/Port is a named range called “Server” (top left).  All of the other fields have been set up as named ranges, which makes the macro more understandable.

Now consider this code:

Dim ServerName, Site, Node, UserName, Password As String

ServerName = Sheets("Connection").Range("Server").Value

Site = Sheets("Connection").Range("Site").Value

Node = Sheets("Connection").Range("Node").Value

UserName = Sheets("Connection").Range("Username").Value

Password = Sheets("Connection").Range("Password").Value

You can see that we can use the construction = Sheets("").Range("").Value in order to pull data out of the spreadsheet and assign it to variables that we can use in our macro.

So to bring these two concepts together, we can remove the hard-coding in the macro and pull the connection information from the spreadsheet in order to log in.  Here’s how the code for that looks:

Sub IETest()

'Define the Internet Explorer (IE) object

Dim ie As Object

Set ie = CreateObject("internetexplorer.Application")

'Get the connection information and save it to variables

Dim ServerName, Site, Node, UserName, Password As String

ServerName = Sheets("Connection").Range("Server").Value

Site = Sheets("Connection").Range("Site").Value

Node = Sheets("Connection").Range("Node").Value

UserName = Sheets("Connection").Range("Username").Value

Password = Sheets("Connection").Range("Password").Value

'Make Internet Explorer Visible, and Navigate to the signin page

ie.Visible = True

ie.navigate ServerName & "/psp/" & Site & "/EMPLOYEE/ERP/?cmd=login"

Do While ie.Busy: DoEvents: Loop

Do While ie.readyState <> 4: DoEvents: Loop

'Enter a Username and Password, and click Submit

ie.document.forms(0).UserId.Value = UserName

ie.document.forms(0).pwd.Value = Password

ie.document.forms(0).submit.Click

Do While ie.Busy: DoEvents: Loop

Do While ie.readyState <> 4: DoEvents: Loop

ie.navigate "http://demo1.erpassociates.com:10040/psp/EP91DMO/EMPLOYEE/ERP/c/ENTER_VOUCHER_INFORMATION.VCHR_EXPRESS.GBL"

Do While ie.Busy: DoEvents: Loop

Do While ie.readyState <> 4: DoEvents: Loop

End Sub

Still with me?  Good.  Now suppose our Single Pay Voucher worksheet looks like this:

You can see we have columns for Status, Business Unit, Vendor ID, Invoice Number, Invoice Date, the Amount fields, Vendor Name, Address, Chartfields, etc.   We’ll make our program loop through these rows one at a time, key them into the various PeopleSoft pages and at the end we’ll click Save.   We’ll check for a successful save, then we’ll update that Status column to “Done” so we’ll know where we are in case the script errors for some reason.  By the way, all of these columns have been set up as Named Ranges to make the macro a little easier to read.

So here’s the loop that will do all of this good stuff:

Dim i, x, lineNbr, estLines, idVchrLineAmt, idGLAmt, idGLBU, idAccount, idDept, idSave, idNextRow As Integer

lineNbr = 2

'As long as the Status field isn't "Done", and the last column has data in it, we want to loop

While Sheets("Data").Range("DEPTID").Cells(lineNbr, 1).Value <> ""

If Sheets("Data").Range("Status").Cells(lineNbr, 1).Value <> "Done" Then

'Navigate to the Voucher Express Search Page

ie.navigate ServerName & "/psp/" & Site & "/EMPLOYEE/" & Node & "/c/ENTER_VOUCHER_INFORMATION.VCHR_EXPRESS.GBL"

Do While ie.Busy: DoEvents: Loop

Do While ie.readyState <> 4: DoEvents: Loop

'Enter the values onto the Search (ADD) Page

ie.document.frames(2).win0.VCHR_ADDSRCH_VW_BUSINESS_UNIT.Value = Sheets("Data").Range("BUSINESS_UNIT").Cells(lineNbr, 1).Value

ie.document.frames(2).win0.VCHR_ADDSRCH_VW_VOUCHER_ID.Value = "NEXT" ' Sheets("Data").Range("VOUCHER_ID").Cells(lineNbr, 1).Value

ie.document.frames(2).win0.VCHR_ADDSRCH_VW_VOUCHER_STYLE.Value = "SGLP"

ie.document.frames(2).win0.VCHR_ADDSRCH_VW_VENDOR_ID.Value = Sheets("Data").Range("VENDOR_ID").Cells(lineNbr, 1).Value

ie.document.frames(2).win0.VCHR_ADDSRCH_VW_INVOICE_ID.Value = Sheets("Data").Range("INVOICE_ID").Cells(lineNbr, 1).Value

ie.document.frames(2).win0.VCHR_ADDSRCH_VW_INVOICE_DT.Value = Sheets("Data").Range("INVOICE_DT").Cells(lineNbr, 1).Value

ie.document.frames(2).win0.VCHR_ADDSRCH_VW_GROSS_AMT.Value = Sheets("Data").Range("GROSS_AMT").Cells(lineNbr, 1).Value

ie.document.frames(2).win0.VCHR_ADDSRCH_VW_TAX_EXEMPT.Value = Sheets("Data").Range("TAX_EXEMPT").Cells(lineNbr, 1).Value

ie.document.frames(2).win0.VCHR_ADDSRCH_VW_VCHR_TTL_LINES.Value = 1 'this macro only supports 1 invoice line

ie.document.frames(2).win0.Item(26).Click 'click the "Add" button

Do While ie.Busy: DoEvents: Loop

Do While ie.readyState <> 4: DoEvents: Loop

'Enter Single Pay Vendor Information

ie.document.frames(2).win0.VCHR_VNDR_INFO_NAME1.Value = Sheets("Data").Range("VENDOR_NAME").Cells(lineNbr, 1).Value

ie.document.frames(2).win0.VCHR_VNDR_INFO_ADDRESS1.Value = Sheets("Data").Range("ADDRESS1").Cells(lineNbr, 1).Value

If Sheets("Data").Range("ADDRESS2").Cells(lineNbr, 1).Value <> "" Then

ie.document.frames(2).win0.VCHR_VNDR_INFO_ADDRESS2.Value = Sheets("Data").Range("ADDRESS2").Cells(lineNbr, 1).Value

End If

If Sheets("Data").Range("ADDRESS3").Cells(lineNbr, 1).Value <> "" Then

ie.document.frames(2).win0.VCHR_VNDR_INFO_ADDRESS3.Value = Sheets("Data").Range("ADDRESS3").Cells(lineNbr, 1).Value

End If

ie.document.frames(2).win0.VCHR_VNDR_INFO_CITY.Value = Sheets("Data").Range("CITY").Cells(lineNbr, 1).Value

ie.document.frames(2).win0.VCHR_VNDR_INFO_STATE.Value = Sheets("Data").Range("STATE").Cells(lineNbr, 1).Value

ie.document.frames(2).win0.VCHR_VNDR_INFO_POSTAL.Value = Sheets("Data").Range("POSTAL").Cells(lineNbr, 1).Value

If Sheets("Data").Range("EMAILID").Cells(lineNbr, 1).Value <> "" Then

ie.document.frames(2).win0.VCHR_VNDR_INFO_EMAILID.Value = Sheets("Data").Range("EMAILID").Cells(lineNbr, 1).Value

End If

'Click the "Invoice Information" tab

ie.document.frames(2).tab0.Click

Do While ie.Busy: DoEvents: Loop

Do While ie.readyState <> 4: DoEvents: Loop

'Enter Invoice Information

'First, loop to find IDs that we'll use.  I know this is a hack but I couldn't make GetElementByID work.

x = 0

If idVchrLineAmt = 0 Then

For i = 1 To 100

'Debug.Print i, ie.document.frames(2).win0.Item(i).Name

If ie.document.frames(2).win0.Item(i).Name = "VOUCHER_LINE_MERCHANDISE_AMT$" & x Then idVchrLineAmt = i

If ie.document.frames(2).win0.Item(i).Name = "DISTRIB_LINE_MERCHANDISE_AMT$" & x Then idGLAmt = i

If ie.document.frames(2).win0.Item(i).Name = "GLBU_CHARTFIELDS$" & x Then idGLBU = i

If ie.document.frames(2).win0.Item(i).Name = "DISTRIB_LINE_ACCOUNT$" & x Then idAcct = i

If ie.document.frames(2).win0.Item(i).Name = "DISTRIB_LINE_DEPTID$" & x Then idDept = i

If ie.document.frames(2).win0.Item(i).Name = "$ICField154$hviewall$0" Then idNextRow = i

If ie.document.frames(2).win0.Item(i).Name = "#ICSave" Then idSave = i

If ie.document.frames(2).win0.Item(i).Name = "#ICUpdate" Then i = 101 'exit the loop

Next

End If

'Enter the voucher line and distribution line information here and click Save

ie.document.frames(2).win0.Item(idGLBU).Value = Sheets("Data").Range("GL_BU").Cells(lineNbr, 1).Value

ie.document.frames(2).win0.Item(idAcct).Value = Sheets("Data").Range("ACCOUNT").Cells(lineNbr, 1).Value

ie.document.frames(2).win0.Item(idDept).Value = Sheets("Data").Range("DEPTID").Cells(lineNbr, 1).Value

'Save the Page

ie.document.frames(2).win0.Item(idSave).Click

Do While ie.Busy: DoEvents: Loop

Do While ie.readyState <> 4: DoEvents: Loop

'Check the value of "ICChanged".  If it really saved, ICChanged will have a value of zero.

If ie.document.frames(2).win0.ICChanged.Value = 0 Then

Sheets("Data").Range("Status").Cells(lineNbr + x, 1).Value = "Done"

Else

Sheets("Data").Range("Status").Cells(lineNbr + x, 1).Value = "Error"

MsgBox "Unsuccessful Save"

End

End If

End If

lineNbr = lineNbr + x + 1

Wend

'Clear out or IE Object and free up memory

Set ie = Nothing

I don’t want to go through this code line-by-line, the comments pretty much tell you what each section does.  But let me point out a few things.

First, by the time we get down to a real PeopleSoft component, the HTML document is more complex than our sign in page.  To address a field you have to include another layer.   Take this line:  ie.document.frames(2).win0.VCHR_ADDSRCH_VW_BUSINESS_UNIT.Value

Here’s how that breaks out:

Ie = our Internet Explorer application

Document = The complete HTML document in Internet Explorer

Frames(2) = The frame where our component is.  The menu is another frame, and the heading is another frame – we don’t care about the fields in those frames.

Win0 = The HTML form where the fields appear.

VCHR_ADDSRCH_VW_BUSINESS_UNIT = this is the field name that we’re looking to populate. If you look at the HTML source you’ll find this value under the “ID” tag.

Value= indicates that we’re interested in the value property.

If you’re interested in the hierarchy of HTML documents, do an internet search for “HTML DOM” or HTML Document Object Model”.  Unfortunately most code examples are going to be javascript, but that might be helpful if you’re trying to figure out how to address some obscure field on a page.

Next, notice the “for I = 1 to 100” loop.  This was a hack because I had a really tough time trying to figure out how to address the fields in the Voucher Line and distribution line scroll areas.  I finally gave up and told my macro to read every darn field on the whole page, and save the index of the ones I was looking for.   If you figure out a better way please let me know!

Finally, notice the check of the ICChanged field at the bottom of the page.  This is the field that javascript checks when you try to navigate off a page.  If it’s a 1, PS knows something was changed and it asks you if you want to save before you navigate away.  We’re using this field to figure out if the save was successful or not, and updating the Status field accordingly.

If you’d like to download the whole spreadsheet you can find it here.  Please let me know if you find any better ways to do this.