[phpBB Debug] PHP Notice: in file /includes/session.php on line 2184: Array to string conversion
[phpBB Debug] PHP Notice: in file /includes/session.php on line 2184: Array to string conversion
[phpBB Debug] PHP Notice: in file /includes/session.php on line 2184: Array to string conversion
[phpBB Debug] PHP Notice: in file /includes/session.php on line 2184: Array to string conversion
[phpBB Debug] PHP Notice: in file /includes/session.php on line 2184: Array to string conversion
[phpBB Debug] PHP Notice: in file /includes/session.php on line 2184: Array to string conversion
[phpBB Debug] PHP Notice: in file /includes/session.php on line 2184: Array to string conversion
[phpBB Debug] PHP Notice: in file /includes/functions.php on line 4509: Cannot modify header information - headers already sent by (output started at /includes/functions.php:3706)
[phpBB Debug] PHP Notice: in file /includes/functions.php on line 4511: Cannot modify header information - headers already sent by (output started at /includes/functions.php:3706)
[phpBB Debug] PHP Notice: in file /includes/functions.php on line 4512: Cannot modify header information - headers already sent by (output started at /includes/functions.php:3706)
[phpBB Debug] PHP Notice: in file /includes/functions.php on line 4513: Cannot modify header information - headers already sent by (output started at /includes/functions.php:3706)
Support Incident Tracker Forum • View topic - Tracker ticket search from Excel macro ?

Tracker ticket search from Excel macro ?

Discuss and get help with plugins. Share your ideas for future SiT! features.

Tracker ticket search from Excel macro ?

Postby rejdrouin » Mon Mar 03, 2014 5:02 pm

Hi all,

I am using an MS Excel macro to generate a Tracker ticket report for the last few months with number of days opened, status, etc. Currently, I do this by :

- do a ticket search in tracker for the last six months using this URL :
http://support.liquidxstream.com/search ... Rechercher

- Select the results using the mouse and copy to clipboard with Ctrl-C

- Open my Excel workbook and run the following macro which clears the "Report" sheet and paste the clipboard into it :

Code: Select all
# Excel VBA - Tracker Report workbook

Sub clearSheetAndPastClipboard()
'
    Sheets("Report").Activate
    Rows("1:1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    Selection.ColumnWidth = 5
   
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 0
    End With
    ActiveWindow.FreezePanes = False

    Range("A1").Select

    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False

End Sub


- Run another Excel macro that sorts data, remove some columns and add others, count days, add borders, etc.

My question : How could I run the Tracker ticket search remotely from Excel macro and import the result automatically into the Excel workbook ? I would expect a URL syntax similar to the one above that would include connection info - user and password - and return the data ?. (That would replace the macro line ""ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False"")

Thanks and regards,
Rd
__________________________
Réjean Drouin, ing., Canada
rejdrouin
SiT! Fan
SiT! Fan
 
Posts: 10
Joined: Fri Mar 08, 2013 7:47 pm
Location: Montréal, Canada

Re: Tracker ticket search from Excel macro ?

Postby Tomse » Mon Mar 03, 2014 6:08 pm

have you considered using ODBC to the sql server ?
If you have a problem, give us enough info of what you have done, what is configured in relation to your question.
Enable debugging and post it's censored but relevant info. Don't forget to write which version of SiT you're running.
User avatar
Tomse
SiT! Developer
SiT! Developer
 
Posts: 1137
Joined: Fri Feb 20, 2009 10:51 am
Location: Somewhere near Copenhagen Denmark

Re: Tracker ticket search from Excel macro ?

Postby rejdrouin » Mon Mar 03, 2014 7:53 pm

Interesting !

Excel VBA first requires Tool Reference "Microsoft ActiveX Data Objects .. " for this, then some code such as this :

Code: Select all
VBA Macro
    'Declare a Connection object
    Dim cnDB As New ADODB.Connection

    'Declare a Recordset Object
    Dim rsRecords As New ADODB.Recordset

    'Open the ODBC Connection using this statement
    cnDB.Open "WriteDSNNameHere"
    rsRecords.Open "Select * from ...", cnDB

    'Print the numberof records in A1 cell
    ...

    'Close everything and set the references to nothing
    ...


Deriving the MySQL query from the URL, I get this :

Code: Select all
http://support.liquidxstream.com/index.php?id=2&page=/
search_incidents_advanced.php
search_title=
search_id=
search_externalid=
search_servicelevel=
search_contact=
search_priority=0
search_product=0
search_details=
search_range=All
search_date=Recent180
search_user=0
sort_results=IDASC
action=search
submit=Rechercher


Is there an easy way to trace the MySQL query generated from the GUI ?

Thank you very much ;)
__________________________
Réjean Drouin, ing., Canada
rejdrouin
SiT! Fan
SiT! Fan
 
Posts: 10
Joined: Fri Mar 08, 2013 7:47 pm
Location: Montréal, Canada


Return to Plugins & Extending SiT!

Who is online

Users browsing this forum: No registered users and 1 guest

cron