Solved

VSTO Excel 2007 Addin, Questions about MSQuery Connection

Posted on 2010-11-16
3
887 Views
Last Modified: 2013-11-10
I am trying to create an Excel 2007 Addin that works pretty much like a MSQuery Connection. I have code working where I can get data from a saved MSQuery conenction but when done manually it creates the data in a table. When done with just QueryTable.Add I get the data but no surrounding Table or ListObject. I have tried creating a ListObject but then don't know how to connect the ListObject to the MSQuery Connection. I'll take either direction.
oQueryTable = moToolsExcelActiveSheet.QueryTables.Add("FINDER;" & sDQYTempFileName, oTarget, sSQL)

oQueryTable.Name = "QT Name"

oQueryTable.MaintainConnection = False

oQueryTable.FieldNames = True

oQueryTable.RowNumbers = False

oQueryTable.FillAdjacentFormulas = False

oQueryTable.PreserveFormatting = True

oQueryTable.RefreshOnFileOpen = False

oQueryTable.BackgroundQuery = False

oQueryTable.RefreshStyle = Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells

oQueryTable.SavePassword = True

oQueryTable.SaveData = True

oQueryTable.AdjustColumnWidth = True

oQueryTable.RefreshPeriod = 0

oQueryTable.PreserveColumnInfo = True

oQueryTable.CommandType = Interop.Excel.XlCmdType.xlCmdSql

oQueryTable.CommandText = sSQL 

oQueryTable.Refresh(BackgroundQuery:=False)

oQueryTable.WorkbookConnection.Name = "Test Connection"

Open in new window

0
Comment
Question by:cpv
3 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 34258435
I think you can just use Listobjects.Add and specify the SourceType as 0, the Source as your connection string, and the Destination. That should automatically create an associated querytable that you can then manipulate. In VBA terms:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=connection_string, Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = SQL_here
        .RowNumbers = False
' etc

Open in new window

0
 
LVL 24

Expert Comment

by:broomee9
ID: 34459689
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now