Working with Lists and Tables in Excel 2007: VBA Samples (Part 2 of 2) (May 2009) Summary: Tables and lists are an integral part of Microsoft Excel. Microsoft Excel MVP Ron de Bruin explains some handy VBA subroutines that can make you more productive when using tables or lists. (8 printed pages) Ron de Bruin, Excel MVP Frank Rice, Microsoft Corporation May 2009 Applies to: Microsoft Office Excel 2003, Microsoft Office Excel 2007 Contents To download a Microsoft Excel workbook containing the procedures discussed in these column and more, see VBA Code Examples for Tables in Excel 2007 or a List in Excel 2003. Copying Tables or Lists to New Workbooks The following code is similar to the subroutine in the previous column except that instead of copying the table or list to a new worksheet in the same workbook, the procedure copies the visible data to a new workbook. Sub CopyListOrTableData2NewWorkbook() 'Works in Excel 2003 and Excel 2007. Filtering Tables or Lists The following code filters a table or list.
Figure 1. To the following: Clearing Filters Conclusion. Navigate a many-to-many relationship. : DataRelation « ADO.Net « C# / CSharp Tutorial. Binding a DataSet to a ListObject. The code below successfully binds the data in the ListObject listTbl to the data in the DataTable twsTable. However, it does not bind the column names or the formatting of twsTable to listTbl. In other words, the column names on listTbl show up on the Excel spreadsheet as "Column 1", "Column 2", etc., and the formatting/column widths is the default for the Excel spreadsheet.
How do I get the column names and formatting from the DataTable bound to the ListObject? Dim listTbl As Microsoft.Office.Tools.Excel.ListObject Dim twsTable As DataTable 'fill twsTable with data and schema from database on SQL Server fromtwsTable.CommandText = "SELECT * FROM [TWS IntraDay] ORDER BY Ticker" twsTableDa.Fill(CosDB_ds, "TWS IntraDay") twsTable = CosDB_ds.Tables("TWS IntraDay") twsTableDa.FillSchema(twsTable, SchemaType.Source) 'add a ListTable to Excel listTbl = Globals.Sheet1.Controls.AddListObject(Globals.Sheet1.Range("A2"), "StockTable") 'bind the list table to the DataTable twsTable Next c Hello Ed White, Ed.
Writing Data from a DataTable to Excel - Deborah's Developer MindScape. As with most things in Visual Studio, there are many ways to export data from your .NET application to an Excel spreadsheet. This post covers one straightforward technique. First, set a reference to the desired version of the Microsoft Excel Object Library from the COM tab of the Add Reference dialog. The resulting reference appears as Microsoft.Office.Interop.Excel. Import the Microsoft.Office.Interop.Excel library in your code file.
To keep the namespaces clear, consider using an alias. In C#: using Excel = Microsoft.Office.Interop.Excel; using System.Reflection; NOTE: C# also requires a reference to System.Reflection to use the Missing.Value field. In VB: Imports Excel = Microsoft.Office.Interop.Excel The rest of the code is presented in VB and C# and then discussed below. Excel.Application oXL; Excel.Workbook oWB; Excel.Worksheet oSheet; Excel.Range oRange; // Start Excel and get Application object. oXL = new Excel.Application(); ' Get a new workbook. oWB = oXL.Workbooks.Add Enjoy! Inserting relational data using DataSet and DataAdapter.
Download source files - 20 Kb Introduction Since the introduction of ADO.NET, we started to use a different concept in the development of database-driven applications, the disconnected environment. Most of you are already aware of this change, but this new development model brings some problems that need to be solved, in order to make our application work in a fully disconnected environment. One of the problems I've found when I was developing an application using this approach was the method of updating relational data using a DataSet and a DataAdapter objects. If you update just one DataTable there is no big deal in creating the code for the DataAdapter, but if you work on more than one table, and these tables have a parent-child relationship, the update/insert process can be a little tricky, specially if the parent table has an IDENTITY/AutoNumber column.
In the example I'll use a database with 2 tables, one that holds Order and one that holds OrderDetails. Inserting Data. How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET. This article discusses how you can use ADO.NET to retrieve data from a Microsoft Excel workbook, modify data in an existing workbook, or add data to a new workbook. To access Excel workbooks with ADO.NET, you can use the Jet OLE DB provider; this article provides the information that you need so that you can use the Jet OLE DB provider when Excel is the target data source. How to Use the Jet OLE DB Provider With Microsoft Excel Workbooks The Microsoft Jet database engine can access data in other database file formats, such as Excel workbooks, through installable Indexed Sequential Access Method (ISAM) drivers.
To open external formats supported by the Microsoft Jet 4.0 OLE DB Provider, specify the database type in the extended properties for the connection. Excel 3.0 Excel 4.0 Excel 5.0 Excel 8.0 Connection String To access an Excel workbook by using the Jet OLE DB Provider, use a connection string that has the following syntax: Data Types Table Naming Conventions Retrieve Records.
Could not find installable ISAM. Use VB to Copy dataset from Excel ListObject to a new Excel file. If you are using VB.NET and VSTO and you have a ListObject on top of Excel, you can use ADO.NET to copy the dataset associated with the ListObject to a completely new Excel .xls file like this: Imports System.Data.OleDb Private m_sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\ExcelData2.xls;" & _ "Extended Properties=""Excel 8.0;HDR=YES""" Private Sub MySub 'Let's assume you have an Excel worksheet called Sheet1 and on it is a ListObject and an associated dataset called MyExcelDataset and a bindingsource called Sheet1BindingSource. 'Bank and Checknum. 'Stop any editing going on in the ListObject Me.Sheet1BindingSource.EndEdit() 'This procedure probably only works on new files, so if we did this once already, get rid of the output file. If Dir("C:\ExcelData2.xls") <> "" Then Kill("C:\ExcelData2.xls") 'Create the table inside of the new Excel file.
Dim conn As New OleDbConnection() conn.ConnectionString = m_sConn conn.Open() Dim cmd As New OleDbCommand() cmd.Connection = conn. Excel-DNA. Converting Custom Collections To and From DataTable. Alright, so for our applications we have a couple of forms that use the DataGridView control to display tabular data (kinda of a common scenario for many business applications). The data is returned from our services as an IList<T> in which we can just then bind directly to the grid by using the grid's DataSource property.
Pretty easy task...unless when it comes to sorting. From the looks of it, Ayende has the similar problem that he's trying to overcome... At that point, our IList<T> doesn't cut it. You could say, well, use an BindlingList<T> and override the ApplySortCore method and do it there, but is that work really worth the hassle? (maybe, maybe not...) So, what we decided to was take the easy way out and that is convert our IList<T> to a DataTable object with the correct schema (for the primitive types) that our contained objects have. To see the full code in action, check this sample out: ListObject.SetDataBinding Method (Object) (Microsoft.Office.Tools.Excel)
[This documentation is for preview only, and is subject to change in later releases. Blank topics are included as placeholders.] Namespace:Microsoft.Office.Tools.ExcelAssembly:Microsoft.Office.Tools.Excel (in Microsoft.Office.Tools.Excel.dll) void SetDataBinding( Object dataSource ) The following code example demonstrates how to use the SetDataBinding method to bind a ListObject to a DataTable. The DataTable contains two columns, which contain the names and ages of employees, and four rows that represent employee entries. This example is for a document-level customization. private void ListObject_SetDataBinding() { int[] Ages = { 32, 44, 28, 61 }; string[] Names = { "Reggie", "Sally", "Henry", "Christine" }; // Create a data table with two columns.
VSTO & .NET & Excel. Programming Office Applications. Using Visual Studio Tools for Office to develop Microsoft Office solutions enables rapid application development (RAD). Visual Studio Tools for Office enables you to create document-level customizations and application-level add-ins by using Visual Basic and Visual C#. Provides information on how to program against host items, host controls, and data in documents. It also describes schema-based programming and server-side programming. Describes how to access members of the host item classes from outside of the classes. Explains how to add event handlers to Visual Studio Tools for Office projects using Visual Basic and C#.
Provides an overview of Automation and how you can incorporate Microsoft Office applications into your custom solutions using managed code. Provides key differences between using Visual Basic code and C# code when using Visual Studio Tools for Office to create applications. Describes how to create custom document properties in a document-level customization. Common Tasks in Office Programming. Installing an Add-In in Excel 2007. Wednesday, August 20, 2008 by Jon PeltierPeltier Technical Services, Inc., Copyright © 2014. In Installing an Excel Add-In I described the protocol for installing an add-in in Excel. The protocol I described was valid for Excel 97 through 2003, but the change in user interface which came with Excel 2007 was accompanied by a more tortuous protocol.
It’s not really more complicated once you get used to it, but the first few times it’s an adventure. To install an Excel 2007 add-in, click on the Office Button, the big round decoration in the top left of the Excel window. This opens the Office Menu. The Excel Options dialog opens up. Make sure the Manage dropdown at the bottom shows Excel Add-Ins, then press the Go button. If the add-in has been stored in one of the default add-in directories, it will appear in the list. This long protocol can be shortened in a couple of ways. Click on the left hand dropdown, and choose All Commands.
Scroll down, select Add-Ins, and click the Add button. A Simple Example of a MySQL Stored Procedure that uses a cursor | Kevin Bedell on Internet Tech. (Save this to Del.icio.us!) What is a cursor and when should you use one? A ‘cursor’ in MySQL is essentially just the result set that’s returned from a query. Using a cursor allows you to iterate, or step through, the results of a query and perform certain operations on each row that’s returned. Think of them like holders for any data you might process in a loop. Cursors have some drawbacks and you don’t want to use them everywhere. Another candidate for a cursor is where some steps of the processing are optional and only need to be performed for certain rows.
When should cursors be avoided? Cursors should be avoided generally whenever you can write a SQL statement that processes all rows in a query at once — and you’ll be surprised at how often this is. This is an advantage usually for a couple reasons. MySQL Cursor Sample Code Here’s an example of how to create a MySQL stored procedure that uses a cursor. I ‘over-commented’ the procedure a bit (if there is such a thing!) Conclusion.