=GetGP(), the Excel Function that gets data from Dynamics GP database into your spreadsheet, one cell at a time.

=GetGP(), the Excel Function that gets data from Dynamics GP database into your spreadsheet, one cell at a time.

At times we need the ability to bring a value from GP into excel, primarily because it could change or to verify if a key is valid. This is a very simple way to provide the much-needed functionality to bring data from GP directly into an Excel spreadsheet and bring Dynamics GP and Excel closer together.

• GL00105 – Account Master (ACTNUMST)
• PM00200 – Vendor Master (VENDORID)
• IV00101 – Item Master (ITEMNMBR)
• RM00101 – Customer Master (CUSTNMBR)
• FA00100 – Account Master (ASSETID)

When using the rest of the parameters, you can point directly to a table and specific fields, check these samples:

A link to a sample spreadsheet (macro enabled) that uses the function included on this blog; you will need to follow the instructions to gain permissions and to point to the right SQL server. Your partner or your IT department can assist you with this task.

Download here the sample Excel file and instructions.

https://files.titaniumgp.net/BlogBits/GetValuesFromGP.zip

The function GetGP Excel UDF (User Defined Function) was created using VBA. If you download the sample file, to see the code for the formula

simply open the VBA Editor (Alt-F11), code is as follows in a module:

_____________________________________________________________________________________________________________________________________

'TitaniumGP
'    Excel Functions to bring and validate data from GP
'    Version 1.0
'
'    COVERED SOFTWARE IS PROVIDED UNDER CDDL-1.0 LICENSE ON "AS IS" BASIS, WITHOUT WARRANTY OF ANY KIND,
'    EITHER EXPRESSED OR IMPLIED, INCLUDING, WITHOUT LIMITATION, WARRANTIES THAT THE COVERED SOFTWARE
'    IS FREE OF DEFECTS, MERCHANTABLE, FIT FOR A PARTICULAR PURPOSE OR NON-INFRINGING. THE ENTIRE RISK
'    AS TO THE QUALITY AND PERFORMANCE OF THE COVERED SOFTWARE IS WITH YOU. SHOULD ANY COVERED SOFTWARE
'    PROVE DEFECTIVE IN ANY RESPECT, YOU (NOT THE INITIAL DEVELOPER OR ANY OTHER CONTRIBUTOR) ASSUME THE
'    COST OF ANY NECESSARY SERVICING, REPAIR OR CORRECTION. THIS DISCLAIMER OF WARRANTY CONSTITUTES AN
'    ESSENTIAL PART OF THIS LICENSE. NO USE OF ANY COVERED SOFTWARE IS AUTHORIZED HEREUNDER
'    EXCEPT UNDER THIS DISCLAIMER.
'
'    Provided under License Agreement CDDL-1.0
'    https://opensource.org/licenses/CDDL-1.0

Public Function GetGP _
            ( _
                         SearchValue As String, _
                Optional WhichGPTable As String = "", _
                Optional FieldToRetrieve As String = "", _
                Optional KeyFieldToSearch As String = "", _
                Optional CompanyDB As String = "" _
            ) As String
            
     'Requires reference to Microsoft ActiveX Data Objects ADO 2.8 (Menu: Tools | References...)
     Dim oConnection As New ADODB.Connection
     
     Dim sServer As String
     Dim sDatabase As String
     Dim inTransaction As String
     Dim oRecordset As ADODB.Recordset
     Dim sSQL As String
      
     On Error GoTo ErrorLabel:
           
     'Connect to the database, get SearchValues from the worksheet advanced properties in the INFO section of Excel
     '  if you prefer not to use custom properties
     sServer = ActiveWorkbook.CustomDocumentProperties("Server")
     If RTrim(CompanyDB) <> "" Then
        sDatabase = CompanyDB
     Else
        sDatabase = ActiveWorkbook.CustomDocumentProperties("Database")
     End If
     If RTrim(sDatabase) = "" Or RTrim(sServer) = "" Then
        GoTo ErrorConnection:
     End If
     
     
     oConnection.Open "provider=sqloledb;data source=" & sServer & ";Trusted_Connection=yes;initial catalog=" & sDatabase & ";"
          
     'Validate against SQL Injection
     If InStr(SearchValue, ";") > 0 Or InStr(SearchValue, "--") > 0 Or InStr(SearchValue, "'") > 0 Then GoTo ErrorInput:
     If InStr(WhichGPTable, ";") > 0 Or InStr(WhichGPTable, "--") > 0 Or InStr(WhichGPTable, "'") > 0 Then GoTo ErrorInput:
     If InStr(FieldToRetrieve, ";") > 0 Or InStr(FieldToRetrieve, "--") > 0 Or InStr(FieldToRetrieve, "'") > 0 Then GoTo ErrorInput:
     If InStr(KeyFieldToSearch, ";") > 0 Or InStr(KeyFieldToSearch, "--") > 0 Or InStr(KeyFieldToSearch, "'") > 0 Then GoTo ErrorInput:

     'Do not allow to bring SearchValues from other databases
     If InStr(WhichGPTable, ".") > 0 Then GoTo ErrorInput:

     Select Case RTrim(WhichGPTable)
        Case Is = ""
            ' Single script to locate description from 5 different entities, you might want to limit depending on database size
            sSQL = "SELECT G1.ACTDESCR Description FROM GL00100 G1 WITH (NOLOCK) JOIN GL00105 G2 ON G1.ACTINDX = G2.ACTINDX WHERE ACTNUMST = '" & SearchValue & "'" & _
                    " UNION " & _
                    "   SELECT VENDNAME FROM PM00200 WITH (NOLOCK) WHERE VENDORID = '" & SearchValue & "'" & _
                    " UNION " & _
                    "   SELECT CUSTNAME FROM RM00101 WITH (NOLOCK) WHERE CUSTNMBR = '" & SearchValue & "'" & _
                    " UNION " & _
                    "   SELECT ITEMDESC FROM IV00101 WITH (NOLOCK) WHERE ITEMNMBR = '" & SearchValue & "'" & _
                    " UNION " & _
                    "   SELECT ASSETDESC FROM FA00100 WITH (NOLOCK) WHERE ASSETID = '" & SearchValue & "'" & _
                    ""
        
        Case Is = "ACCOUNT", "A", "ACC"
            sSQL = "SELECT G1." & FieldToRetrieve & " FROM GL00100 G1 WITH (NOLOCK) JOIN GL00105 G2 ON G1.ACTINDX = G2.ACTINDX WHERE ACTNUMST = '" & SearchValue & "'"
        
        Case Is = "VENDOR", "V", "VEN"
            sSQL = "SELECT " & FieldToRetrieve & " FROM PM00200 WITH (NOLOCK) WHERE VENDORID = '" & SearchValue & "'"
        
        Case Is = "ITEM", "I", "ITM"
            sSQL = "SELECT " & FieldToRetrieve & " FROM IV00101 WITH (NOLOCK) WHERE ITEMNMBR = '" & SearchValue & "'"
        
        Case Is = "CUSTOMER", "C", "CUS"
            sSQL = "SELECT " & FieldToRetrieve & " FROM RM00101 WITH (NOLOCK) WHERE CUSTNMBR = '" & SearchValue & "'"
        
        Case Is = "ASSET"
            sSQL = "SELECT ASSETDESC FROM FA00100 WITH (NOLOCK) WHERE ASSETID = '" & SearchValue & "'"
        
        Case Else
            sSQL = "SELECT " & FieldToRetrieve & " FROM " & WhichGPTable & " WITH (NOLOCK) WHERE " & KeyFieldToSearch & " = '" & SearchValue & "'"
        
        End Select

     'Execute the SQL Script
     inTransaction = oConnection.BeginTrans
     Set oRecordset = oConnection.Execute(sSQL)
     If oRecordset.EOF Then
         'If SearchValue not found then return n/a
         GetGP = "n/a"
     Else
         ' Return the found FieldToRetrieve
         If RTrim(FieldToRetrieve) = "" Then
            GetGP = RTrim(oRecordset(0)) 'Get first column of the dataset
         Else
            GetGP = RTrim(oRecordset(FieldToRetrieve))
         End If
     End If
     oConnection.RollbackTrans
     inTransaction = 0
     oRecordset.Close
     oConnection.Close
     
     Exit Function

ErrorLabel:
     If inTransaction > 0 Then
        oConnection.RollbackTrans
     End If
     
     GetGP = "<<Error:" & Err.Description & ">>"
     Exit Function
     
ErrorInput:

     GetGP = "<<Parameters of this function must not contain ' (single quote) ; (semicolon) or -- (two continuos dashes)>>"
     Exit Function

ErrorConnection:

     GetGP = "<<Database or Server have not been defined in the Custom Properties window>>"
     Exit Function
     
ExitFunction:

End Function

_____________________________________________________________________________________________________________________________________________
_____________________________________________________________________________________________________________________________________________

For more info visit our website:  http://www.titaniumgp.com/2017/04/getgp-excel-function/