1. Kayako Download customers: we will continue to develop and support Kayako Download beyond July 2017, alongside the new Kayako for existing customers.

    Find out more.

  2. The forum you are viewing relates to Kayako Classic. If you signed up or upgraded to the new Kayako (after the 4th July 2016), the information in this thread may not apply to you. You can visit the forums for the new Kayako here.

Authentication when pulling data using Excel VBA code

Discussion in 'Developing, APIs and extending' started by johnny.lingwood, May 1, 2014.

  1. johnny.lingwood

    johnny.lingwood New Member

    Not sure if I'm posting this in the right place but hopefully someone can help.

    I have some VBA code that I want to use to pull data from a report into excel:

    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://support.mycompany.com/staff/index.php?/Reports/Report/Generate/693" _
    , Destination:=Range("$D$5"))
    .Name = _
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    End With

    However when I run this piece of code, it will pull the text from the login page (see attachment)

    I have tried using:

    Any ideas where I'm going wrong?

    Attached Files:

  2. Gary McGrath

    Gary McGrath Staff Member

    Hi Johnny,

    If your self hosted, you could just install the MySQL ODBC driver, and then have your excel sheet pull data directly out of the Kayako tables.

    Trying to call the report URL is not going to work very easily, as you need to be logged in to view reports.

  3. iangessey

    iangessey Established Member

    Hi Gary - just jumping on the back of this one.
    I have set up an ODBC link into Excel from Kayako (we are self hosted).
    Theoretically, the data link could be set to refresh to build a "kind of" information radiator if the right tables were queried?
  4. Gary McGrath

    Gary McGrath Staff Member

    Hi Ian,

    Indeed, if you use the ODBC driver, you just tell excel to refresh its sheet, and it will update the data, and of course, any charts/pivots using this data will update aswell.

  5. iangessey

    iangessey Established Member

    I may have stumbled across a solution then.
    Thanks for that.

  6. iangessey

    iangessey Established Member

    Right - when i run the report in Kayako using KQL the figures are correct.
    When i run a pivot chart report in Excel over the MYSQL ODBC link (even after refreshing) the figures are out by about 2 or 3 per department.
    Any ideas?
    Im querying swtickets.

  7. Anas

    Anas New Member

    Hi iangessey.62144,

    I have the same problem. Can I know how were you doing to execute directly the KQL request from VBA ?

    Thank you
  8. Anas

    Anas New Member


    We are not self-hosted. I have tried many and many codes and don't find a way to automate the authentication.

    Is there anyone who find a trick to do that.

    Thank you

Share This Page