VBA code for Excel can only be written inside Excel using the VBA IDE. VBA projects are stored as part of the Excel file and cannot be loaded into Visual Studio. However, you can write VSTO managed add-ins for Excel using Visual Studio. The following MSDN page covers both developing. Python in Visual Studio Code. Working with Python in Visual Studio Code, using the Microsoft Python extension, is simple, fun, and productive. The extension makes VS Code an excellent Python editor, and works on any operating system with a variety of Python interpreters. It leverages all of VS Code's power to provide auto complete.
Note
Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.
Summary
This article discusses numerous methods for transferring data to Microsoft Excel from your Microsoft Visual Basic application. This article also presents the advantages and the disadvantages for each method so that you can choose the solution that works best for you.
More Information
The approach most commonly used to transfer data to an Excel workbook is Automation. Automation gives you the greatest flexibility for specifying the location of your data in the workbook as well as the ability to format the workbook and make various settings at run time. With Automation, you can use several approaches for transferring your data:
- Transfer data cell by cell
- Transfer data in an array to a range of cells
- Transfer data in an ADO recordset to a range of cells using the CopyFromRecordset method
- Create a QueryTable on an Excel worksheet that contains the result of a query on an ODBC or OLEDB data source
- Transfer data to the clipboard and then paste the clipboard contents into an Excel worksheet
There are also methods that you can use to transfer data to Excel that do not necessarily require Automation. If you are running an application server-side, this can be a good approach for taking the bulk of processing the data away from your clients. The following methods can be used to transfer your data without Automation:
- Transfer your data to a tab- or comma-delimited text file that Excel can later parse into cells on a worksheet
- Transfer your data to a worksheet using ADO
- Transfer data to Excel using Dynamic Data Exchange (DDE)
The following sections provide more detail on each of these solutions.
Note When you use Microsoft Office Excel 2007, you can use the new Excel 2007 Workbook (*.xlsx) file format when you save the workbooks. To do this, locate the following line of code in the following code examples:
Replace this code with with the following line of code:
Additionally, the Northwind database is not included in Office 2007 by default. However, you can download the Northwind database from Microsoft Office Online.
Use Automation to transfer data cell by cell
With Automation, you can transfer data to a worksheet one cell at a time:
Transferring data cell by cell can be a perfectly acceptable approach if the amount of data is small. You have the flexibility to place data anywhere in the workbook and can format the cells conditionally at run time. However, this approach is not recommended if you have a large amount of data to transfer to an Excel workbook. Each Range object that you acquire at run time results in an interface request so that transferring data in this manner can be slow. Additionally, Microsoft Windows 95 and Windows 98 have a 64K limitation on interface requests. If you reach or exceed this 64k limit on interface requests, the Automation server (Excel) might stop responding or you might receive errors indicating low memory.
Once more, transferring data cell by cell is acceptable only for small amounts of data. If you need to transfer large data sets to Excel, you should consider one of the solutions presented later.
For more sample code for Automating Excel, see How to automate Microsoft Excel from Visual Basic.
Use automation to transfer an array of data to a range on a worksheet
An array of data can be transferred to a range of multiple cells at once:
If you transfer your data using an array rather than cell by cell, you can realize an enormous performance gain with a large amount of data. Consider this line from the code above that transfers data to 300 cells in the worksheet:
This line represents two interface requests (one for the Range object that the Range method returns and another for the Range object that the Resize method returns). On the other hand, transferring the data cell by cell would require requests for 300 interfaces to Range objects. Whenever possible, you can benefit from transferring your data in bulk and reducing the number of interface requests you make.
Use automation to transfer an ADO recordset to a worksheet range
Excel 2000 introduced the CopyFromRecordset method that allows you to transfer an ADO (or DAO) recordset to a range on a worksheet. The following code illustrates how you could automate Excel 2000, Excel 2002, or Office Excel 2003 and transfer the contents of the Orders table in the Northwind Sample Database using the CopyFromRecordset method.
Note If you use the Office 2007 version of the Northwind database, you must replace the following line of code in the code example:
Replace this line of code with the following line of code:
Excel 97 also provides a CopyFromRecordset method but you can use it only with a DAO recordset. CopyFromRecordset with Excel 97 does not support ADO.
For more information about using ADO and the CopyFromRecordset method, please see the following article in the Microsoft Knowledge Base:
246335 How to transfer data from an ADO recordset to Excel with automation
Use automation to create a QueryTable on a worksheet
A QueryTable object represents a table built from data returned from an external data source. While automating Microsoft Excel, you can create a QueryTable by simply providing a connection string to an OLEDB or an ODBC data source along with an SQL string. Excel assumes the responsibility for generating the recordset and inserting it into the worksheet at the location you specify. Using QueryTables offers several advantages over the CopyFromRecordset method:
- Excel handles the creation of the recordset and its placement into the worksheet.
- The query can be saved with the QueryTable so that it can be refreshed at a later time to obtain an updated recordset.
- When a new QueryTable is added to your worksheet, you can specify that data already existing in cells on the worksheet be shifted to accommodate the new data (see the RefreshStyle property for details).
The following code demonstrates how you could automate Excel 2000, Excel 2002, or Office Excel 2003 to create a new QueryTable in an Excel worksheet using data from the Northwind Sample Database:
Use the clipboard
The Windows Clipboard can also be used as a mechanism for transferring data to a worksheet. To paste data into multiple cells on a worksheet, you can copy a string where columns are delimited by tab characters and rows are delimited by carriage returns. The following code illustrates how Visual Basic can use its Clipboard object to transfer data to Excel:
Create a delimited text file that Excel can parse into rows and columns
Excel can open tab- or comma-delimited files and correctly parse the data into cells. You can take advantage of this feature when you want to transfer a large amount of data to a worksheet while using little, if any, Automation. This might be a good approach for a client-server application because the text file can be generated server-side. You can then open the text file at the client, using Automation where it is appropriate.
The following code illustrates how you can create a comma-delimited text file from an ADO recordset:
Note If you use the Office 2007 version of the Northwind database, you must replace the following line of code in the code example:
Replace this line of code with the following line of code:
If your text file has a .CSV extension, Excel opens the file without displaying the Text Import Wizard and automatically assumes that the file is comma-delimited. Similarly, if your file has a .TXT extension, Excel automatically parse the file using tab delimiters.
In the previous code sample, Excel was launched using the Shell statement and the name of the file was used as a command line argument. No Automation was used in the previous sample. However, if so desired, you could use a minimal amount of Automation to open the text file and save it in the Excel workbook format:
Transfer data to a worksheet by using ADO
Using the Microsoft Jet OLE DB Provider, you can add records to a table in an existing Excel workbook. A 'table' in Excel is merely a range with a defined name. The first row of the range must contain the headers (or field names) and all subsequent rows contain the records. The following steps illustrate how you can create a workbook with an empty table named MyTable.
Excel 97, Excel 2000, and Excel 2003
Start a new workbook in Excel.
Add the following headers to cells A1:B1 of Sheet1:
A1: FirstName B1: LastName
Format cell B1 as right-aligned.
Select A1:B1.
On the Insert menu, choose Names and then select Define. Enter the name MyTable and click OK.
Save the new workbook as C:Book1.xls and quit Excel.
To add records to MyTable using ADO, you can use code similar to the following:
Excel 2007
In Excel 2007, start a new workbook.
Add the following headers to cells A1:B1 of Sheet1:
A1: FirstName B1: LastName
Format cell B1 as right-aligned.
Select A1:B1.
On the Ribbon, click the Formulas tab, and then click Define Name. Type the name MyTable, and then click OK.
Save the new workbook as C:Book1.xlsx, and then quit Excel.
To add records to the MyTable table by using ADO, use code that resembles the following code example.
When you add records to the table in this manner, the formatting in the workbook is maintained. In the previous example, new fields added to column B are formatted with right alignment. Each record that is added to a row borrows the format from the row above it.
You should note that when a record is added to a cell or cells in the worksheet, it overwrites any data previously in those cells; in other words, rows in the worksheet are not 'pushed down' when new records are added. You should keep this in mind when designing the layout of data on your worksheets.
Note
The method to update data in an Excel worksheet by using ADO or by using DAO does not work in Visual Basic for Application environment within Access after you install Office 2003 Service Pack 2 (SP2) or after you install the update for Access 2002 that is included in Microsoft Knowledge Base article 904018. The method works well in Visual Basic for Application environment from other Office applications, such as Word, Excel, and Outlook.
For more information, click the following article numbers to view the article in the Microsoft Knowledge Base: 904953 You cannot change, add, or delete data in tables that are linked to an Excel workbook in Office Access 2003 or in Access 2002
For additional information on using ADO to access an Excel workbook, seeHow To Query and Update Excel Data Using ADO From ASP.
Use DDE to transfer data to Excel
DDE is an alternative to Automation as a means for communicating with Excel and transferring data; however, with the advent of Automation and COM, DDE is no longer the preferred method for communicating with other applications and should only be used when there is no other solution available to you.
To transfer data to Excel using DDE, you can use the LinkPoke method to poke data to a specific range of cell(s), or you use the LinkExecute method to send commands that Excel will execute.
The following code example illustrates how to establish a DDE conversation with Excel so that you can poke data to cells on a worksheet and execute commands. Using this sample, for a DDE conversation to be successfully established to the LinkTopic Excel|MyBook.xls, a workbook with the name MyBook.xls must already be opened in a running instance of Excel.
Note
When you use Excel 2007, you can use the new .xlsx file format to save the workbooks. Make sure that you update the file name in the following code example.
Note In this example, Text1 represents a Text Box control on a Visual Basic form:
When using LinkPoke with Excel, you specify the range in row-column (R1C1) notation for the LinkItem. If you are poking data to multiple cells, you can use a string where the columns are delimited by tabs and rows are delimited by carriage returns.
When you use LinkExecute to ask Excel to carry out a command, you must give Excel the command in the syntax of the Excel Macro Language (XLM). The XLM documentation is not included with Excel versions 97 and later. DDE is not a recommended solution for communicating with Excel. Automation provides the greatest flexibility and gives you more access to the new features that Excel has to offer.
VB.NET Excel examples
On this page you will find a set of HowTo samples that can help you to get started with add-in development for Excel 2019, 2016, 2013, 2010 and lower:
And here are a few more VB.NET examples for other applications of the Microsoft Office 2019 - 2000 suite:
Excel extensions: COM add-in, RTD server
How to develop a COM add-in for Microsoft Office (Excel, Word and PowerPoint)
This sample shared COM add-in for Excel, Word and PowerPoint shows how you can use Add-in Express for Office and .net to add custom command bars and command bar controls, place new controls onto Office Ribbon tabs, create custom task panes, handle application-level events and more.
Download COM addin
How to customize Microsoft Excel ribbon and toolbar
See how to create custom ribbons and toolbars for Excel and how to integrate with an existing Excel tab.
Download add-in
How to customize Excel main menu, context menus and Backstage view
VB.NET sample shows how to create a custom context menu for Excel 2019, 2016, 2013, 2010, 2007 and lower; how to add your own Backstage view item and how to customize the main menu in Excel 2003 - 2000.
Download sample
How to build your first Excel RTD server in VB.NET
See how to build an RTD server step-by-step in the Developer's Guide. The RTD server project is written in Visual Basic .NET. See also a video showing how to create an RTD server.
Download RTD server
Working with Excel Object model
Visual Studio Office Add In
How to handle the WorkbookBeforeSave event
How to create a custom event when Excel calculation mode changes
This VB.NET Excel addin example provides a solution for missing event that should occur then the user changes the calculation mode.
Download addin
How to find the last used cell (row or column) in Excel
This Visual Basic sample explains some reliable methods for finding the last used cell in an Excel worksheet or a range.
Download Find Last Cell add-in
How to populate Excel workbooks and ranges with arrays
VB.NET code example shows how to use arrays to populate Excel with data; how to fill array with a query or range and insert into a worksheet, and more.
Download Populate with Arrays add-in
How to work with Excel tables & ranges using VB.NET
This sample Excel addin demonstrates how work with tables and ranges: create a new table or a range, insert a column or a row, sort, filter and more.
Download Excel Tables and Ranges add-in
How to work with Excel pivot tables
VB.NET code example shows how to automate pivot tables in Excel: create a PivotTable, add calculated fields, display or hide a field; delete, refresh or clear a pivot table; create a pivot chart and more.
Download Excel PivotTables Add-in
Advanced Excel Task Panes
How to show and hide Excel Task Panes programmatically
This Visual Basic .NET Excel sample demonstrates how to set the visibility of an advanced task pane to make it show up from the command bar.
Download VB.NET sample
Excel Visual Studio Add In
How to switch between several task panes programmatically
This addin shows how you can use several custom task panes in one position and switch between them programmatically.
Download example
How to show an advanced Excel task pane dynamically
The Excel VB.NET add-in shows how to build a context-dependent custom task pane. The plug-in shows a task pane when cell A1 contains any value.
Download plug-in
How to re-size an Excel task pane in VB.NET
This Visual Basic .NET example shows how you can resize your custom form using Advanced Task Panes for Excel 2019 - 2000. To change the form size programmatically, you set the splitter to none. Otherwise, only the user can resize the task pane using the splitter.
Download sample plugin
User-defined functions
How to develop Excel Automation add-in
See how to create Excel Automation add-ins step-by-step in the Developer's Guide. The sample is written in VB.NET.
Download Automation add-in
How to develop XLL add-in
This sample project demonstrates how you can build an XLL addin in Visual Studio. You can find a detail description of this example in the Developer's Guide: How to create an XLL add-in.
Download XLL addin
Note. You can find plenty more HowTo examples on our technical blog. Be sure to check it out, we add new HowTo samples every week.