8 Replies Latest reply on Sep 11, 2019 8:19 AM by LJ LongWing

    Creating a tool for company and need some help with data source

    Cody Shanks
      Share This:

      I am creating a tool in Excel that will make it more efficient to search for "Company" within Remedy, but I am unsure how to query the information into Excel via VBA.  Any and all information pointing in the right direction would be greatly appreciated.

        • 1. Re: Creating a tool for company and need some help with data source
          Parag Ghanekar

          I am not 100% sure what you mean here, but if i am not wrong you want to pull data from remedy using Excel? 

           

          If thats the case easiest option would be to use ODBC connection to remedy, and then getting the data that way. 

          • 2. Re: Creating a tool for company and need some help with data source
            LJ LongWing

            Cody,

            The only way to pull data via Excel is with ODBC.  The only ODBC that BMC still produces is 32 bit, so if your Excel is 64 bit, you can't use the 32 bit ODBC.  BMC Actively produces a JDBC, but Excel can't use JDBC natively...there are some bridges out there that you can buy/use....but your road is not paved very well to move forward with your chosen tools.

            3 of 3 people found this helpful
            • 3. Re: Creating a tool for company and need some help with data source
              Kevin Barker

              You can use the REST api and vba to get Remedy data and populate it into a spreadsheet (or vice versa -- push data from a spreadsheet into new HPD:Help Desk records, for example).

               

              I used this GitHub - VBA-tools/VBA-Web: VBA-Web: Connect VBA, Excel, Access, and Office for Windows and Mac to web services and the …  to write code that accesses the REST api.

              It works very well...we've used it to 'bulk submit' incidents and for a bit of data retrieval.

              The project hasn't been updated in a while but it certainly works, as is.

              4 of 4 people found this helpful
              • 7. Re: Creating a tool for company and need some help with data source
                Carl Wilson

                Hi,

                the arapi.net will also allow you to pull data into Excel, and has a sample spreadsheet with Visual Basic code to do this.  The only issue is this is not a fully supported API from BMC, but it does the job.

                 

                Example code (uses buttons and a drop down in the spreadsheet):

                 

                ------------------------------------------------

                im arserver

                 

                 

                Sub LoginButton_Click()

                    On Error GoTo LoginError

                    Set arserver = CreateObject("BMC.ARSystem.Server")

                   

                    Dim server

                    server = InputBox("Enter the name of the AR System Server", "Login")

                    Dim user

                    user = InputBox("Enter your user name", "Login")

                    Dim password

                    password = InputBox("Enter your password", "Login")

                 

                 

                    'arserver.Login "WIN-ATMSK2NQP0B", "Demo", "", ""

                    arserver.Login server, user, password

                   

                    ' load the forms

                    Dim schemas

                    Set schemas = arserver.GetListForm()

                    Dim schema

                    Dim sheet2 As Worksheet

                    Set sheet2 = Application.Worksheets(2)

                    Dim i

                    i = 1

                    Dim topCell As Range, bottomCell As Range, sourceRange As Range

                    Set topCell = Range("Sheet2!A1")

                    Set bottomCell = Range("Sheet2!A65535").End(xlUp)

                    Set sourceRange = Range(topCell, bottomCell)

                    sourceRange.ClearContents

                    For Each schema In schemas

                        Dim cellIndex

                        cellIndex = "Sheet2!A" & i

                        Set topCell = Range(cellIndex)

                        Set sourceRange = Range(topCell, topCell)

                        sourceRange.Value = schema

                        i = i + 1

                    Next

                    Exit Sub

                   

                LoginError:

                    MsgBox Err.Description

                   

                End Sub

                 

                 

                Sub GetData_Click()

                   

                    On Error GoTo Handle_Error

                   

                    ' Go get then number of rows by status

                    ' First get the status field

                    Dim formNum

                    ' formName is linked to Sheet2!$C$1

                    Dim formRange As Range

                    Set formRange = Range("Sheet2!$C$1")

                    formNum = formRange.Value

                    ' Get the form name with that index

                    Dim formCell

                    formCell = "Sheet2!$A$" & formNum

                    Set formRange = Range(formCell)

                    formName = formRange.Value

                    Dim field

                    Set field = arserver.GetField(formName, 7)

                    Dim status

                    Dim i

                    i = 0

                    Dim topCell As Range, bottomCell As Range, sourceRange As Range

                    Set topCell = Range("Sheet1!B10")

                    Set bottomCell = Range("Sheet1!B65535").End(xlUp)

                    Set sourceRange = Range(topCell, bottomCell)

                    sourceRange.ClearContents

                    Set topCell = Range("Sheet1!C10")

                    Set bottomCell = Range("Sheet1!C65535").End(xlUp)

                    Set sourceRange = Range(topCell, bottomCell)

                    sourceRange.ClearContents

                     

                    Dim groupBy

                    groupBy = "7"

                      

                   

                    Dim statistics

                    Set statistics = arserver.GetEntryStatistics(formName, "", "", 1, groupBy)

                    Dim cellName

                    For Each status In field.Limit.Values

                        ' Make a query for the number of rows with this status

                        Dim qual

                        qual = "'7' = " & i

                        Dim numMatches As Long

                        ' Find the matching statistic

                        numMatches = 0

                        Dim j

                        Dim stat

                        For Each stat In statistics

                            Dim groupByValues

                            Set groupByValues = stat.groupByValues

                            Dim groupByValue

                            ' We know there is only one group by value

                            For Each groupByValue In groupByValues

                                If groupByValue = i Then

                                    numMatches = stat.Value

                                End If

                          

                            Next

                        Next

                        ' Set the rows on sheet 3

                        ' Now insert status in col1, count in col2

                        i = i + 1

                        cellName = "Sheet1!B" & (i + 10)

                        Set topCell = Range(cellName)

                        Set sourceRange = Range(topCell, topCell)

                        sourceRange.Value = status

                        cellName = "Sheet1!C" & (i + 10)

                        Set topCell = Range(cellName)

                        Set sourceRange = Range(topCell, topCell)

                        sourceRange.Value = numMatches

                    Next

                    GoTo Success

                   

                Handle_Error:

                    MsgBox Err.Description

                Success:

                End Sub

                 

                 

                Sub FormList_Change()

                End Sub

                ------------------------------

                 

                Cheers

                Carl

                1 of 1 people found this helpful
                • 8. Re: Creating a tool for company and need some help with data source
                  LJ LongWing

                  I often forget about that API because, as you said, it's not supported, and I don't think it's been updated in quite some time....I personally like the idea of using Rest to pull the data that you need in.