Excel’s ODBC and OLEDB connectivity can be a slow an laborious, completing fields repeatedly with little or no real control over the end result until more menu surfing and button clicking is done, which is where my most recent code snippet has come from. Although I am a fairly heavy user of Excel, producing reports and analysing statistics from our service desk, it’s nothing compared to the volume of work my colleague Carly Bond puts through it.
Between the two of us we came up with the idea of a simple but powerful Excel Add-in, one that not only took the hassle out of creating the database connections but also saves entering the same text repeatedly.
Here’s a code snippet of my add-in, The Add-in was developed using Visual Studio.Net. To use this code, create a new form, on the form add controls for the user to input the server, database, table names and a box for the SQL statement. In my example below I use comboboxes as my source for the information. In a later revision of the code I have also added logic to save the previously entered values, so if a user repeatedly enters the same values over and over again this should help save time.
Upon publishing, we also decided to remove the connection after extracting the data but this is personal preference.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
Imports Microsoft.Office.Interop.Excel PublicClass myForm Dim Workbook As Excel.Workbook = CType(Globals.ThisAddIn.Application.ActiveWorkbook, Excel.Workbook) Dim sheet As Excel.Worksheet = CType(Globals.ThisAddIn.Application.ActiveSheet, Excel.Worksheet) PrivateSub CommArr() Dim commandarray Dim connectionstring commandarray = txtSelect.Text connectionstring = _ "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;" _ & "Initial Catalog=" & cmbDB.Text & ";" & "Data Source=" & cmbServer.Text & ";" _ & "Use Procedure for Prepare=1;" _ & "Auto Translate=True;" _ & "Packet Size=4096;" _ & "Workstation ID=MIKEHUDSON;" _ & "Use Encryption for Data=False;" _ & "Tag with column collation when possible=False" Workbook.Connections.Add(txtName.Text, "SQL Commander Connection", connectionstring, commandarray, 2) ' Workbook.Connections.Item(1).OLEDBConnection.Connection.ToString() sheet = Workbook.ActiveSheet With sheet.QueryTables.Add(Workbook.Connections.Item(1).OLEDBConnection.Connection, sheet.Range("A1"), txtSelect.Text) .Name = "Data" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh(BackgroundQuery:=False) EndWith sheet.Name = "Worksheet" EndSub EndClass |
If you would like to add the ability to save the values, use Visual Studio’s built in ‘my.settings’ logic and store the values in application settings upon execution.
This is an Add-in in it’s very early stages, and as the code matures I will post updated versions.
Hope this is of use to someone.