In this task
Summary
Overview Techniques
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 Object 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 Transfer XML Data (Excel 2002 Only) Create The Complete Sample Visual Basic .NET Project References
SUMMARYThis 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.
Back to the top
OverviewThe Technique That Is Used Most Frequently To Transfer Data To An Excel Workbook IS
Automation. 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.
With Automation, you can use several techniques to transfert 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 by using the CopyFromRecordset method. Create a QueryTable object 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.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 offer from your clients.the folload approaches may 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.NET. Transfer XML data to Excel (version 2002 only) to provide data that is Formatted and Arranged Into Rows and Column.
Back to the top
Techniques
Use Automation To Transfer Data Cell by Cellwith Automation, You Can Transfer Data TO A Worksheet One Cell At A Time, 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 ()
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
Range object that you acquire at run time results in an interface request; therefore, transferring data in this manner can be slow Additionally, 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 additional information, click the article number below to view the article in the Microsoft Knowledge Base:
216400 PRB: Cross-Process COM Automation Can Hang Client Application on Win95 / 98 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.
For additional information, and for an example of how to automate Excel with visual basic .NET, Click The Article Number Below To View The Article In The Microsoft Knowledge Base:
301982 HOWTO: Automate Microsoft Excel from Visual Basic .NET
Back to the top
Use Automation to Transfer An Array of Data To a Range ON A Worksheetan Array of Data Can Be TransferRed to a Range of Multiple Cells At The Same Time, As Follows: Dim Oxcel 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 transfer your 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.:
Osheet.Range ("A2"). Resize (100, 3) .value = dataArray
THIS LINE REPRESENTS TWO Interface Requests: One for the
Range Object That
Range Method Returns, And Another for Thae
Range Object That
Resize method returns. In contrast, transferring the data cell by cell requires requests for 300 interfaces toRange objects. Whenever possible, you can benefit from transferring your data in bulk and reducing the number of interface requests you make.
Back to the top
User Automation to Transfer an ado recordset to a worksheet rangethe object models for Excel 2000 and Excel 2002 Provide THE
CopyFromRecordset method 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
CopyFromRecordset Method:
'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 ()
Note:
CopyFromRecordset Works ONLY with ADO
Recordset Objects. A
DataSet That You create by using ado.net cannot be used with the
.
Back to the top
Use automation to create a querytable object on a Worksheeta
QueryTable Object Represents a Table That Is Built from Data That Returned from An External Data Source. While You Automate Excel, You Can Create A
QueryTable By OLEDB OR AN ODBC DATA SOURCE AND A SQL STRING. EXCEL STRING. EXCEL GENERATES The RecordSet and INSERTS The Recordset Into The Worksheet At the Location That You Specify. Using
QueryTable Objects Offers The Following Advantages over the Following Advantages Over
CopyFromRecordset Method:
Excel handles the creation of the recordset and its placement into the worksheet. The query can be saved with the QueryTable object so that it can be refreshed later to obtain an updated recordset. When a new QueryTable is added to your worksheet, you can specify that Data Already EXISTIN CELLS ON The Worksheet Be Shifted to Fit The New Data (See The RefreshStyle Property For Details) .the folload code demonstrates how to automate excel 2000 or 2002 to create a new
QueryTable in an Excel Worksheet by Using Data from The Northwind Sample Database:
'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
Back to the top
Use the ClipboardYou can use the Windows 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 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 the top
Create a Delimited Text File that Excel Can Parse into Rows and ColumnsExcel can open tab- 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.
The Following Code Illustrate Now To Generate A Tab-Delimited Text File from Data That Is 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 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:
'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 the top
Transfer Data to a Worksheet Using ADO.NETYou can use the Microsoft Jet OLE DB provider to add records to a table in an existing Excel workbook A "table" 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.
The Following Code Adds Two New Records To a Table in Book7.xls. The Table In this case 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 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.
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 "pushed down" when new records are added. Keep this in mind ................................
For Additional Information About How To Use ADO.NET, CLICK THE ARTICLE NUMBERS BELOW TO View The Articles in The Microsoft Knowledge Base:
301075 HOW: Connect to a Database and Run A Command by Using ADO.NET and VISUAL BASIC .NET
301216 How to: populate a dataset Object from a database by using Visual Basic .NET
301248 HOW TO: Update a Database from a DataSet Object by Using Visual Basic .NET For additional information about how to use the Jet OLE DB provider with Excel data sources, click the article number% 2 below to view the article% 2 in the Microsoft Knowledge Base: 278973 Sample: ExceLado Demonstrates How To Use Ado To Read and Write Data in Excel Workbooks
257819 HOWTO: USE ADO with Excel Data from visual Basic or VBA
Back to the top
Transfer XML Data (Excel 2002 ONLY) Excel 2002 Can Open Any Xml File That Well-formed. Xml Files Can Be Opened Directly from THE
Open Command on The
File menu, or programatically by using either
Open OR
OpenXML Methods of The
Workbooks collection. If you create XML Files for Use in Excel, You Can Also Create Style Sheets To Format The Data.
For Additional Information About How To Use Xml with Excel 2002, Click The Article Numbers Below to View The Articles in The Microsoft Knowledge Base:
307021 HOW TO: Transfer XML Data To Microsoft Excel 2002 by Using Visual Basic .NET
288215 INFO: Microsoft Excel 2002 and XML
Back to the top
Create The Complete Sample Visual Basic .NET Project
Create a new folder to hold The Excel Workbooks That The Sample Will Create for You, And Then Name The Folder C: / Exceldata / .Follow Thase Steps To Create a New Workbook for the sample to write to:
Start a new workbook in Excel On Sheet1 of the new workbook, type FirstName in cell A1 and 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 Visual Basic Projects, SELECT Windows Application. By Default, Form1 IS Created. 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, locate Microsoft Excel 10.0 Object Library, and then click Select.NOTE: If you have not already done so, Microsoft recommends that you download and then install the Microsoft Office XP Primary Interop Assemblies (PIAs) for additional information about Office XP PIAs, click the article number below to view the article in the Microsoft Knowledge Base:. 328912 INFO: Microsoft Office XP PIAs Are Available for Download On the COM tab, locate Microsoft ActiveX Data Objects 2.7 Library, and then click Select. Click OK in the Add References dialog box to accept your selections. If you receive a prompt to generate wrappers for the libraries that you selected, click Yes. Add a Combo Box control and a Button control to Form1. Add The Following Code To Form1: const Ssamplefolder = "c: / exceldata /" const Snorthwind = "c: / program files / microsoft office / office / samples / northwind.mdb"
Private Sub Form1_Load (Byval Sender 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 = 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 ()
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 property = 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 did not install Office to the default folder (C: / Program Files / Microsoft Office), change the sNorthwind constant in the code sample to match your installation path for Northwind.mdb Add the following code to the top of Form1. .vb: imports Microsoft.Office.Interoppress f5 to build and then run the sample.
