{"id":177,"date":"2013-04-08T17:58:16","date_gmt":"2013-04-08T22:58:16","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=177"},"modified":"2013-04-08T17:58:16","modified_gmt":"2013-04-08T22:58:16","slug":"how-to-transfer-data-to-an-excel-workbook-by-using-visual-basic-net","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2013\/04\/08\/how-to-transfer-data-to-an-excel-workbook-by-using-visual-basic-net\/","title":{"rendered":"How to transfer data to an Excel workbook by using Visual Basic .NET"},"content":{"rendered":"<div id=\"mt_article_properties\" translate=\"false\">Article ID: 306022 &#8211;\u00a0<a href=\"http:\/\/support.microsoft.com\/kb\/306022#appliesto\">View products that this article applies to.<\/a><\/div>\n<div>This article was previously published under Q306022<\/div>\n<div>\n<div>For a Microsoft Visual C# .NET version of this article, see\u00a0<a href=\"http:\/\/support.microsoft.com\/kb\/306023\">306023<\/a>.<\/div>\n<\/div>\n<div id=\"kb_section\">\n<div id=\"kb_expandcollapseall\" translate=\"false\"><a id=\"expandAll\"><\/a>Expand all\u00a0|\u00a0<a id=\"collapseAll\"><\/a>Collapse all<\/div>\n<h2 id=\"tocHeadRef\" translate=\"false\"><img decoding=\"async\" alt=\"\" src=\"http:\/\/support.microsoft.com\/library\/images\/support\/en-us\/20x20_grey_plus.png\" \/><a id=\"mt_toc_title\"><\/a>On This Page<\/h2>\n<h2 id=\"tocHeadRef\" translate=\"false\"><img decoding=\"async\" alt=\"\" src=\"http:\/\/support.microsoft.com\/library\/images\/support\/en-us\/20x20_grey_minus.png\" \/><a>SUMMARY<\/a><\/h2>\n<div id=\"MT0\">This step-by-step article describes several methods for transferring data to Excel 2002 from a Visual Basic .NET program. This article also presents the advantages and disadvantages of each method so that you can select the solution that works best for your situation.<\/p>\n<h3 id=\"tocHeadRef\">Overview<\/h3>\n<p>The technique that is used most frequently to transfer data to an Excel workbook is\u00a0<i>Automation<\/i>. With Automation, you can call methods and properties that are specific to Excel tasks. Automation gives you the greatest flexibility for specifying the location of your data in the workbook, and the ability to format the workbook and make various settings at run time.<\/p>\n<p>With Automation, you can use several techniques to transfer your data:<\/p>\n<ul>\n<li>Transfer data cell by cell.<\/li>\n<li>Transfer data in an array to a range of cells.<\/li>\n<li>Transfer data in an ADO recordset to a range of cells by using the\u00a0<b>CopyFromRecordset<\/b>\u00a0method.<\/li>\n<li>Create a\u00a0<b>QueryTable<\/b>\u00a0object on an Excel worksheet that contains the result of a query on an ODBC or OLEDB data source.<\/li>\n<li>Transfer data to the clipboard, and then paste the clipboard contents into an Excel worksheet.<\/li>\n<\/ul>\n<p>You can also use several methods that do not necessarily require Automation to transfer data to Excel. If you are running a server-side program, this can be a good approach for taking the bulk of data processing away from your clients.<\/p>\n<p>The following approaches may be used to transfer your data without Automation:<\/p>\n<ul>\n<li>Transfer your data to a tab- or comma-delimited text file that Excel can later parse into cells on a worksheet.<\/li>\n<li>Transfer your data to a worksheet using ADO.NET.<\/li>\n<li>Transfer XML data to Excel (version 2002 only) to provide data that is formatted and arranged into rows and columns.<\/li>\n<\/ul>\n<h3 id=\"tocHeadRef\">Techniques<\/h3>\n<h4 id=\"tocHeadRef\">Use Automation to transfer data cell by cell<\/h4>\n<p>With Automation, you can transfer data to a worksheet one cell at a time, as follows.<\/p>\n<div>\n<div>\n<pre>        Dim oExcel As Object\n        Dim oBook As Object\n        Dim oSheet As Object\n\n        'Start a new workbook in Excel.\n        oExcel = CreateObject(\"Excel.Application\")\n        oBook = oExcel.Workbooks.Add\n\n        'Add data to cells of the first worksheet in the new workbook.\n        oSheet = oBook.Worksheets(1)\n        oSheet.Range(\"A1\").Value = \"Last Name\"\n        oSheet.Range(\"B1\").Value = \"First Name\"\n        oSheet.Range(\"A1:B1\").Font.Bold = True\n        oSheet.Range(\"A2\").Value = \"Doe\"\n        oSheet.Range(\"B2\").Value = \"John\"\n\n        'Save the Workbook and quit Excel.\n        oBook.SaveAs(sSampleFolder &amp; \"Book1.xls\")\n        oSheet = Nothing\n        oBook = Nothing\n        oExcel.Quit()\n        oExcel = Nothing\n        GC.Collect()<\/pre>\n<\/div>\n<\/div>\n<p>Transferring data cell by cell can be an acceptable approach if there is not much data to transfer. You have the flexibility to put data anywhere in the workbook and can format the cells conditionally at run time. However, this approach is not recommended if you have a lot of data to transfer to an Excel workbook. Each\u00a0<b>Range<\/b>\u00a0object that you acquire at run time results in an interface request. Therefore, transferring data in this manner can be slow.<br \/>\nAdditionally, Microsoft Windows 95, Microsoft Windows 98, and Microsoft Windows Millennium Edition (Me) have a 64 KB limitation on interface requests. If you have 64 KB or more of interface requests, the Automation server (Excel) may stop responding, or you may receive error messages that indicate low memory. For more information, click the following article number to view the article in the Microsoft Knowledge Base:<\/p>\n<div><a href=\"http:\/\/support.microsoft.com\/kb\/216400\">216400<\/a>\u00a0Cross-process COM Automation can hang client application on Win95\/98<\/div>\n<p>Again, transferring data cell by cell is acceptable only for small amounts of data. If you must transfer large data sets to Excel, consider using one of the other approaches that are discussed in this article to transfer data in bulk.<\/p>\n<p>For more information, and for an example of how to automate Excel with Visual Basic .NET, click the following article number to view the article in the Microsoft Knowledge Base:<\/p>\n<div><a href=\"http:\/\/support.microsoft.com\/kb\/301982\">301982<\/a>\u00a0How to automate Microsoft Excel from Visual Basic .NET<\/div>\n<h4 id=\"tocHeadRef\">Use Automation to transfer an array of data to a range on a worksheet<\/h4>\n<p>An array of data can be transferred to a range of multiple cells at the same time, as follows.<\/p>\n<div>\n<div>\n<pre>        Dim oExcel As Object\n        Dim oBook As Object\n        Dim oSheet As Object\n\n        'Start a new workbook in Excel.\n        oExcel = CreateObject(\"Excel.Application\")\n        oBook = oExcel.Workbooks.Add\n\n        'Create an array with 3 columns and 100 rows.\n        Dim DataArray(99, 2) As Object\n        Dim r As Integer\n        For r = 0 To 99\n            DataArray(r, 0) = \"ORD\" &amp; Format(r + 1, \"0000\")\n            DataArray(r, 1) = Rnd() * 1000\n            DataArray(r, 2) = DataArray(r, 1) * 0.07\n        Next\n\n        'Add headers to the worksheet on row 1.\n        oSheet = oBook.Worksheets(1)\n        oSheet.Range(\"A1\").Value = \"Order ID\"\n        oSheet.Range(\"B1\").Value = \"Amount\"\n        oSheet.Range(\"C1\").Value = \"Tax\"\n\n        'Transfer the array to the worksheet starting at cell A2.\n        oSheet.Range(\"A2\").Resize(100, 3).Value = DataArray\n\n        'Save the Workbook and quit Excel.\n        oBook.SaveAs(sSampleFolder &amp; \"Book2.xls\")\n        oSheet = Nothing\n        oBook = Nothing\n        oExcel.Quit()\n        oExcel = Nothing\n        GC.Collect()<\/pre>\n<\/div>\n<\/div>\n<p>If you transfer data by using an array instead of cell by cell, you can realize an enormous performance gain with a lot of data. Consider this line from the earlier code, which transfers data to 300 cells in the worksheet.<\/p>\n<div>\n<div>\n<pre>        oSheet.Range(\"A2\").Resize(100, 3).Value = DataArray<\/pre>\n<\/div>\n<\/div>\n<p>This line represents two interface requests: one for the\u00a0<b>Range<\/b>\u00a0object that the\u00a0<b>Range<\/b>\u00a0method returns, and another for the<b>Range<\/b>\u00a0object that the\u00a0<b>Resize<\/b>\u00a0method returns. In contrast, transferring the data cell by cell requires requests for 300 interfaces to\u00a0<b>Range<\/b>\u00a0objects. Whenever possible, you can benefit from transferring your data in bulk and reducing the number of interface requests you make.<\/p>\n<h4 id=\"tocHeadRef\">Use Automation to transfer an ADO recordset to a worksheet range<\/h4>\n<p>The object models for Excel 2000 and Excel 2002 provide the\u00a0<b>CopyFromRecordset<\/b>\u00a0method for transferring an ADO recordset to a range on a worksheet. The following code illustrates how to automate Excel to transfer the contents of the Orders table in the Northwind sample database by using the\u00a0<b>CopyFromRecordset<\/b>\u00a0method.<\/p>\n<div>\n<div>\n<pre>        'Create a Recordset from all the records in the Orders table.\n        Dim sNWind As String\n        Dim conn As New ADODB.Connection()\n        Dim rs As ADODB.Recordset\n        conn.Open(\"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" &amp; _\n            sNorthwind &amp; \";\")\n        conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient\n        rs = conn.Execute(\"Orders\", , ADODB.CommandTypeEnum.adCmdTable)\n\n        'Create a new workbook in Excel.\n        Dim oExcel As Object\n        Dim oBook As Object\n        Dim oSheet As Object\n        oExcel = CreateObject(\"Excel.Application\")\n        oBook = oExcel.Workbooks.Add\n        oSheet = oBook.Worksheets(1)\n\n        'Transfer the field names to Row 1 of the worksheet:\n        'Note: CopyFromRecordset copies only the data and not the field\n        '      names, so you can transfer the fieldnames by traversing the\n        '      fields collection.\n        Dim n As Int32\n        For n = 1 To rs.Fields.Count\n            oSheet.Cells(1, n).Value = rs.Fields(n - 1).Name\n        Next\n\n        'Transfer the data to Excel.\n        oSheet.Range(\"A2\").CopyFromRecordset(rs)\n\n        'Save the workbook and quit Excel.\n        oBook.SaveAs(sSampleFolder &amp; \"Book3.xls\")\n        oSheet = Nothing\n        oBook = Nothing\n        oExcel.Quit()\n        oExcel = Nothing\n        GC.Collect()\n\n        'Close the connection\n        rs.Close()\n        conn.Close()<\/pre>\n<\/div>\n<\/div>\n<p><b>Note\u00a0<\/b><b>CopyFromRecordset<\/b>\u00a0works only with ADO\u00a0<b>Recordset<\/b>\u00a0objects. A\u00a0<b>DataSet<\/b>\u00a0that you create by using ADO.NET cannot be used with the\u00a0<b>CopyFromRecordset<\/b>\u00a0method. Several examples in the sections that follow demonstrate how to transfer data to Excel with ADO.NET.<\/p>\n<h4 id=\"tocHeadRef\">Use Automation to create a QueryTable object on a worksheet<\/h4>\n<p>A\u00a0<b>QueryTable<\/b>\u00a0object represents a table that is built from data that is returned from an external data source. While you automate Excel, you can create a\u00a0<b>QueryTable<\/b>\u00a0by providing a connection string to an OLEDB or an ODBC data source and a SQL string. Excel generates the recordset and inserts the recordset into the worksheet at the location that you specify. Using<b>QueryTable<\/b>\u00a0objects offers the following advantages over the\u00a0<b>CopyFromRecordset<\/b>\u00a0method:<\/p>\n<ul>\n<li>Excel handles the creation of the recordset and its placement into the worksheet.<\/li>\n<li>The query can be saved with the\u00a0<b>QueryTable<\/b>\u00a0object so that it can be refreshed later to obtain an updated recordset.<\/li>\n<li>When a new\u00a0<b>QueryTable<\/b>\u00a0is added to your worksheet, you can specify that data already existing in cells on the worksheet be shifted to fit the new data (see the\u00a0<b>RefreshStyle<\/b>\u00a0property for details).<\/li>\n<\/ul>\n<p>The following code demonstrates how to automate Excel 2000 or 2002 to create a new\u00a0<b>QueryTable<\/b>\u00a0in an Excel worksheet by using data from the Northwind sample database.<\/p>\n<div>\n<div>\n<pre>        'Create a new workbook in Excel.\n        Dim oExcel As Object\n        Dim oBook As Object\n        Dim oSheet As Object\n        oExcel = CreateObject(\"Excel.Application\")\n        oBook = oExcel.Workbooks.Add\n        oSheet = oBook.Worksheets(1)\n\n        'Create the QueryTable object.\n        Dim oQryTable As Object\n        oQryTable = oSheet.QueryTables.Add( _\n        \"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" &amp; _\n            sNorthwind &amp; \";\", oSheet.Range(\"A1\"), _\n            \"Select * from Orders\")\n        oQryTable.RefreshStyle = 2 ' xlInsertEntireRows = 2\n        oQryTable.Refresh(False)\n\n        'Save the workbook and quit Excel.\n        oBook.SaveAs(sSampleFolder &amp; \"Book4.xls\")\n        oQryTable = Nothing\n        oSheet = Nothing\n        oBook = Nothing\n        oExcel.Quit()\n        oExcel = Nothing<\/pre>\n<\/div>\n<\/div>\n<h4 id=\"tocHeadRef\">Use the Clipboard<\/h4>\n<p>You can use the Clipboard to transfer data to a worksheet. To paste data into multiple cells on a worksheet, you can copy a string in which columns are delimited by tab characters, and rows are delimited by carriage returns. The following code illustrates how Visual Basic .NET uses the Clipboard to transfer data to Excel.<\/p>\n<div>\n<div>\n<pre>        'Copy a string to the Clipboard.\n        Dim sData As String\n        sData = \"FirstName\" &amp; vbTab &amp; \"LastName\" &amp; vbTab &amp; \"Birthdate\" &amp; vbCr _\n            &amp; \"Bill\" &amp; vbTab &amp; \"Brown\" &amp; vbTab &amp; \"2\/5\/85\" &amp; vbCr _\n            &amp; \"Joe\" &amp; vbTab &amp; \"Thomas\" &amp; vbTab &amp; \"1\/1\/91\"\n        System.Windows.Forms.Clipboard.SetDataObject(sData)\n\n        'Create a workbook in Excel.\n        Dim oExcel As Object\n        Dim oBook As Object\n        oExcel = CreateObject(\"Excel.Application\")\n        oBook = oExcel.Workbooks.Add\n\n        'Paste the data.\n        oBook.Worksheets(1).Range(\"A1\").Select()\n        oBook.Worksheets(1).Paste()\n\n        'Save the workbook and quit Excel.\n        oBook.SaveAs(sSampleFolder &amp; \"Book5.xls\")\n        oBook = Nothing\n        oExcel.Quit()\n        oExcel = Nothing\n        GC.Collect()<\/pre>\n<\/div>\n<\/div>\n<h4 id=\"tocHeadRef\">Create a delimited text file that Excel can parse into rows and columns<\/h4>\n<p>Excel can open tab-delimited files or comma-delimited files and correctly parse the data into cells. You can use this feature when you want to transfer a lot of data to a worksheet while using little, if any, Automation. This may be a good approach for a client-server program, 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.<\/p>\n<p>The following code illustrates how to generate a tab-delimited text file from data that is read with ADO.NET.<\/p>\n<div>\n<div>\n<pre>        'Connect to the data source.\n        Dim objConn As New System.Data.OleDb.OleDbConnection( _\n            \"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" &amp; sNorthwind &amp; \";\")\n        objConn.Open()\n\n        'Execute a command to retrieve all records from the Employees table.\n        Dim objCmd As New System.Data.OleDb.OleDbCommand( _\n            \"Select * From Employees\", objConn)\n        Dim objReader As System.Data.OleDb.OleDbDataReader\n        objReader = objCmd.ExecuteReader()\n\n        'Read the records in the dataset and write select fields to the \n        'output file.\n        FileOpen(1, sSampleFolder &amp; \"Book6.txt\", OpenMode.Output)\n        Dim i As Integer, s As String\n        While objReader.Read()\n            'Loop through first 6 fields and concatenate\n            'each field, separated by a tab, into s variable.\n            s = \"\"\n            For i = 0 To 5\n                If Not objReader.IsDBNull(i) Then\n                    If i = 0 Then 'field 1 is EmployeeId\n                        s = s &amp; objReader.GetInt32(i).ToString\n                    ElseIf i = 5 Then 'field 6 is BirthDate\n                        s = s &amp; objReader.GetDateTime(i)\n                    Else 'field is a text field\n                        s = s &amp; objReader.GetString(i)\n                    End If\n                End If\n                s = s &amp; Microsoft.VisualBasic.ControlChars.Tab\n            Next\n            PrintLine(1, s)\n        End While\n        FileClose(1)\n\n        'Close the reader and the connection.\n        objReader.Close()\n        objConn.Close()<\/pre>\n<\/div>\n<\/div>\n<p>No Automation was used in the previous code. However, you can use minimal Automation to open the text file and save the file in the Excel workbook format, as follows.<\/p>\n<div>\n<div>\n<pre>        'Create a new instance of Excel.\n        Dim oExcel As Object\n        oExcel = CreateObject(\"Excel.Application\")\n\n        'Open the text file and save it in the Excel workbook format.\n        oExcel.Workbooks.OpenText(sSampleFolder &amp; \"Book6.txt\", _\n            , , , -4142, , True) 'xlTextQualifierNone=-4142\n\n        oExcel.ActiveWorkbook.SaveAs(sSampleFolder &amp; \"Book6.xls\", _\n            -4143) 'xlWorkbookNormal = -4143\n\n        'Quit Excel.\n        oExcel.Quit()\n        oExcel = Nothing\n        GC.Collect()<\/pre>\n<\/div>\n<\/div>\n<h4 id=\"tocHeadRef\">Transfer data to a worksheet by using ADO.NET<\/h4>\n<p>You can use the Microsoft Jet OLE DB provider to add records to a table in an existing Excel workbook. A &#8220;table&#8221; in Excel is merely a range of cells; the range may have a defined name. Typically, the first row of the range contains the headers (or field names), and all later rows in the range contain the records.<\/p>\n<p>The following code adds two new records to a table in Book7.xls. The table in this case is Sheet1.<\/p>\n<div>\n<div>\n<pre>       'Establish a connection to the data source.\n        Dim sConnectionString As String\n        sConnectionString = \"Provider=Microsoft.Jet.OLEDB.4.0;\" &amp; _\n            \"Data Source=\" &amp; sSampleFolder &amp; _\n            \"Book7.xls;Extended Properties=Excel 8.0;\"\n        Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)\n        objConn.Open()\n\n        'Add two records to the table.\n        Dim objCmd As New System.Data.OleDb.OleDbCommand()\n        objCmd.Connection = objConn\n        objCmd.CommandText = \"Insert into [Sheet1$] (FirstName, LastName)\" &amp; _\n            \" values ('Bill', 'Brown')\"\n        objCmd.ExecuteNonQuery()\n        objCmd.CommandText = \"Insert into [Sheet1$] (FirstName, LastName)\" &amp; _\n            \" values ('Joe', 'Thomas')\"\n        objCmd.ExecuteNonQuery()\n\n        'Close the connection.\n        objConn.Close()<\/pre>\n<\/div>\n<\/div>\n<p>When you add records with ADO.NET as shown, the formatting in the workbook is maintained. Each record that is added to a row borrows the format from the row before it. For example, new fields that are added to column B are formatted with right alignment because cell B1 is right-aligned.<\/p>\n<p>Note that when a record is added to a cell or cells in the worksheet, it overwrites any data that those cells previously contained. In other words, rows in the worksheet are not &#8220;pushed down&#8221; when new records are added. Keep this in mind when you design the layout of data on your worksheets if you plan to the insert new records by using ADO.NET.<\/p>\n<p>For more information about how to use ADO.NET, click the following article numbers to view the articles in the Microsoft Knowledge Base:<\/p>\n<div><a href=\"http:\/\/support.microsoft.com\/kb\/301075\">301075<\/a>\u00a0How to connect to a database and run a command by using ADO.NET and Visual Basic .NET<\/div>\n<div><a href=\"http:\/\/support.microsoft.com\/kb\/301216\">301216<\/a>\u00a0How to populate a DataSet object from a database by using Visual Basic .NET<\/div>\n<div><a href=\"http:\/\/support.microsoft.com\/kb\/301248\">301248<\/a>\u00a0How to update a database from a DataSet object by using Visual Basic .NET<\/div>\n<p>For more information about how to use the Jet OLE DB provider with Excel data sources, click the following article number to view the article in the Microsoft Knowledge Base:<\/p>\n<div><a href=\"http:\/\/support.microsoft.com\/kb\/278973\">278973<\/a>\u00a0ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks<\/div>\n<div><a href=\"http:\/\/support.microsoft.com\/kb\/257819\">257819<\/a>\u00a0How to use ADO with Excel data from Visual Basic or VBA<\/div>\n<h4 id=\"tocHeadRef\">Transfer XML data (Excel 2002 only)<\/h4>\n<p>Excel 2002 can open any XML file that is well-formed. XML files can be opened directly from the\u00a0<strong>Open<\/strong>\u00a0command on the\u00a0<strong>File<\/strong>menu, or programmatically by using either the\u00a0<b>Open<\/b>\u00a0or\u00a0<b>OpenXML<\/b>\u00a0methods of the\u00a0<b>Workbooks<\/b>\u00a0collection. If you create XML files for use in Excel, you can also create style sheets to format the data.<\/p>\n<p>For more information about how to use XML with Excel 2002, click the following article numbers to view the articles in the Microsoft Knowledge Base:<\/p>\n<div><a href=\"http:\/\/support.microsoft.com\/kb\/307021\">307021<\/a>\u00a0How to transfer XML data to Microsoft Excel 2002 by using Visual Basic .NET<\/div>\n<div><a href=\"http:\/\/support.microsoft.com\/kb\/288215\">288215<\/a>\u00a0Microsoft Excel 2002 and XML<\/div>\n<h3 id=\"tocHeadRef\">Create the complete sample Visual Basic .NET project<\/h3>\n<ol>\n<li>Create a new folder to hold the Excel workbooks that the sample will create for you, and then name the folder C:\\Exceldata\\.<\/li>\n<li>Follow these steps to create a new workbook for the sample to write to:\n<ol>\n<li>Start a new workbook in Excel.<\/li>\n<li>On Sheet1 of the new workbook, type\u00a0FirstName\u00a0in cell A1 and\u00a0LastName\u00a0in cell A2.<\/li>\n<li>Save the workbook as C:\\Exceldata\\Book7.xls.<\/li>\n<\/ol>\n<\/li>\n<li>Start Visual Studio .NET. On the\u00a0<strong>File<\/strong>\u00a0menu, click\u00a0<strong>New<\/strong>\u00a0and then click\u00a0<strong>Project<\/strong>. Under\u00a0<strong>Visual Basic Projects<\/strong>, select<strong>Windows Application<\/strong>. By default, Form1 is created.<\/li>\n<li>Add a reference to the Excel object library. To do this, follow these steps:\n<ol>\n<li>On the\u00a0<strong>Project<\/strong>\u00a0menu, click\u00a0<strong>Add Reference<\/strong>.<\/li>\n<li>On the\u00a0<strong>COM<\/strong>\u00a0tab, locate\u00a0<strong>Microsoft Excel 10.0 Object Library<\/strong>, and then click\u00a0<strong>Select<\/strong>.\n<p><b>Note<\/b>\u00a0If you have not already done so, Microsoft recommends that you download and then install the Microsoft Office XP Primary Interop Assemblies (PIAs). For more information about Office XP PIAs, click the following article number to view the article in the Microsoft Knowledge Base:<\/p>\n<div><a href=\"http:\/\/support.microsoft.com\/kb\/328912\">328912<\/a>\u00a0Microsoft Office XP primary interop assemblies (PIAs) are available for download<\/div>\n<\/li>\n<li>On the\u00a0<strong>COM<\/strong>\u00a0tab, locate\u00a0<strong>Microsoft ActiveX Data Objects 2.7 Library<\/strong>, and then click\u00a0<strong>Select<\/strong>.<\/li>\n<li>Click\u00a0<strong>OK<\/strong>\u00a0in the\u00a0<strong>Add References<\/strong>\u00a0dialog box to accept your selections. If you receive a prompt to generate wrappers for the libraries that you selected, click\u00a0<strong>Yes<\/strong>.<\/li>\n<\/ol>\n<\/li>\n<li>Add a\u00a0<b>Combo Box<\/b>\u00a0control and a\u00a0<b>Button<\/b>\u00a0control to Form1.<\/li>\n<li>Add the following code to Form1.\n<div>\n<div>\n<pre>    Const sSampleFolder = \"C:\\ExcelData\\\"\n    Const sNorthwind = \"C:\\Program Files\\Microsoft Office\\Office10\\Samples\\Northwind.mdb\"\n\n    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _\n      Handles MyBase.Load\n        ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList\n        Dim aList As String() = _\n            {\"Use Automation to Transfer Data Cell by Cell \", _\n             \"Use Automation to Transfer an Array of Data to a Range on a Worksheet \", _\n             \"Use Automation to Transfer an ADO Recordset to a Worksheet Range \", _\n             \"Use Automation to Create a QueryTable on a Worksheet\", _\n             \"Use the Clipboard\", _\n             \"Create a Delimited Text File that Excel Can Parse into Rows and Columns\", _\n             \"Transfer Data to a Worksheet Using ADO.NET \"}\n        ComboBox1.Items.AddRange(aList)\n        ComboBox1.SelectedIndex = 0\n        Button1.Text = \"Go!\"\n    End Sub\n\n    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _\n      Handles Button1.Click\n        Select Case ComboBox1.SelectedIndex\n            Case 0 : Automation_CellByCell()\n            Case 1 : Automation_UseArray()\n            Case 2 : Automation_ADORecordset()\n            Case 3 : Automation_QueryTable()\n            Case 4 : Use_Clipboard()\n            Case 5 : Create_TextFile()\n            Case 6 : Use_ADONET()\n        End Select\n        GC.Collect()\n    End Sub\n\n    Private Function Automation_CellByCell()\n        Dim oExcel As Object\n        Dim oBook As Object\n        Dim oSheet As Object\n\n        'Start a new workbook in Excel.\n        oExcel = CreateObject(\"Excel.Application\")\n        oBook = oExcel.Workbooks.Add\n\n        'Add data to cells of the first worksheet in the new workbook.\n        oSheet = oBook.Worksheets(1)\n        oSheet.Range(\"A1\").Value = \"Last Name\"\n        oSheet.Range(\"B1\").Value = \"First Name\"\n        oSheet.Range(\"A1:B1\").Font.Bold = True\n        oSheet.Range(\"A2\").Value = \"Doe\"\n        oSheet.Range(\"B2\").Value = \"John\"\n\n        'Save the workbook and quit Excel.\n        oBook.SaveAs(sSampleFolder &amp; \"Book1.xls\")\n        oSheet = Nothing\n        oBook = Nothing\n        oExcel.Quit()\n        oExcel = Nothing\n        GC.Collect()\n    End Function\n\n    Private Function Automation_UseArray()\n        Dim oExcel As Object\n        Dim oBook As Object\n        Dim oSheet As Object\n\n        'Start a new workbook in Excel.\n        oExcel = CreateObject(\"Excel.Application\")\n        oBook = oExcel.Workbooks.Add\n\n        'Create an array with 3 columns and 100 rows.\n        Dim DataArray(99, 2) As Object\n        Dim r As Integer\n        For r = 0 To 99\n            DataArray(r, 0) = \"ORD\" &amp; Format(r + 1, \"0000\")\n            DataArray(r, 1) = Rnd() * 1000\n            DataArray(r, 2) = DataArray(r, 1) * 0.07\n        Next\n\n        'Add headers to the worksheet on row 1.\n        oSheet = oBook.Worksheets(1)\n        oSheet.Range(\"A1\").Value = \"Order ID\"\n        oSheet.Range(\"B1\").Value = \"Amount\"\n        oSheet.Range(\"C1\").Value = \"Tax\"\n\n        'Transfer the array to the worksheet starting at cell A2.\n        oSheet.Range(\"A2\").Resize(100, 3).Value = DataArray\n\n        'Save the workbook and quit Excel.\n        oBook.SaveAs(sSampleFolder &amp; \"Book2.xls\")\n        oSheet = Nothing\n        oBook = Nothing\n        oExcel.Quit()\n        oExcel = Nothing\n        GC.Collect()\n    End Function\n\n    Private Function Automation_ADORecordset()\n        'Create a Recordset from all the records in the Orders table.\n        Dim sNWind As String\n        Dim conn As New ADODB.Connection()\n        Dim rs As ADODB.Recordset\n        conn.Open(\"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" &amp; _\n            sNorthwind &amp; \";\")\n        conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient\n        rs = conn.Execute(\"Orders\", , ADODB.CommandTypeEnum.adCmdTable)\n\n        'Create a new workbook in Excel.\n        Dim oExcel As Object\n        Dim oBook As Object\n        Dim oSheet As Object\n        oExcel = CreateObject(\"Excel.Application\")\n        oBook = oExcel.Workbooks.Add\n        oSheet = oBook.Worksheets(1)\n\n        'Transfer the field names to Row 1 of the worksheet:\n        'Note: CopyFromRecordset copies only the data and not the field\n        '      names, so you can transfer the fieldnames by traversing the\n        '      fields collection.\n        Dim n As Int32\n        For n = 1 To rs.Fields.Count\n            oSheet.Cells(1, n).Value = rs.Fields(n - 1).Name\n        Next\n\n        'Transfer the data to Excel.\n        oSheet.Range(\"A2\").CopyFromRecordset(rs)\n\n        'Save the workbook and quit Excel.\n        oBook.SaveAs(sSampleFolder &amp; \"Book3.xls\")\n        oSheet = Nothing\n        oBook = Nothing\n        oExcel.Quit()\n        oExcel = Nothing\n        GC.Collect()\n\n        'Close the connection.\n        rs.Close()\n        conn.Close()\n    End Function\n\n    Private Function Automation_QueryTable()\n        'Create a new workbook in Excel.\n        Dim oExcel As Object\n        Dim oBook As Object\n        Dim oSheet As Object\n        oExcel = CreateObject(\"Excel.Application\")\n        oBook = oExcel.Workbooks.Add\n        oSheet = oBook.Worksheets(1)\n\n        'Create the QueryTable object.\n        Dim oQryTable As Object\n        oQryTable = oSheet.QueryTables.Add( _\n        \"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" &amp; _\n            sNorthwind &amp; \";\", oSheet.Range(\"A1\"), _\n            \"Select * from Orders\")\n        oQryTable.RefreshStyle = 2 ' xlInsertEntireRows = 2\n        oQryTable.Refresh(False)\n\n        'Save the workbook and quit Excel.\n        oBook.SaveAs(sSampleFolder &amp; \"Book4.xls\")\n        oQryTable = Nothing\n        oSheet = Nothing\n        oBook = Nothing\n        oExcel.Quit()\n\n        oExcel = Nothing\n\n    End Function\n\n    Private Function Use_Clipboard()\n        'Copy a string to the clipboard.\n        Dim sData As String\n        sData = \"FirstName\" &amp; vbTab &amp; \"LastName\" &amp; vbTab &amp; \"Birthdate\" &amp; vbCr _\n            &amp; \"Bill\" &amp; vbTab &amp; \"Brown\" &amp; vbTab &amp; \"2\/5\/85\" &amp; vbCr _\n            &amp; \"Joe\" &amp; vbTab &amp; \"Thomas\" &amp; vbTab &amp; \"1\/1\/91\"\n        System.Windows.Forms.Clipboard.SetDataObject(sData)\n\n        'Create a new workbook in Excel.\n        Dim oExcel As Object\n        Dim oBook As Object\n        oExcel = CreateObject(\"Excel.Application\")\n        oBook = oExcel.Workbooks.Add\n\n        'Paste the data.\n        oBook.Worksheets(1).Range(\"A1\").Select()\n        oBook.Worksheets(1).Paste()\n\n        'Save the workbook and quit Excel.\n        oBook.SaveAs(sSampleFolder &amp; \"Book5.xls\")\n        oBook = Nothing\n        oExcel.Quit()\n        oExcel = Nothing\n        GC.Collect()\n    End Function\n\n    Private Function Create_TextFile()\n        'Connect to the data source.\n        Dim objConn As New System.Data.OleDb.OleDbConnection( _\n            \"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" &amp; sNorthwind &amp; \";\")\n        objConn.Open()\n\n        'Run a command to retrieve all records from the Employees table.\n        Dim objCmd As New System.Data.OleDb.OleDbCommand( _\n            \"Select * From Employees\", objConn)\n        Dim objReader As System.Data.OleDb.OleDbDataReader\n        objReader = objCmd.ExecuteReader()\n\n        'Read the records in the dataset and write select fields to the \n        'output file.\n        FileOpen(1, sSampleFolder &amp; \"Book6.txt\", OpenMode.Output)\n        Dim i As Integer, s As String\n        While objReader.Read()\n            'Loop through first 6 fields and concatenate\n            'each field, separated by a tab, into s variable.\n            s = \"\"\n            For i = 0 To 5\n                If Not objReader.IsDBNull(i) Then\n                    If i = 0 Then 'field 1 is EmployeeId\n                        s = s &amp; objReader.GetInt32(i).ToString\n                    ElseIf i = 5 Then 'field 6 is BirthDate\n                        s = s &amp; objReader.GetDateTime(i)\n                    Else 'field is a text field\n                        s = s &amp; objReader.GetString(i)\n                    End If\n                End If\n                s = s &amp; Microsoft.VisualBasic.ControlChars.Tab\n            Next\n            PrintLine(1, s)\n        End While\n        FileClose(1)\n\n        'Close the reader and the connection.\n        objReader.Close()\n        objConn.Close()\n\n        'Create a new instance of Excel.\n        Dim oExcel As Object\n        oExcel = CreateObject(\"Excel.Application\")\n\n        'Open the text file and save it in the Excel workbook format.\n        oExcel.Workbooks.OpenText(sSampleFolder &amp; \"Book6.txt\", _\n            , , , -4142, , True) 'xlTextQualifierNone=-4142\n\n        oExcel.ActiveWorkbook.SaveAs(sSampleFolder &amp; \"Book6.xls\", _\n            -4143) 'xlWorkbookNormal = -4143\n\n        'Quit Excel.\n        oExcel.Quit()\n        oExcel = Nothing\n        GC.Collect()\n    End Function\n\n    Private Function Use_ADONET()\n\n        'Verify that the workbook to write to does exist.\n        Dim sFile As String = sSampleFolder &amp; \"Book7.xls\"\n        If Dir(sFile) = \"\" Then\n            MsgBox(\"Please create the workbook Book7.xls and try again.\")\n            Exit Function\n        End If\n\n        'Establish a connection to the data source.\n        Dim sConnectionString As String\n        sConnectionString = \"Provider=Microsoft.Jet.OLEDB.4.0;\" &amp; _\n            \"Data Source=\" &amp; sSampleFolder &amp; _\n            \"Book7.xls;Extended Properties=Excel 8.0;\"\n        Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)\n        objConn.Open()\n\n        'Add two records to the table named 'MyTable'.\n        Dim objCmd As New System.Data.OleDb.OleDbCommand()\n        objCmd.Connection = objConn\n        objCmd.CommandText = \"Insert into [Sheet1$] (FirstName, LastName)\" &amp; _\n            \" values ('Bill', 'Brown')\"\n        objCmd.ExecuteNonQuery()\n        objCmd.CommandText = \"Insert into [Sheet1$] (FirstName, LastName)\" &amp; _\n            \" values ('Joe', 'Thomas')\"\n        objCmd.ExecuteNonQuery()\n\n        'Close the connection.\n        objConn.Close()\n    End Function<\/pre>\n<\/div>\n<\/div>\n<p><b>Note<\/b>\u00a0If you did not install Office to the default folder (C:\\Program Files\\Microsoft Office), change the\u00a0<b>sNorthwind<\/b>constant in the code sample to match your installation path for Northwind.mdb.<\/li>\n<li>Add the following code to the top of Form1.vb.\n<div>\n<div>\n<pre>Imports Microsoft.Office.Interop<\/pre>\n<\/div>\n<\/div>\n<\/li>\n<li>Press F5 to build and then run the sample.<\/li>\n<\/ol>\n<div translate=\"false\"><a href=\"http:\/\/support.microsoft.com\/kb\/306022#top\"><img decoding=\"async\" alt=\"\" src=\"http:\/\/support.microsoft.com\/library\/images\/support\/en-us\/uparrow.gif\" \/>Back to the top<\/a>\u00a0|\u00a0<a href=\"http:\/\/support.microsoft.com\/kb\/306022#survey\">Give Feedback<\/a><\/div>\n<\/div>\n<h2 id=\"tocHeadRef\" translate=\"false\"><img decoding=\"async\" alt=\"\" src=\"http:\/\/support.microsoft.com\/library\/images\/support\/en-us\/20x20_grey_minus.png\" \/><a>REFERENCES<\/a><\/h2>\n<div id=\"MT1\">For more information, visit the following Microsoft Developer Network (MSDN) Web site:<\/p>\n<div><a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/aa188489(office.10).aspx\">http:\/\/msdn2.microsoft.com\/en-us\/library\/aa188489(office.10).aspx<\/a><\/div>\n<p>For more information, click the following article number to view the article in the Microsoft Knowledge Base:<\/p>\n<div><a href=\"http:\/\/support.microsoft.com\/kb\/247412\">247412<\/a>\u00a0Methods for transferring data to Excel from Visual Basic<\/div>\n<p>&nbsp;<\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Article ID: 306022 &#8211;\u00a0View products that this article applies to. This article was previously published under Q306022 For a Microsoft Visual C# .NET version of&hellip; <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[],"class_list":["post-177","post","type-post","status-publish","format-standard","hentry","category-vb-net"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/177","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/comments?post=177"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/177\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=177"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=177"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=177"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}