For Microsoft Visual C # .NET versions of this article, see
306023.
This task content
summary
Overview
Use "Automation" to transfer data by "Automation" to transfer data arrays to the worksheet using Automation to transfer the ADO recordset to the workpiece area Use "Automation" to create queryTable objects on a worksheet. The board creates a text file that can be analyzed by Excel analysis as a row and columns using ADO.NET to transfer data to worksheets to transfer XML data (Excel 2002 only) Create a complete example Visual Basic .NET project reference
SUMMARY This article is gradually introduced in a number of methods from the Visual Basic .NET program to Excel 2002. This article also provides the advantages and disadvantages of each method so you can choose the solution that best suits your situation.
Back to top
Overview The technology that is most common to transmit data to an Excel workbook is
automation. With "automation", you can call methods and properties specific to Excel task. "Automation" provides you with the greatest flexibility of the location where you specify data in your workbook, and the ability to format the workbook and various settings at runtime.
With "automation", you can use multiple techniques to transfer data:
Transferring data by unit-by-cell transmission to a region in the array to a region consisting of a cell. Use the CopyFromRecordset method to transfer data in the ADO recordset to the cell area. Create a queryTable object on the Excel worksheet, which contains the results of the query on the ODBC or OLEDB data source. Transfer the data to the clipboard and paste the clipboard content into the Excel worksheet. It is also possible to use a variety of methods that are not necessarily required to use "automation" to transmit data to Excel. If you are running a server-side program, this can be a good way to remove bulk data from the client.
In the absence of "automation", you can use the following methods to transfer data:
Transfer data to tab-divided or comma-separated text files, and Excel can analyze the text file into a unit on a worksheet. Transfer data to a worksheet using ADO.NET. Transfer XML data to Excel (only 2002) to provide data that can be formatted and arranged as rows and columns.
Back to top
method
Use "Automation" to transmit data-by-cell-by-"Automation", you can transfer data from the worksheet by units as follows:
Dim Oexcel As Object
DIM OBOOK As Object
Dim Osheet As Object
'Start a new workbook in excel.
OEXCEL = CREATEOBJECT ("excel.application")
Obook = OEXCEL.WORKBOOKS.ADD
'Add data to cells of the first worksheet in the new workbook.
Osheet = OBOOK.WORKSHEETS (1)
Osheet.Range ("a1"). Value = "Last Name"
Osheet.Range ("B1"). Value = "first name"
Osheet.Range ("A1: B1"). Font.Bold = TRUE
Osheet.Range ("a2"). Value = "doe"
Osheet.Range ("b2"). Value = "john"
'Save the workbook and quit excel.
Obook.saveas (SSAMPLEFolder & "Book1.xls") osheet = Nothing
OBOOK = Nothing
OEXCEL.QUIT ()
OEXCEL = Nothing
Gc.collect ()
If there is no data that needs to be transmitted, transmitting data one by one cell may be an acceptable way. You can flexibly put the data anywhere in the workbook and can format the cells in accordance with the conditions based on the conditions. However, if you have a lot of data that need to be transferred to the Excel workbook, this method is not recommended. Every one you get at runtime
Range objects generate an interface request; therefore, the transmission data speed is slower in this way. In addition, both Microsoft Windows 95, Microsoft Windows Millennium Edition (ME) requests 64 KB restrictions on the interface. If you have 64 kB or more interface requests, the Automation server (Excel) may stop responding, or you may receive an error message indicating that insufficient memory. For additional information, click the article number below to see the article in the Microsoft Knowledge Base:
216400 PRB: Cross-Process COM Automation Can Hang Client Application On Win95 / 98 Need to emphasize that transmitting data by cell is only acceptable for a small amount of data. If you have to transfer big data sets to the Excel, you should consider transferring data to one of the other methods discussed herein.
For examples of other information, how to use Visual Basic .NET to automatically run Excel, click the article number below to view the article in the Microsoft Knowledge Base:
301982 HOWTO: Make Microsoft Excel automatically in Visual Basic .NET
Back to top
Use "Automation" to transfer data arrays to worksheets to simultaneously transmit data arrays to regions composed of multiple cells as follows:
Dim Oexcel As Object
DIM OBOOK As Object
Dim Osheet As Object
'Start a new workbook in excel.
OEXCEL = CREATEOBJECT ("excel.application")
Obook = OEXCEL.WORKBOOKS.ADD
'Create An Array With 3 Column and 100 Rows.
Dim DataArray (99, 2) as Object
DIM R AS INTEGER
For r = 0 to 99
DataArray (r, 0) = "ORD" & Format (R 1, "0000")
DataArray (R, 1) = RND () * 1000
DataArray (R, 2) = DataArray (r, 1) * 0.07
NEXT
'Add headers to the worksheet on row 1.
Osheet = OBOOK.WORKSHEETS (1)
Osheet.Range ("a1"). value = "Order ID"
Osheet.Range ("B1"). Value = "Amount"
Osheet.Range ("C1"). Value = "Tax" 'Transfer The Array To The Worksheet Starting At Cell A2.
Osheet.Range ("A2"). Resize (100, 3) .value = dataArray
'Save the workbook and quit excel.
Obook.saveas (Ssamplefolder & "Book2.xls")
Osheet = Nothing
OBOOK = Nothing
OEXCEL.QUIT ()
OEXCEL = Nothing
Gc.collect ()
If you use an array instead of a cell, the transmission performance is greatly enhanced when transmitting a large amount of data. Consider the following line in the front code, the row transmits the data to 300 cells in the worksheet:
Osheet.Range ("A2"). Resize (100, 3) .value = dataArray
This line represents two interface requests: a request is for
Range method returned
Range object, another request is
Resize method returned
Range object. In contrast, transmitting data by cell is required
The RANGE object issues 300 interface requests. As long as it is possible, you can benefit from bulk to data and reduce the number of interface requests issued.
Back to top
Use "Automation" to transfer the ADO recordset to the worksheet area Excel 2000 and Excel 2002's object model.
CopyFromRecordset method, used to transfer ADO records to the area on the worksheet. The following code shows how to use
The CopyFromRecordset method allows Excel to run to transfer the contents of the "Order" table in the Northwind sample database:
'Create a Recordset from all the records in the order of ot.
DIM SNWIND AS STRING
DIM CONN As new adodb.connection ()
DIM RS as adodb.recordset
Conn.open ("provider = microsoft.jet.Oledb.4.0; data source =" & _
Snorthwind & ";")
Conn.cursorLocation = Adodb.cursorLocationNum.AduSeclient
RS = conn.execute ("Orders", AdoDb.commandtypeenum.adcmdtable)
'Create a New Workbook in Excel.
Dim Oexcel As Object
DIM OBOOK As Object
Dim Osheet As Object
OEXCEL = CREATEOBJECT ("excel.application")
Obook = OEXCEL.WORKBOOKS.ADD
Osheet = OBOOK.WORKSHEETS (1)
'Transfer the Field Names To Row 1 of the Worksheet:
'NOTE: CopyFromRecordset Copies Only the data and not the field
'names, so you can transfer the fieldnames by traversing the' fields collection.
Dim n as int32
For n = 1 to rs.fields.count
Osheet.cells (1, n) .value = rs.fields (n - 1) .name
NEXT
'Transfer the Data to Excel.
Osheet.Range ("A2"). CopyFromRecordset (RS)
'Save the workbook and quit excel.
Obook.saveas (SSAMPLEFolder & "Book3.xls")
Osheet = Nothing
OBOOK = Nothing
OEXCEL.QUIT ()
OEXCEL = Nothing
Gc.collect ()
'Close THE CONNECTION
Rs.close ()
CONN.CLOSE ()
note:
CopyFromRecordset can only be with ADO
The Recordset object is used together. Created using ADO.NET
DataSet can't be
CopyFromRecordset method is used together. Multiple examples in the following sections demonstrate how to use ADO.NET to transfer data to Excel.
Back to top
Use "Automation" to create a QueryTable object on the worksheet
The QueryTable object represents a table that is generated with data returned from the external data source. When you automatically run Excel, you can create a connection string and SQL string that points to OLEDB or ODBC data sources.
QueryTable. Excel will generate record sets and insert the recordset into the works you specify. use
QueryTable object provides the following best
The advantages of the CopyFromRecordset method:
The Excel handles the creation of the record set and placed it into the worksheet. You can save the query using the queryTable object so you can refresh it in the future to get the updated recordset. When adding new queryTable to a worksheet, you can specify the data shift already existing in the unit on the worksheet to place new data (for more information, see the RefreshStyle attribute). The following code demonstrates how to automatically run Excel 2000 or 2002 so that data in the Northwind sample database is created in the Excel worksheet.
QueryTable:
'Create a New Workbook in Excel.
Dim Oexcel As Object
DIM OBOOK As Object
Dim Osheet As Object
OEXCEL = CREATEOBJECT ("excel.application")
Obook = OEXCEL.WORKBOOKS.ADD
Osheet = OBOOK.WORKSHEETS (1)
'Create the querytable object.
Dim oqrytable as object
oqrytable = osheet.QueryTables.Add (_
"OLEDB; Provider = Microsoft.jet.Oledb.4.0; data source =" & _
Snorthwind & ";", Osheet.Range ("a1"), _
"SELECT * from Orders")
oqrytable.refreshstyle = 2 'Xlinsertirerows = 2OQRYTABLE.REFRESH (FALSE)
'Save the workbook and quit excel.
Obook.saveas (Ssamplefolder & "Book4.xls")
oqrytable = Nothing
Osheet = Nothing
OBOOK = Nothing
OEXCEL.QUIT ()
OEXCEL = Nothing
Back to top
Use the clipboard to use the Windows clipboard to transfer data to the worksheet. To paste the data into a plurality of cells on the worksheet, you can copy a string with the following format: In this string, the list is separated by the cargo manner. The following code illustrates how Visual Basic .NET uses the Windows clipboard to transfer data to Excel:
'Copy A String to the Clipboard.
Dim SDATA AS STRING
SDATA = "Firstname" & Vbtab & "Lastname" & vbtab & "birthdate" & vbcr _
& "Bill" & Vbtab & "Brown" & Vbtab & "2/5/85" & VBCR _
& "Joe" & Vbtab & "Thomas" & vbtab & "1/1/91"
System.windows.Forms.clipboard.SetDataObject (SDATA)
'Create a New Workbook in Excel.
Dim Oexcel As Object
DIM OBOOK As Object
OEXCEL = CREATEOBJECT ("excel.application")
Obook = OEXCEL.WORKBOOKS.ADD
'Paste the data.
OBOOK.WORKSHEETS (1). Range ("a1"). SELECT ()
OBOOK.WORKSHEETS (1) .paste ()
'Save the workbook and quit excel.
Obook.saveas (Ssamplefolder & "Book5.xls")
OBOOK = Nothing
OEXCEL.QUIT ()
OEXCEL = Nothing
Gc.collect ()
Back to top
Create a text file Excel with a tab and column of Excel analysis as a row and column can open a file from a tab or comma and analyze the data to a cell. This feature can be used when you want to transfer a lot of data to the worksheet. This may be a good way for client-server programs because text files can be generated on server-side. Then, you can open the text file as you need to use "Automation" as needed.
The following code illustrates how to generate a table-separated text file from the data read with ADO.NET:
'Connect to The Data Source.
Dim objconn as new system.data.oledb.oledbConnection (_
"Provider = microsoft.jet.Oledb.4.0; data source =" & Snorthwind & ") Objconn.Open ()
'Execute A Command To Retrieve All Records from The Employees Table.
Dim objcmd as new system.data.oledb.oledbcommand (_
"SELECT * from Employees", Objconn)
Dim objreader as system.data.oledb.oledbdataareader
Objreader = Objcmd.executeReader ()
'Read the Records in The Dataset and Write SELECT FIELDS TO
'Output file.
FileOpen (1, SSAMPLEFolder & "Book6.txt", OpenMode.Output)
DIM I as integer, s String
While Objreader.Read ()
'Loop THROUGH FIRST 6 FIELDS and Concatenate
'Each Field, Separated by a Tab, Into S Variable.
s = ""
For i = 0 TO 5
IF not objreader.isdbnull (i) THEN
IF i = 0 THEN 'FIELD 1 IS EMPLOYEEID
S = S & Objreader.Getint32 (i) .tostring
Elseif i = 5 Then 'Field 6 is birthdate
s = S & Objreader.getdatetime (i)
Else 'Field Is A Text Field
S = S & Objreader.getstring (i)
END IF
END IF
S = S & Microsoft.visualBasic.ControlChars.tab
NEXT
Printline (1, s)
End while
FILECLOSE (1)
'Close the Reader and the Connection.
Objreader.close ()
Objconn.close ()
No "automation" is used in the previous code. However, you can use the least amount of "Automation" as follows, and save this file in an Excel workbook format:
'Create a New Instance of Excel.
Dim Oexcel As Object
OEXCEL = CREATEOBJECT ("excel.application")
'Open the text file and save it in the Excel Workbook Format.
OEXCEL.WORKBOOKS.OpenText (SSAMPLEFolder & "Book6.txt", _
,, -4142, true) 'XLTextQualifiernone = -4142
OEXCEL.Activeworkbook.saveas (Ssamplefolder & "Book6.xls", _
-4143) 'XLWORKBOOKNORMAL = -4143
'Quit Excel.OExcel.quit ()
OEXCEL = Nothing
Gc.collect ()
Back to top
Use ADO.NET to transfer data to a worksheet. You can add a record to the table in the existing Excel workbook using the Microsoft Jet OLE DB provider. "Table" in Excel is only a region consisting of cells; the region may have a predetermined name. Typically, the first row of the region contains a title (or field name), and all future rows in this area contain records.
The following code adds two new records to the tables in Book7.xls. In this case, the table is Sheet1:
'ESTABLISH a connection to the data source.
DIM SCONNESTRING AS STRING
Sconnectionstring = "provider = microsoft.jet.Oledb.4.0;" & _
"Data Source =" & SSAMPLEFOLDER & _
"Book7.xls; extended property = Excel 8.0;"
Dim objconn as new system.data.oledb.oledbconnection (sconnectionstring)
Objconn.open ()
'Add two records to the Table.
Dim objcmd as new system.data.oledb.oledbcommand ()
Objcmd.connection = Objconn
Objcmd.commandtext = "Insert INTO [SHEET1 $] (Firstname, Lastname" & _
Values ('Bill', 'Brown') "
Objcmd.executenonQuery ()
Objcmd.commandtext = "Insert INTO [SHEET1 $] (Firstname, Lastname" & _
"VALUES ('Joe', 'Thomas')"
Objcmd.executenonQuery ()
'Close the Connection.
Objconn.close ()
When you use the ADO.NET to add a record as shown, the format in the workbook will be maintained. Each record added to the row will inherit the format of the row it in front. For example, the format of the new field added to column B is right-aligned because cell B1 is right alignment.
Note that when a record is added to one or more cells in the worksheet, it covers any of those cells previously contained. In other words, when adding new records, the rows in the worksheet will not be "down". If you plan to insert a new record using ADO.NET, you must remember this when you design the data on the worksheet.
For additional information about how to use ADO.NET, click the article number below to see the article in the Microsoft Knowledge Base:
301075 HOW TO: Connect to the database using ADO.NET and Visual Basic .NET and run commands
301216 How to: populate a dataset Object from a database by using Visual Basic .NET
301248 HOW TO: Using Visual Basic .NET Update the database from the DataSet object to other information about how to use the Jet OLE DB provider with the Excel data source, click the article number% 2 below to view the article in the Microsoft Knowledge Base. % 2: 278973 Sample: Excelado Demonstrates How To Use Ado To Read and Write Data in Excel Workbooks
257819 HOWTO: Using ADO in Visual Basic or VBA to handle Excel data
Back to top
Transfer XML data (Excel 2002 only) Excel 2002 can open any XML files in the format. XML file can be directly from
File menu
Open the command to open, or use
Workbooks collection
Open or
The OpenXML method is open in programming. If you create an XML file for use in Excel, you can also create a style sheet to set the data.
For additional information about how to use XML with Excel 2002, click the article number below to see the article in the Microsoft Knowledge Base:
307021 How to: Use Visual Basic .NET to transmit XML data to Microsoft Excel 2002
288215 INFO: Microsoft Excel 2002 and XML
Back to top
Create a complete example Visual Basic .NET project
Create a new folder to save this example will create the Excel workbook for you, then name it C: / Exceldata /. Follow these steps to create a new workbook for this example to write data:
Start a new workbook in Excel. On the Sheet1 of the new workbook, type firstname to type in cell A1, type LastName in cell A2. Save the workbook as C: /exceldata/book7.xls. Start Visual Studio .NET. On the File menu, click New, and then click Project. Under the Visual Basic project, select the Windows application. FORM1 is created by default. Add a reference to the Excel object library. To do this, follow these steps:
On the project menu, click Add Reference. On the COM tab, find the Microsoft Excel 10.0 object library, and then click Select. Note: If you have not done this, Microsoft recommends you to download and install the Microsoft Office XP master InteroP assembly (PIA). For additional information about Office XP PIA, click the article number below to see the article in the Microsoft Knowledge Base: 328912 Info: Microsoft Office XP PIA is available on the COM tab, find Microsoft ActiveX Data Objects 2.7 Library, Then click Select. In the Add Reference dialog box, click OK to accept your choice. If the system prompts you to generate a package for the selected library, click Yes. Add a COMBO BOX control to Form1 and a Button control. Add the following code to Form1: const samplefolder = "c: / exceldata /"
Const Snorthwind = "C: / Program Files / Microsoft Office / Office / Samples / Northwind.mdb" Private Sub Form1_Load (Byvale AS System.Object, ByVal E AS System.EventArgs)
Handles mybase.load
ComboBoX1.dropdownStyle = ComboBoxStyle.dropdownList
DIM alist as string () = _
{"Use Automation to Transfer Data Cell By Cell", _
"Use Automation to Transfer an Array of Data TO A Range on a Worksheet", _
"Use Automation to Transfer An Ado Recordset To a Worksheet Range", _
"Use Automation to Create a QueryTable on a Worksheet", _
Use the clipboard ", _
"CREATE A Delimited Text File That Excel Can Parse Into Rows and Column", _
"Transfer Data to a Worksheet Using ADO.NET"}
ComboBoX1.Items.addrange (alist)
ComboBoX1.SelectedIndIndex = 0
Button1.text = "go!"
End Sub
Private sub button1_click (Byval Sender As System.Object, Byval E as System.EventArgs)
Handles button1.click
Select Case ComboBox1.SelectedIndex
Case 0: Automation_Cellbycell ()
Case 1: Automation_UseArray ()
Case 2: automation_adorecordset ()
Case 3: Automation_QueryTable ()
Case 4: USE_CLIPBOARD ()
Case 5: CREATE_TEXTFILE ()
Case 6: USE_ADONET ()
End SELECT
Gc.collect ()
End Sub
Private function automation_cellbycell ()
Dim Oexcel As Object
DIM OBOOK As Object
Dim Osheet As Object
'Start a new workbook in excel.
OEXCEL = CREATEOBJECT ("excel.application")
Obook = OEXCEL.WORKBOOKS.ADD
'Add data to cells of the first worksheet in the new workbook.
Osheet = OBOOK.WORKSHEETS (1)
Osheet.Range ("a1"). Value = "Last Name"
Osheet.Range ("B1"). Value = "first name"
Osheet.Range ("A1: B1"). font.bold = trueosheet.range ("a2"). Value = "doe"
Osheet.Range ("b2"). Value = "john"
'Save the workbook and quit excel.
Obook.saveas (Ssamplefolder & "Book1.xls")
Osheet = Nothing
OBOOK = Nothing
OEXCEL.QUIT ()
OEXCEL = Nothing
Gc.collect ()
END FUNCTION
Private function automation_userray ()
Dim Oexcel As Object
DIM OBOOK As Object
Dim Osheet As Object
'Start a new workbook in excel.
OEXCEL = CREATEOBJECT ("excel.application")
Obook = OEXCEL.WORKBOOKS.ADD
'Create An Array With 3 Column and 100 Rows.
Dim DataArray (99, 2) as Object
DIM R AS INTEGER
For r = 0 to 99
DataArray (r, 0) = "ORD" & Format (R 1, "0000")
DataArray (R, 1) = RND () * 1000
DataArray (R, 2) = DataArray (r, 1) * 0.07
NEXT
'Add headers to the worksheet on row 1.
Osheet = OBOOK.WORKSHEETS (1)
Osheet.Range ("a1"). value = "Order ID"
Osheet.Range ("B1"). Value = "Amount"
Osheet.Range ("c1"). Value = "TAX"
'Transfer the Array to the Worksheet Starting At Cell A2.
Osheet.Range ("A2"). Resize (100, 3) .value = dataArray
'Save the workbook and quit excel.
Obook.saveas (Ssamplefolder & "Book2.xls")
Osheet = Nothing
OBOOK = Nothing
OEXCEL.QUIT ()
OEXCEL = Nothing
Gc.collect ()
END FUNCTION
Private function automation_adorecordset ()
'Create a Recordset from all the records in the order of ot.
DIM SNWIND AS STRING
DIM CONN As new adodb.connection ()
DIM RS as adodb.recordset
Conn.open ("provider = microsoft.jet.Oledb.4.0; data source =" & _
Snorthwind & ";") Conn.cursorLocation = Adodb.cursorLocationNum.aduseclient
RS = conn.execute ("Orders", AdoDb.commandtypeenum.adcmdtable)
'Create a New Workbook in Excel.
Dim Oexcel As Object
DIM OBOOK As Object
Dim Osheet As Object
OEXCEL = CREATEOBJECT ("excel.application")
Obook = OEXCEL.WORKBOOKS.ADD
Osheet = OBOOK.WORKSHEETS (1)
'Transfer the Field Names To Row 1 of the Worksheet:
'NOTE: CopyFromRecordset Copies Only the data and not the field
'name, so you can guide the fieldnames by traversing the
'Fields Collection.
Dim n as int32
For n = 1 to rs.fields.count
Osheet.cells (1, n) .value = rs.fields (n - 1) .name
NEXT
'Transfer the Data to Excel.
Osheet.Range ("A2"). CopyFromRecordset (RS)
'Save the workbook and quit excel.
Obook.saveas (SSAMPLEFolder & "Book3.xls")
Osheet = Nothing
OBOOK = Nothing
OEXCEL.QUIT ()
OEXCEL = Nothing
Gc.collect ()
'Close the Connection.
Rs.close ()
CONN.CLOSE ()
END FUNCTION
Private function automation_querytable ()
'Create a New Workbook in Excel.
Dim Oexcel As Object
DIM OBOOK As Object
Dim Osheet As Object
OEXCEL = CREATEOBJECT ("excel.application")
Obook = OEXCEL.WORKBOOKS.ADD
Osheet = OBOOK.WORKSHEETS (1)
'Create the querytable object.
Dim oqrytable as object
oqrytable = osheet.QueryTables.Add (_
"OLEDB; Provider = Microsoft.jet.Oledb.4.0; data source =" & _
Snorthwind & ";", Osheet.Range ("a1"), _
"SELECT * from Orders")
oqryTable.refreshStyle = 2 'XLinSertentireROWS = 2
oqrytable.refresh (false)
'Save the workbook and quit excel.Obook.saveas (Ssamplefolder & "Book4.xls")
oqrytable = Nothing
Osheet = Nothing
OBOOK = Nothing
OEXCEL.QUIT ()
OEXCEL = Nothing
END FUNCTION
Private function use_clipboard ()
'Copy A String to the Clipboard.
Dim SDATA AS STRING
SDATA = "Firstname" & Vbtab & "Lastname" & vbtab & "birthdate" & vbcr _
& "Bill" & Vbtab & "Brown" & Vbtab & "2/5/85" & VBCR _
& "Joe" & Vbtab & "Thomas" & vbtab & "1/1/91"
System.windows.Forms.clipboard.SetDataObject (SDATA)
'Create a New Workbook in Excel.
Dim Oexcel As Object
DIM OBOOK As Object
OEXCEL = CREATEOBJECT ("excel.application")
Obook = OEXCEL.WORKBOOKS.ADD
'Paste the data.
OBOOK.WORKSHEETS (1). Range ("a1"). SELECT ()
OBOOK.WORKSHEETS (1) .paste ()
'Save the workbook and quit excel.
Obook.saveas (Ssamplefolder & "Book5.xls")
OBOOK = Nothing
OEXCEL.QUIT ()
OEXCEL = Nothing
Gc.collect ()
END FUNCTION
Private function create_textfile ()
'Connect to The Data Source.
Dim objconn as new system.data.oledb.oledbConnection (_
"Provider = microsoft.jet.Oledb.4.0; data source =" & Snorthwind & ";")
Objconn.open ()
'Run a Command To Retrieve All Records from The Employees Table.
Dim objcmd as new system.data.oledb.oledbcommand (_
"SELECT * from Employees", Objconn)
Dim objreader as system.data.oledb.oledbdataareader
Objreader = Objcmd.executeReader ()
'Read the Records in The Dataset and Write SELECT FIELDS TO
'Output file.
FileOpen (1, SSAMPLEFOLDER & "BOOK6.TXT", OpenMode.Output) Dim i as integer, s String
While Objreader.Read ()
'Loop THROUGH FIRST 6 FIELDS and Concatenate
'Each Field, Separated by a Tab, Into S Variable.
s = ""
For i = 0 TO 5
IF not objreader.isdbnull (i) THEN
IF i = 0 THEN 'FIELD 1 IS EMPLOYEEID
S = S & Objreader.Getint32 (i) .tostring
Elseif i = 5 Then 'Field 6 is birthdate
s = S & Objreader.getdatetime (i)
Else 'Field Is A Text Field
S = S & Objreader.getstring (i)
END IF
END IF
S = S & Microsoft.visualBasic.ControlChars.tab
NEXT
Printline (1, s)
End while
FILECLOSE (1)
'Close the Reader and the Connection.
Objreader.close ()
Objconn.close ()
'Create a New Instance of Excel.
Dim Oexcel As Object
OEXCEL = CREATEOBJECT ("excel.application")
'Open the text file and save it in the Excel Workbook Format.
OEXCEL.WORKBOOKS.OpenText (SSAMPLEFolder & "Book6.txt", _
,, -4142, true) 'XLTextQualifiernone = -4142
OEXCEL.Activeworkbook.saveas (Ssamplefolder & "Book6.xls", _
-4143) 'XLWORKBOOKNORMAL = -4143
'Quit Excel.
OEXCEL.QUIT ()
OEXCEL = Nothing
Gc.collect ()
END FUNCTION
Private function use_adonet ()
'Verify what the workbook to write to does exist.
DIM SFILE AS STRING = SSAMPLEFOLDER & "BOOK7.XLS"
IF DIR (SFILE) = "" "
Msgbox ("please create the workbook book7.xls and try book.")
EXIT FUNCTION
END IF
'ESTABLISH a connection to the data source.
DIM SCONNESTRING AS STRING
Sconnectionstring = "provider = microsoft.jet.Oledb.4.0;" & _
"Data Source =" & SSAMPLEFOLDER & _ "book7.xls; extended Properties = Excel 8.0;"
Dim objconn as new system.data.oledb.oledbconnection (sconnectionstring)
Objconn.open ()
'Add two records to the table named' myTable '.
Dim objcmd as new system.data.oledb.oledbcommand ()
Objcmd.connection = Objconn
Objcmd.commandtext = "Insert INTO [SHEET1 $] (Firstname, Lastname" & _
Values ('Bill', 'Brown') "
Objcmd.executenonQuery ()
Objcmd.commandtext = "Insert INTO [SHEET1 $] (Firstname, Lastname" & _
"VALUES ('Joe', 'Thomas')"
Objcmd.executenonQuery ()
'Close the Connection.
Objconn.close ()
END FUNCTION
Note: If you do not install Office to the Default Folder (C: / Program Files / Microsoft Office), change the Snorthwind constant in the code example to match the installation path of Northwind.mdb. Add the following code to the top of Form1.vb: Imports Microsoft.Office.Interop
Press F5 to generate and run this example.
Back to top
Refer to more information, visit the following Microsoft Developer Network (MSDN) Web site:
Microsoft Office Development with Visual Studio (Microsoft Office Development) http://msdn.microsoft.com/library/en-us/dnoffdev/html/vsofficeDev.asp For additional information, click the following article number, To view the article in the Microsoft Knowledge Base:
247412 Info: Methods for Transferring Data To Excel from visual Basic
Back to top
The information in this article applies to:
Microsoft Excel 2002 Microsoft Visual Basic .NET (2002) Microsoft ADO.NET (Included with the .net framework)
Recent Updated: 2004-3-29 (4.0) Keywords: kbautomation kbhowtomaster KB306022 KBAUDDEVELOPER
