AnsweredAssumed Answered

Using Excel to make a Report Generator with Hyperlinks

Question asked by Kent Keller on Nov 30, 2011
Latest reply on May 16, 2016 by Asko Promm

I have had several occasions when I needed to write a custom query that cannot be done in the query editor.  Those handy with SQL are often a bit disapointed that there is no way to port a simple SQL query to the Query Result Window.  The Report Generator is ok, but I have found it very clunky.  Cut and paste is limited and there is no way to perform an action against the result (i.e. Checkout, change workflow state, view, get history, etc)


I wrote an Excel Report Generator that is fairly expandable and has links to allow you to see history, navigate and select file or open. 


I thought I would share it with the group for whoever might be interested.


You will need to edit the connection string in the code.  Here is a sample of my connection string.  Change this to your datbase connection.


Dim cn As New ADODB.Connection

Const strConnectionString = "Provider=SQLOLEDB;Data Source=wp-pdmworks;Initial Catalog=WestportEPDM;User Id=Kent;Password=MyPassword"


The report selector should run when you first open the Excel File.


Give it a try and let me know if it works in other implimentations