Link to home
Start Free TrialLog in
Avatar of Dale Fye
Dale FyeFlag for United States of America

asked on

Create PayPal invoice from within VBA application

Anybody done any invoicing in PayPal via VBA?

I've got a client who would like to generate an invoice in PayPal, but not send the invoice from PayPal.  He wants to pass in some basic info for the invoice, but not the invoice details from within his Access application.  We would then get the PayPal invoice ID from the Response and embed that as the hyperlink address of a label in the Access invoice, which gets saved as PDF and transmitted to the client using Outlook automation.

Just looking to see if anyone has any sample code that is specific to PayPal before I go digging.
Avatar of Bill Prew
Bill Prew

Can't say as I've ever seen VBA code for that.  You might have better luck looking for VB6, or VB.NET examples and then adapting them to VBA.  Just a thought, I know you're bright, probably already working that angle...


»bp
@Bill pointed to the right direction
If the client doesn't mind paying you can get ready working solution
Their API is very well documented, though I haven't used it.
You'll need first to obtain credentials, then go to this page for testing:

PayPal Invoicing

where you can check out both in dummy mode (mock) as well as perform a true transaction.
However, no VB(A) code can be generated, but the default cURL gives a good idea about parameter values.

Prepare to spend some days figuring this out. I've worked with other APIs and there is no mercy; all details must be proper.

Not au fait with paypal however you may are probably considering curl commands to get the paypal invoice num...

I did something similar to get auth tokens from blizz servers:

Sub GetNewAuthToken()

Dim strClientID As String
Dim strClientSecret As String
Dim strResult As String
Dim strCURL As String
Dim varArray As Variant

strClientID = CurrentDb.Properties("ClientID")
strClientSecret = CurrentDb.Properties("ClientSecret")
strCURL = "curl -u " & strClientID & ":" & strClientSecret & " -d grant_type=client_credentials https://us.battle.net/oauth/token"

strResult = fncShellRun(strCURL)

strResult = Replace(strResult, Chr(34), "")
strResult = Replace(strResult, "access_token:", "")
strResult = Replace(strResult, "access_token:", "")
strResult = Replace(strResult, "{", "")
varArray = fncCsvToArray(strResult, 44)

strResult = varArray(0)

CurrentDb.Properties("AuthToken") = strResult

End Sub

Open in new window


shell cmd:

Public Function fncShellRun(sCmd As String) As String

    'Run a shell command, returning the output as a string
    Dim oShell As Object
    Dim oExec As Object
    Dim oOutput As Object
    Dim s As String
    Dim sLine As String
    
    Set oShell = CreateObject("WScript.Shell")

    'run command
    Set oExec = oShell.Exec(sCmd)
    Set oOutput = oExec.StdOut

    'handle the results as they are written to and read from the StdOut object
    
    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        If sLine <> "" Then s = s & sLine & vbCrLf
    Wend

    fncShellRun = s

End Function

Open in new window



fncCSVToAarray (super useful over the years ;-) )

Function fncCsvToArray(strDataLine As String, intDelimiter, Optional intTextQual As Integer) As Variant
    
    'Author: TheRealMongoose
    
    Dim strField() As String
    Dim booDelimit As Boolean
    Dim X As Integer
    Dim Y As Integer
    Dim intParseMethod As Integer
    
    Dim varItem As Variant
    
    ReDim strField(0)
    booDelimit = False
    
    Y = 1
    
    If IsMissing(intTextQual) Then
        intParseMethod = 1
    ElseIf InStr(strDataLine, Chr(intTextQual)) = 0 Then
        intParseMethod = 1
    Else
        intParseMethod = 2
    End If
    
    Select Case intParseMethod
    
        Case 1
            'text qualifier not supplied in arguments
            'or is supplied but not present in data
            For X = 1 To Len(strDataLine)
                If Mid(strDataLine, X, 1) = Chr(intDelimiter) Then
                    strField(UBound(strField)) = Mid(strDataLine, Y, X - Y)
                    Y = X + 1
                    ReDim Preserve strField(UBound(strField) + 1)
                End If
            Next X
            
            strField(UBound(strField)) = Mid(strDataLine, Y, X - Y)
            
        Case 2
            'text qualifier supplied in arguments
            'and present in data
            For X = 1 To Len(strDataLine)
                If Mid(strDataLine, X, 1) = Chr(intTextQual) Then
                    booDelimit = Not booDelimit
                    If booDelimit Then
                        Y = Y + 1
                    End If
                End If
                If Mid(strDataLine, X, 1) = Chr(intDelimiter) Then
                    If Not booDelimit Then
                        strField(UBound(strField)) = Mid(strDataLine, Y, X - Y - 1)
                        Y = X + 1
                        ReDim Preserve strField(UBound(strField) + 1)
                    End If
                End If
            Next X
        
            strField(UBound(strField)) = Replace(Mid(strDataLine, Y, X - Y), Chr(intTextQual), "")
            
            'Debug.Print strField(UBound(strField))
            
    End Select
    
    fncCsvToArray = strField
    
End Function

Open in new window

Dale,

 I know you were also working with Auth.net, and this is probably the same client and just more of the same.  
 
 The only thing I'm going to add is that before you go any deeper with straight code like I showed you is to consider using the VBA-WEB tool written by Tim Hall.   I know Jack Leach ran unto some issues with the JSON parsing, which he discussed here:

https://dymeng.com/parsing-json-with-vba/ 

  But so far, I haven't found those.  

  As for why the VBA-WEB lib, it has oAuth capabilities, which I am starting to run into more and more with web services.  Everyone is heading that way retiring their token based access and I would start using this lib now so you become familer with it.

 And as far as the question, I have not worked with Paypal as yet, which is why I didn't comment.

Jim.


Avatar of Dale Fye

ASKER

Thanks, Jim.

I'm not finding anything other than the JSON code on their developers site, but that contains way more info than my client wants to send. I've requested technical assistance to find out what the minimum required data is to create the invoice, but so far all I've heard is crickets.

Dale
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks, Jim.  I'll take a closer look at that later today.

For the moment, the client has decided he can live without the invoice number and simply use paypal.me, which is really simple, looks something like:  https://www.paypal.me/AccountName/10
which is what I use in the Donate link in my Access Shortcut Tool and the Linked Table Manager.  This will provide him with a payment date, amount, and client name in his paypal accounts, not quite as elegant as having a specific invoice number, but close enough.  Although it does provide the client with the ability to change the amount paid ( $10 in my example).

I found the JSON text on the PayPal sight, but it was the
curl ....

Open in new window

code I've got no experience with.  Will have to look into that.


<<Will have to look into that. >>

 I've got some cheat sheets that I found on the web that helped me quite a bit with that.   I'll scan them and upload.

Jim.
@Dale,

Are you all set with this now, or do you need more help?  If all set, could you please close it out now.  If you need help with the question close process take a look at:



»bp