I built a query generator to produce a complex query that needs to return results to an Excel 2007 spreadsheet.
I can run the query and dump the results into a spreadsheet, but I want to embed the query in the spreadsheet the same way as if MSQuery built it.
I want the user to save the spreadsheet and be able to update the data by right-clicking and choosing 'refresh data'.
The code below gives a 'parameter is invalid' error.
I tried many different variations for the 'connection string' parameter, from pure text, to the sqlconn object that I use later in the code that works.
Many hours searching the Internet yielded nothing than other people not able to get this to work.
excel.ActiveWorkbook.Connections.Add("Env Report", "New Report", ConfigurationManager.AppSettings("dev"), strQuery, CommandType.Text)