HOW TO: Transfer Data to an Excel Workbook by Using Visual Basic .NET (Q306022)

The information in this article applies to:

- Microsoft Visual Basic .NET (2002)

- Microsoft ADO.NET (included with the .NET Framework)

- Microsoft Excel 2000

- Microsoft Excel 2002

For a Microsoft Visual Basic 6.0 version of this article, see Q247412 .

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 Columns
- Transfer Data to a Worksheet Using ADO.NET
- Transfer XML Data (Excel 2002 Only)

- Create the Complete Sample Visual Basic .NET Project
- REFERENCES

SUMMARY

This step-by-step article describes numerous methods for transferring data to Excel 2000 or Excel 2002 from a Visual Basic .NET application. This article also presents the advantages and disadvantages of each method so that you can choose the solution that works best for your situation.
back to the top

Overview

The technique that is most commonly used 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, as well as the ability to format the workbook and make various settings at run time.
With Automation, you can use several techniques to transfer 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 an application server-side, this can be a good approach for taking the bulk of data processing away from your clients.
The following 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 columns.

back to the top

Techniques

Use Automation to Transfer Data Cell by Cell

With 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 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; therefore, transferring data in this manner can be slow. Additionally, Microsoft Windows 95, Microsoft Windows 98, and Microsoft Windows Millenium Edition (Me) have a 64 KB limitation on interface requests. If you reach or exceed this 64 KB limit on 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:

Q216400 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 need to transfer large data sets to Excel, you should consider using one of the other approaches that are discussed in this article to transfer data in bulk.
For an example of how to automate Excel with Visual Basic .NET, see the following Knowledge Base article:

Q301982 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 Worksheet

An array of data can be transferred to a range of multiple cells at once, 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 columns 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 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:

oSheet.Range("A2").Resize(100, 3).Value = DataArray

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. In contrast, transferring the data cell by cell requires 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.
back to the top

Use Automation to Transfer an ADO Recordset to a Worksheet Range

The 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 Orders table.

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.CursorLocationEnum.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 works only with ADO Recordset objects. A DataSet that you create by using ADO.NET cannot be used with the CopyFromRecordset method. Several examples in the sections that follow demonstrate how to transfer data to Excel with ADO.NET.
back to the top

Use Automation to Create a QueryTable Object on a Worksheet

A QueryTable object represents a table that is built from data that is returned from an external data source. While you automate Excel, you can create a QueryTable by providing a connection string to an OLEDB or an ODBC data source along with a Structured Query Language (SQL) 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 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 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 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 Clipboard

You 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 Columns

Excel 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 large amount of data to a worksheet while using little, if any, Automation. This may 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 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.OleDbDataReader

objReader = objCmd.ExecuteReader()

'Read the records in the dataset and write select fields to the

'output file.

FileOpen(1, sSampleFolder & "Book6.txt", OpenMode.Output)

Dim i As Integer, s As 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 a minimal amount of 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.NET

You 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 subsequent 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 sConnectionString 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.

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 above it; for example, new fields that are added to column B are formatted with right alignment because cell B1 is right-aligned.
You should 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. You should keep this in mind when you design the layout of data on your worksheets if you intend to the insert new records by using ADO.NET.
For more information about using ADO.NET, see the following Knowledge Base articles:

Q301075 HOW TO: Connect to a Database and Run a Command by Using ADO.NET and Visual Basic .NET

Q301216 HOW TO: Populate a DataSet Object from a Database by Using Visual Basic .NET

Q301248 HOW TO: Update a Database from a DataSet Object by Using Visual Basic .NET

For more inforamtion on using the Jet OLEDB Provider with Excel data sources, see the following Knowledge Base articles:

Q278973 SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks

Q257819 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 is well-formed. XML files can be opened directly from the Open command on the File menu, or programmatically by using either the 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 more information about using XML with Excel 2002, see the following Knowledge Base articles:

Q307021 HOW TO: Transfer XML Data to Microsoft Excel 2002 by Using Visual Basic .NET

Q288215 INFO: Microsoft Excel 2002 and XML

back to the top

Create the Complete Sample Visual Basic .NET Project

1. Create a new folder to hold the Excel workbooks that the sample will create for you, and then name the folder C:\Exceldata\.
2. Follow these steps to create a new workbook for the sample to write to:

1. Start a new workbook in Excel.
2. On Sheet1 of the new workbook, type FirstName in cell A1 and LastName in cell A2.
3. Save the workbook as C:\Exceldata\Book7.xls.

3. Start Visual Studio .NET. On the File menu, click New and then click Project . Under Visual Basic Projects , select Windows Application . Form1 is created by default.
4. Add a reference to the Excel object library. To do this, follow these steps:

1. On the Project menu, click Add Reference .
2. On the COM tab, locate Microsoft Excel 9.0 Object Library (or Microsoft Excel 10.0 Object Library ) and click Select .
3. On the COM tab, locate Microsoft ActiveX Data Objects 2.7 Library and click Select .
4. 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 .

5. Add a ComboBox control and a Button control to Form1.
6. Add the following code to Form1:

7. Const sSampleFolder = "C:\ExcelData\"

8. Const sNorthwind = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

9.

10. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _

11. Handles MyBase.Load

12. ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList

13. Dim aList As String() = _

14. {"Use Automation to Transfer Data Cell by Cell ", _

15. "Use Automation to Transfer an Array of Data to a Range on a Worksheet ", _

16. "Use Automation to Transfer an ADO Recordset to a Worksheet Range ", _

17. "Use Automation to Create a QueryTable on a Worksheet", _

18. "Use the Clipboard", _

19. "Create a Delimited Text File that Excel Can Parse into Rows and Columns", _

20. "Transfer Data to a Worksheet Using ADO .NET "}

21. ComboBox1.Items.AddRange(aList)

22. ComboBox1.SelectedIndex = 0

23. Button1.Text = "Go!"

24. End Sub

25.

26. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

27. Handles Button1.Click

28. Select Case ComboBox1.SelectedIndex

29. Case 0 : Automation_CellByCell()

30. Case 1 : Automation_UseArray()

31. Case 2 : Automation_ADORecordset()

32. Case 3 : Automation_QueryTable()

33. Case 4 : Use_Clipboard()

34. Case 5 : Create_TextFile()

35. Case 6 : Use_ADONET()

36. End Select

37. GC.Collect()

38. End Sub

39.

40. Private Function Automation_CellByCell()

41. Dim oExcel As Object

42. Dim oBook As Object

43. Dim oSheet As Object

44.

45. 'Start a new workbook in Excel.

46. oExcel = CreateObject("Excel.Application")

47. oBook = oExcel.Workbooks.Add

48.

49. 'Add data to cells of the first worksheet in the new workbook.

50. oSheet = oBook.Worksheets(1)

51. oSheet.Range("A1").Value = "Last Name"

52. oSheet.Range("B1").Value = "First Name"

53. oSheet.Range("A1:B1").Font.Bold = True

54. oSheet.Range("A2").Value = "Doe"

55. oSheet.Range("B2").Value = "John"

56.

57. 'Save the workbook and quit Excel.

58. oBook.SaveAs(sSampleFolder & "Book1.xls")

59. oSheet = Nothing

60. oBook = Nothing

61. oExcel.Quit()

62. oExcel = Nothing

63. GC.Collect()

64. End Function

65.

66. Private Function Automation_UseArray()

67. Dim oExcel As Object

68. Dim oBook As Object

69. Dim oSheet As Object

70.

71. 'Start a new workbook in Excel.

72. oExcel = CreateObject("Excel.Application")

73. oBook = oExcel.Workbooks.Add

74.

75. 'Create an array with 3 columns and 100 rows.

76. Dim DataArray(99, 2) As Object

77. Dim r As Integer

78. For r = 0 To 99

79. DataArray(r, 0) = "ORD" & Format(r + 1, "0000")

80. DataArray(r, 1) = Rnd() * 1000

81. DataArray(r, 2) = DataArray(r, 1) * 0.07

82. Next

83.

84. 'Add headers to the worksheet on row 1.

85. oSheet = oBook.Worksheets(1)

86. oSheet.Range("A1").Value = "Order ID"

87. oSheet.Range("B1").Value = "Amount"

88. oSheet.Range("C1").Value = "Tax"

89.

90. 'Transfer the array to the worksheet starting at cell A2.

91. oSheet.Range("A2").Resize(100, 3).Value = DataArray

92.

93. 'Save the workbook and quit Excel.

94. oBook.SaveAs(sSampleFolder & "Book2.xls")

95. oSheet = Nothing

96. oBook = Nothing

97. oExcel.Quit()

98. oExcel = Nothing

99. GC.Collect()

100. End Function

101.

102. Private Function Automation_ADORecordset()

103. 'Create a Recordset from all the records in the Orders table.

104. Dim sNWind As String

105. Dim conn As New ADODB.Connection()

106. Dim rs As ADODB.Recordset

107. conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

108. sNorthwind & ";")

109. conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient

110. rs = conn.Execute("Orders", , ADODB.CommandTypeEnum.adCmdTable)

111.

112. 'Create a new workbook in Excel.

113. Dim oExcel As Object

114. Dim oBook As Object

115. Dim oSheet As Object

116. oExcel = CreateObject("Excel.Application")

117. oBook = oExcel.Workbooks.Add

118. oSheet = oBook.Worksheets(1)

119.

120. 'Transfer the field names to Row 1 of the worksheet:

121. 'Note: CopyFromRecordset copies only the data and not the field

122. ' names, so you can transfer the fieldnames by traversing the

123. ' fields collection.

124. Dim n As Int32

125. For n = 1 To rs.Fields.Count

126. oSheet.Cells(1, n).Value = rs.Fields(n - 1).Name

127. Next

128.

129. 'Transfer the data to Excel.

130. oSheet.Range("A2").CopyFromRecordset(rs)

131.

132. 'Save the workbook and quit Excel.

133. oBook.SaveAs(sSampleFolder & "Book3.xls")

134. oSheet = Nothing

135. oBook = Nothing

136. oExcel.Quit()

137. oExcel = Nothing

138. GC.Collect()

139.

140. 'Close the connection.

141. rs.Close()

142. conn.Close()

143. End Function

144.

145. Private Function Automation_QueryTable()

146. 'Create a new workbook in Excel.

147. Dim oExcel As Object

148. Dim oBook As Object

149. Dim oSheet As Object

150. oExcel = CreateObject("Excel.Application")

151. oBook = oExcel.Workbooks.Add

152. oSheet = oBook.Worksheets(1)

153.

154. 'Create the QueryTable object.

155. Dim oQryTable As Object

156. oQryTable = oSheet.QueryTables.Add( _

157. "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

158. sNorthwind & ";", oSheet.Range("A1"), _

159. "Select * from Orders")

160. oQryTable.RefreshStyle = 2 ' xlInsertEntireRows = 2

161. oQryTable.Refresh(False)

162.

163. 'Save the workbook and quit Excel.

164. oBook.SaveAs(sSampleFolder & "Book4.xls")

165. oQryTable = Nothing

166. oSheet = Nothing

167. oBook = Nothing

168. oExcel.Quit()

169.

170.

171. oExcel = Nothing

172.

173. End Function

174.

175. Private Function Use_Clipboard()

176. 'Copy a string to the clipboard.

177. Dim sData As String

178. sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _

179. & "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _

180. & "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"

181. System.Windows.Forms.Clipboard.SetDataObject(sData)

182.

183. 'Create a new workbook in Excel.

184. Dim oExcel As Object

185. Dim oBook As Object

186. oExcel = CreateObject("Excel.Application")

187. oBook = oExcel.Workbooks.Add

188.

189. 'Paste the data.

190. oBook.Worksheets(1).Range("A1").Select()

191. oBook.Worksheets(1).Paste()

192.

193. 'Save the workbook and quit Excel.

194. oBook.SaveAs(sSampleFolder & "Book5.xls")

195. oBook = Nothing

196. oExcel.Quit()

197. oExcel = Nothing

198. GC.Collect()

199. End Function

200.

201. Private Function Create_TextFile()

202. 'Connect to the data source.

203. Dim objConn As New System.Data.OleDb.OleDbConnection( _

204. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sNorthwind & ";")

205. objConn.Open()

206.

207. 'Run a command to retrieve all records from the Employees table.

208. Dim objCmd As New System.Data.OleDb.OleDbCommand( _

209. "Select * From Employees", objConn)

210. Dim objReader As System.Data.OleDb.OleDbDataReader

211. objReader = objCmd.ExecuteReader()

212.

213. 'Read the records in the dataset and write select fields to the

214. 'output file.

215. FileOpen(1, sSampleFolder & "Book6.txt", OpenMode.Output)

216. Dim i As Integer, s As String

217. While objReader.Read()

218. 'Loop through first 6 fields and concatenate

219. 'each field, separated by a tab, into s variable.

220. s = ""

221. For i = 0 To 5

222. If Not objReader.IsDBNull(i) Then

223. If i = 0 Then 'field 1 is EmployeeId

224. s = s & objReader.GetInt32(i).ToString

225. ElseIf i = 5 Then 'field 6 is BirthDate

226. s = s & objReader.GetDateTime(i)

227. Else 'field is a text field

228. s = s & objReader.GetString(i)

229. End If

230. End If

231. s = s & Microsoft.VisualBasic.ControlChars.Tab

232. Next

233. PrintLine(1, s)

234. End While

235. FileClose(1)

236.

237. 'Close the reader and the connection.

238. objReader.Close()

239. objConn.Close()

240.

241. 'Create a new instance of Excel.

242. Dim oExcel As Object

243. oExcel = CreateObject("Excel.Application")

244.

245. 'Open the text file and save it in the Excel workbook format.

246. oExcel.Workbooks.OpenText(sSampleFolder & "Book6.txt", _

247. , , , -4142, , True) 'xlTextQualifierNone=-4142

248.

249. oExcel.ActiveWorkbook.SaveAs(sSampleFolder & "Book6.xls", _

250. -4143) 'xlWorkbookNormal = -4143

251.

252. 'Quit Excel.

253. oExcel.Quit()

254. oExcel = Nothing

255. GC.Collect()

256. End Function

257.

258. Private Function Use_ADONET()

259.

260. 'Verify that the workbook to write to does exist.

261. Dim sFile As String = sSampleFolder & "Book7.xls"

262. If Dir(sFile) = "" Then

263. MsgBox("Please create the workbook Book7.xls and try again.")

264. Exit Function

265. End If

266.

267. 'Establish a connection to the data source.

268. Dim sConnectionString As String

269. sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

270. "Data Source=" & sSampleFolder & _

271. "Book7.xls;Extended Properties=Excel 8.0;"

272. Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)

273. objConn.Open()

274.

275. 'Add two records to the table named 'MyTable'.

276. Dim objCmd As New System.Data.OleDb.OleDbCommand()

277. objCmd.Connection = objConn

278. objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" & _

279. " values ('Bill', 'Brown')"

280. objCmd.ExecuteNonQuery()

281. objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" & _

282. " values ('Joe', 'Thomas')"

283. objCmd.ExecuteNonQuery()

284.

285. 'Close the connection.

286. objConn.Close()

End Function

NOTE : If you did not install Office to the default folder (C:\Program Files\Microsoft Office), you should modify the sNorthwind constant in the code sample to match your installation path for Northwind.mdb.

287. Press F5 to build and run the sample.

back to the top

REFERENCES

For more information, see the following Microsoft Developer Network (MSDN) Web site:

Microsoft Office Development with Visual Studio
http://msdn.microsoft.com/library/en-us/dnoffdev/html/vsofficedev.asp

back to the top
(c) Microsoft Corporation 2001, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.