VSTO 3.0 Get / Modify excel 2007 workbook connection
I am struggling to find a way to get and modify and improve the 2007 workbook connection (Menu Data -> Existing Connections -> Connections in this book). This is the connection (actually actually) with SQL Server and used in the pivot table.
I've tried using Application.ActiveWorkbook.Connections or Globals.ThisWorkbook.Connections , but they both always return Null..Ive tried in a leaf event as well as in a custom event on a ribbon button.
The only way I can think of is using a VBA method to code that does the job and then calls it in my VSTO code, but that's not very elegant ...
source to share
Existing connections in Excel (this works in 2007) are not active connections. You must connect using an existing connection before you can get that connection (I did it manually before clicking the button that executed this code).
var application = Globals.ThisAddIn.Application;
//This must be an active connection otherwise handle exceptions
// such as 'Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))'
var connection = application.ActiveWorkbook.Connections["EXISTING_CONNECTION_NAME"];
var oledb = connection.OLEDBConnection;
var settings = oledb.Connection;
source to share
Here I am setting up the Excel Connections connection string. Please note that I only have one connection in the workbook.
public class WorkbookConnectionsManager
{
public static void AdjustConnectionToSqlConnectionString(Excel.WorkbookConnection connection, String connectionString)
{
char[] propertiesSeparator = new char[] { ';' };
char[] propertyValueSeparator = new char[] { '=' };
Excel.OLEDBConnection oleDbConn = connection.OLEDBConnection;
Dictionary<string, string> dictExcelConnStrProperties = GetConnStrDictionary(oleDbConn.Connection, propertiesSeparator, propertyValueSeparator);
Dictionary<string, string> dictActualConnStrProperties = GetConnStrDictionary(connectionString, propertiesSeparator, propertyValueSeparator);
string[] reggedPropertyies = new string[] { "Integrated Security", "Persist Security Info", "User ID", "Password", "Initial Catalog", "Data Source", "Workstation ID" };
foreach (string property in reggedPropertyies)
if (dictExcelConnStrProperties.ContainsKey(property) && dictActualConnStrProperties.ContainsKey(property)
&& null != dictActualConnStrProperties[property] && !String.IsNullOrEmpty(dictActualConnStrProperties[property].ToString()))
dictExcelConnStrProperties[property] = dictActualConnStrProperties[property];
string connStr = GetConnStrFromDict(dictExcelConnStrProperties, propertiesSeparator[0], propertyValueSeparator[0]);
oleDbConn.Connection = connStr;
}
private static string GetConnStrFromDict(Dictionary<string, string> dictConnStrProperties, char propertiesSeparator, char propertyValueSeparator)
{
StringBuilder connStrBuilder = new StringBuilder();
foreach (KeyValuePair<string, string> keyValuePair in dictConnStrProperties)
{
connStrBuilder.Append(keyValuePair.Key);
if (!String.IsNullOrEmpty(keyValuePair.Value))
{
connStrBuilder.Append(propertyValueSeparator);
connStrBuilder.Append(keyValuePair.Value);
}
connStrBuilder.Append(propertiesSeparator);
}
string connStr = String.Empty;
if (connStrBuilder.Length > 1)
{
connStr = connStrBuilder.ToString(0, connStrBuilder.Length - 1);
}
return connStr;
}
private static Dictionary<string, string> GetConnStrDictionary(string connString, char[] propertiesSeparator, char[] propertyValueSeparator)
{
string[] keyAndValue;
string[] arrayConnStrProperties = connString.Split(propertiesSeparator);
Dictionary<string, string> dictConnStrProperties = new Dictionary<string, string>();
foreach (string excelConnStrProperty in arrayConnStrProperties)
{
keyAndValue = excelConnStrProperty.Split(propertyValueSeparator);
if (keyAndValue.Length > 1)
{
dictConnStrProperties.Add(keyAndValue[0], keyAndValue[1]);
}
else if (keyAndValue.Length > 0)
{
//standalone attribute
dictConnStrProperties.Add(keyAndValue[0], String.Empty);
}
}
return dictConnStrProperties;
}
}
source to share
I don't remember where, but I remember reading somewhere that the Connections collection had limited use for recording ODBC type connections. It has several enumeration values for "connection", but I'm not sure if some of them are read-only from this interface.
Regardless, it should be fairly easy to implement new connections and edit existing ones from the VSTO. The best option would be to use COM interop to call the SQLConfigDataSource () function from the win32 ODBCCP32.DLL library. Also check out the following add- on that makes it easier to work with query tables in Excel.
source to share