1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel to Exchequer PIN

Discussion in 'Business Applications' started by djarmour_01522, Dec 16, 2011.

Thread Status:
Not open for further replies.
  1. djarmour_01522

    djarmour_01522 Thread Starter

    Dec 16, 2011
    Hi All,

    Could I possibly ask if anyone could help me with a quandry I have with Excel to Exchequer VBA coding... please?

    I am currently attempting to write a ExchPostPINSum function, to post multiple lines of Purchases (to different job numbers and VAT rates) to a single Purchase Invoice on Exchequer using an Excel spreadsheet.

    Could I ask if anyone could help, please?

    The code I have so far is...

    Function ExchPostPINSum(ByVal _
    Post_Flag As String, _
    Company_Code As String, _
    Account_Code As String, _
    Job_Code As String, _
    Analysis_Code As String, _
    Trans_Date As Date, _
    Your_Ref As String, _
    Alt_Ref As String, _
    Office As String, _
    Business_Area As String, _
    Description As String, _
    Amount As Double, _
    VAT_Code As String, _
    GL_Code As Long, _
    VAT_Amount As Double) As String

    Dim iCoCount As Integer
    Dim lRes As Long
    Dim iRes As Integer
    Dim sCoDatapath As String
    Dim oTrans As Enterprise01.ITransaction2
    Dim iRows As Integer
    Dim iCount As Integer
    Dim sJobCode As String
    Dim SJobAnalCode As String
    Dim dTotalVAT As Double
    Dim dVatTotS As Double
    Dim dVatTotZ As Double
    Dim dVatTotE As Double

    iRows = 0
    If (UCase(Post_Flag) = "YES") Then
    'do function
    Set oToolkit = CreateObject("Enterprise01.Toolkit")
    sCoDatapath = ""
    With oToolkit.Company
    If .cmCount > 0 Then
    For iCoCount = 1 To .cmCount
    If .cmCompany(iCoCount).coCode = Company_Code Then
    sCoDatapath = .cmCompany(iCoCount).coPath
    End If
    Next iCoCount
    If sCoDatapath = "" Then
    ExchPostPINSum = "Invalid Company Code"
    Exit Function
    End If
    ExchPostPINSum = "Unable to access company list"
    Exit Function
    End If
    End With
    With oToolkit.Configuration
    .DataDirectory = sCoDatapath
    End With
    lRes = oToolkit.OpenToolkit
    If lRes <> 0 Then
    ExchPostPINSum = "Can't open Toolkit" + oToolkit.LastErrorString
    Exit Function
    End If

    'post trans
    Set oTrans = oToolkit.Transaction.Add(dtPIN)
    dVatTotS = 0
    dVatTotZ = 0
    dVatTotE = 0
    With oTrans
    .thAcCode = Account_Code
    .thTransDate = Format(Trans_Date, "yyymmdd")
    .thYourRef = Your_Ref
    .thLongYourRef = Alt_Ref
    .thCurrency = 1
    .thOperator = "XL-PIN"
    .thJobCode = Job_Code
    .thAnalysisCode = Analysis_Code
    .thUserField1 = Description

    With oTrans.thLines.Add

    sJobCode = Job_Code
    SJobAnalCode = Analysis_Code
    .tlDescr = Description
    .tlQty = 1
    .tlNetValue = Amount
    If sJobCode <> "" Then
    .tlJobCode = sJobCode
    End If
    If SJobAnalCode <> "" Then
    .tlAnalysisCode = SJobAnalCode
    End If
    .tlVATCode = VAT_Code
    .tlCostCentre = Office
    .tlDepartment = Business_Area
    .tlGlCode = GL_Code
    .tlVATAmout = VAT_Amount
    dVatTotS = dVatTotS + .tlVATAmount
    End With

    .thTotalVAT = dVatTotS + dVatTotZ + dVatTotE
    .thVATAnalysis("S") = dVatTotS
    .thManualVAT = True
    lRes = .Save(True)
    If lRes <> 0 Then
    ExchPostPINSum = "Unable to post: " & oToolkit.LastErrorString & " (" & lRes & ")"
    ExchPostPINSum = "Posted - Tran No: " & .thOurRef
    End If
    End With

    Set oTrans = Nothing
    Set oToolkit = Nothing
    ExchPostPINSum = "Disabled"
    End If 'post flag yes

    End Function

    Many thanks.
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1031498

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice