none
Need to fetch data from Remedy database to excel after passing a parameter to query RRS feed

  • Question

  • I am trying to set up a parameter query using ODBC-Microsoft Query to BMC Remedy The ODBC connection is "AR System ODBC Data Source".

    I want to pass a parameter to modified date field and get all the remedy tickets for which the modified date is<= to the passed parameter date.

    So in MS query, SQL-  the query looks like

     WHERE ("Trouble Ticket"."Create Date">={ts '2014-10-01 00:00:00'}) AND ("Trouble Ticket"."Modified-date"<=?)

    I am getting the data fetched from database in the MS query window, but unfortunately

    When I click File-Return Data to Microsoft Excel, its showing an error: Driver]Parameter missing

    I set the connection properties- parameter-Prompt for value using the following string.

    ----Finally I saw from a site that Excel's parameter query does not support extraction of data from Remedy's database.

    Can anyone here help me with another way to connect to Remedy database from excel and fetch data by passing parameter.

    -->I found some VBA code, excel macro etc explained in some sites. I am new to this so

    can anyone give me step by step way to do this and vba code also


    Please help


    Tuesday, October 7, 2014 3:23 PM

All replies

  • I would start by finding the correct connection string.

    and then familiarize yourself a bit with ADODB library.

    Tuesday, October 7, 2014 3:57 PM
  • Thank u soo much Michal ,

    I went through the links you gave me and found connection string .

    I think it is

    DSN=AR System ODBC Data Source; Uid=I gave my username here; Pwd= I gave my password here;

    then Creating ADO Objects in Visual Basic, it is : Dim conn As New ADODB.Connection

    Please correct me if I am wrong(I want to connect to Remedy database from excel)

    Under Referencing the ADO Library

    it is written to select the below libraries also , but I couldn't find these under References

    • Visual Basic runtime objects and procedures
    • Visual Basic objects and procedures.

    Please help. .And guide me to the next step to the next step


    Wednesday, October 8, 2014 9:10 AM
  • Thank u soo much Michal ,

    I went through the links you gave me and found connection string .

    I think it is

    DSN=AR System ODBC Data Source; Uid=I gave my username here; Pwd= I gave my password here;

    then Creating ADO Objects in Visual Basic, it is : Dim conn As New ADODB.Connection

    Please correct me if I am wrong(I want to connect to Remedy database from excel)

    Under Referencing the ADO Library

    it is written to select the below libraries also , but I couldn't find these under References

    • Visual Basic runtime objects and procedures
    • Visual Basic objects and procedures.

    Please help. .And guide me to the next step to the next step

    Wednesday, October 8, 2014 9:20 AM
  • Hi,

    To use ADODB connection in VBA, you need to add the reference of Microsoft AcitveX Data Objects x.x Library in Tool tab -> References.

    Here is a link which may help you understand ADODB and it provides samples for Excel *.xls file.

    How To Use ADO with Excel Data from Visual Basic or VBA

    Here is a sample about using ADODB to query in Excel 2007 and later version file (*.xlsx).

    Sub GetSumValue()
    
    Dim cn As ADODB.Connection
    'get the data
    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & Application.ActiveWorkbook.FullName & ";" & _
    "Extended Properties=Excel 12.0 xml;"
        .Open
    End With
    
    Application.ActiveWorkbook.Sheets(3).Range("A1").CopyFromRecordset cn.Execute("select sum(ProjejValue)  from [Sheet1$] inner join [Sheet2$] on [Sheet1$].Projcode=[Sheet2$].Projcode where [Sheet1$].[Project Status]='True'")
    
    cn.Close
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 8, 2014 9:46 AM
    Moderator
  • Hi Luna,

    thank you soo much

    How to know which one we have to select for : Microsoft AcitveX Data Objects x.x Library , for x.x u selected 6.1, there is lot of options like 2.8,2.7 ,2.0 etc. Please help.

    Also 

    • Visual Basic runtime objects and procedures
    • Visual Basic objects and procedures.

    are missing is that fine

    And I want to know code example for How To Use ADO with Remedy database data from Visual Basic or VBA.

    can you please help me

    Wednesday, October 8, 2014 10:03 AM
  • >>How to know which one we have to select for : Microsoft AcitveX Data Objects x.x Library , for x.x u selected 6.1, there is lot of options like 2.8,2.7 ,2.0 etc. Please help.

    You can select the higher version library.

    I don't think you need to add the reference of "Visual Basic runtime objects and procedures" and "Visual Basic objects and procedures."

    >>And I want to know code example for How To Use ADO with Remedy database data from Visual Basic or VBA.

    AFAIK, reading Remedy database data is not related to Excel, if you want an example , you could research in the internet or ask help from VBA forum instead of this forum.

    Thursday, October 9, 2014 2:59 AM
  • Thanks Chunchen  for your help.

    Can you help me with a way to solve this issue .

    Thursday, October 9, 2014 3:24 PM