INFO: Method for transferring data from Visual Basic to Excel
Suitable
SUMMARY This article describes a variety of ways to transfer data from Microsoft Visual Basic applications to Microsoft Excel. This article also introduces the advantages and disadvantages of each method so you can choose the solution that best suits you.
MORE INFORMATION The most common way to transfer data to the Excel workbook is "Automation" function. The Automation feature provides you with the maximum flexibility to specify the location where you are located in your workbook, and the functionality of formatting the workbook and performs various settings at runtime. With the "Automation" function, you can use multiple methods to transfer data:
Melody Transmission Data Transferring data in an array to cells Using the CopyFromRecordSet method to create a queryTable on the EXCEL worksheet to the cell area, which contains QueryTable on the Excel worksheet, which contains the results of the ODBC or OLEDB data source. Transfer the data to the clipboard and paste the clipboard content into the Excel worksheet. You can also use some other way to transfer data to Excel without having to use Automation. If you are running a server-side application, this is a good way to handle bulk data from the client. In the case where there is no "automated" function, you can use the following methods to transfer data:
Transfer data to a tablet-separated or comma-separated text file, then Excel can analyze the text file to work on the worksheet using ADO to transfer data to worksheet using dynamic data exchange (DDE) transfer data to Excel The following section provides details of each solution.
Use the Automation feature to transfer data with the Automation feature, you can transfer data to the worksheet by order.
Dim Oexcel As Object
DIM OBOOK As Object
Dim Osheet As Object
'Start a New Workbook in Excel
Set Oexcel = CreateObject ("Excel.Application")
Set Obook = OEXCEL.WORKBOOKS.ADD
'Add Data To Cells of The First Worksheet in The New Workbook
Set 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 "C: /book1.xls"
OEXCEL.QUIT If the amount of data is less, the unit-by-order transmission data is a fully acceptable approach. You can flexibly put the data anywhere in the workbook and can format the cells based on the conditions at runtime. However, if you need to transfer a lot of data 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, Microsoft Windows 95 and Windows 98 have 64K on the interface request. If you reach or exceed this 64K restriction on the interface request, the Automation Server (Excel) may stop responding, or you may receive an error indicating insufficient memory. This limitation in Windows 95 and Windows 98 discussed in the following knowledge base article: 216400 PRB: Cross-Process COM Automation Can Hang Client Application On Win95 / 98 Need to emphasize that the unit-by-element transmission data applies only A small amount of data is transmitted. If you need to transfer a large amount of data set to Excel, one of the solutions provided below should be considered.
For more sample code for automated Excel, see the following Microsoft Knowledge Base Article:
219151 HOWTO: Automatically runs Microsoft Excel in Visual Basic
Use the Automation feature to transfer the data array to the worksheet. You can transfer a data array to multiple cell regions at a time:
Dim Oexcel As Object
DIM OBOOK As Object
Dim Osheet As Object
'Start a New Workbook in Excel
Set Oexcel = CreateObject ("Excel.Application")
Set Obook = OEXCEL.WORKBOOKS.ADD
'Create An Array With 3 Columns and 100 Rows
Dim DataArray (1 to 100, 1 to 3) As Variant
DIM R AS INTEGER
For r = 1 to 100
DataArray (R, 1) = "ORD" & Format (r, "0000")
DataArray (r, 2) = rND () * 1000
DataArray (R, 3) = DataArray (r, 2) * 0.7
NEXT
'Add headers to the worksheet on row 1
Set Osheet = Obook.Worksheets (1)
Osheet.Range ("A1: C1"). Value = Array ("ORDER ID", "Amount", "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 "C: /book1.xls"
OEXCEL.QUIT If you use an array to transfer data instead of a single-cell transfer data, the transmission performance is greatly enhanced when transmitting a large amount of data. Please note the following lines in the above code, the line transmits the data to 300 cells in the worksheet:
Osheet.Range ("A2"). Resize (100, 3) .value = DataArray This line represents two interface requests (one for
Range method returned
Range object, another for
Resize method returned
Range object). On the other hand, a single-piece transmission data requires request to point to
300 interfaces of the Range object. As long as it is possible, you can benefit from bulk transfer data and reducing the number of interface requests. Use the Automation feature to transfer the ADO recordset to the worksheet area Excel 2000.
The CopyFromRecordset method allows you to transfer the ADO (or DAO) record set to a region on the worksheet. The following code illustrates how to automate Excel 2000, Excel 2002 or Office Excel 2003, and use
The CopyFromRecordSet method transmits the contents of the order table in the Rose Document Database.
'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
SNWIND = _
"C: / Program Files / Microsoft Office / Office / Samples / Northwind.mdb"
Conn.open "provider = microsoft.jet.oledb.4.0; data source =" & _
Snwind & ";"
Conn.cursorLocation = aduseclient
SET RS = conn.execute ("Orders", AdcmdTable)
'Create a New Workbook in Excel
Dim Oexcel As Object
DIM OBOOK As Object
Dim Osheet As Object
Set Oexcel = CreateObject ("Excel.Application")
Set Obook = OEXCEL.WORKBOOKS.ADD
Set Osheet = Obook.Worksheets (1)
'Transfer The Data to Excel
Osheet.Range ("a1"). CopyFromRecordset RS
'Save the workbook and quit excel
Obook.saveas "C: /book1.xls"
OEXCEL.QUIT
'Close THE Connection
Rs.close
Conn.CloseExcel 97 also provides a
CopyFromRecordset method, but it can only use the DAO recordset. Excel 97
CopyFromRecordset does not support ADO.
About using ADO and
For more information on your CopyFromRecordset method, see the following Microsoft Knowledge Base Article:
246335 HOWTO: Use the "Automation" function to transfer data from the ADO recordset to Excel
Use the Automation feature to create queryTable on a worksheet
The QueryTable object represents a table built by the data returned by the external data source. When you automatically run Microsoft Excel, you can only provide a connection string and SQL strings that point to OLEDB or ODBC data sources.
QueryTable. Excel assumes to generate a recordset and is responsible for inserting it into the worksheet where you specified. use
QueryTables can provide better than
CopyFromRecordset methods have multiple advantages:
The Excel handles the creation of the record set and placed it into the worksheet. The query can be saved in the queryTable so that you can refresh in later 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, Excel 2002, or Office Excel 2003 to create a new QueryTable in the Excel worksheet using data in the Ross document Database:
'Create a New Workbook in Excel
Dim Oexcel As Object
DIM OBOOK As Object
Dim Osheet As Object
Set Oexcel = CreateObject ("Excel.Application")
Set Obook = OEXCEL.WORKBOOKS.ADD
Set Osheet = Obook.Worksheets (1)
'Create the QueryTable
DIM SNWIND AS STRING
SNWIND = _
"C: / Program Files / Microsoft Office / Office / Samples / Northwind.mdb"
Dim oqrytable as object
Set oqrytable = osheet.querytables.add (_
"OLEDB; Provider = Microsoft.jet.Oledb.4.0; data source =" & _
Snwind & ";", Osheet.Range ("A1"), "SELECT * from Orders")
oqrytable.refreshstyle = xlinsertirerows
oqrytable.refresh False
'Save the workbook and quit excel
Obook.saveas "C: /book1.xls"
OEXCEL.QUIT
Using a clipboard Windows clipboard can also be used as a mechanism to transfer data to a worksheet. To paste the data into a plurality of cells on the worksheet, you can copy the column separated by the tab, and the row is separated by the carriageway. The following code illustrates how Visual Basic uses its clipboard object 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"
CLIPBOARD.CLEAR
Clipboard.Settext SDATA
'Create a New Workbook in Excel
Dim Oexcel As Object
DIM OBOOK As Object
Set OEXCEL = CreateObject ("Excel.Application") Set 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 "C: /book1.xls"
OEXCEL.QUIT
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 applications because text files can be generated on server-side. Then, you can open the text file using the Automation feature using the Automation feature as needed.
The following code shows how to create a comma-separated text file from the ADO recordset:
'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
Dim SDATA AS STRING
SNWIND = _
"C: / Program Files / Microsoft Office / Office / Samples / Northwind.mdb"
Conn.open "provider = microsoft.jet.oledb.4.0; data source =" & _
Snwind & ";"
Conn.cursorLocation = aduseclient
SET RS = conn.execute ("Orders", AdcmdTable)
'Save the RecordSet As a Tab-Delimited File
SDATA = rs.getstring (Adclipstring, VBTAB, VBCR, VBNULLSTRING)
Open "c: /test.txt" for Output AS # 1
Print # 1, SDATA
Close # 1
'Close THE Connection
Rs.close
Conn.close
'Open the New Text File in Excel
Shell "C: / Program Files / Microsoft Office / Office / Excel.exe" & _
CHR (34) & "C: / Test.txt" & chr (34), VBMaximizedFocus If the text file has a .csv extension, Excel will open the file without displaying "Text Import Wizard" and automatically assumes the file. It is a comma separated file. Similarly, if the file has a .txt extension, Excel will automatically use tabs to analyze this file.
In the previous code example, Excel uses
The shell statement is started, the file name is used as command line parameters. The "Automation" function is not used in the previous example. However, if you want, you can use the least amount of "Automation" function to open the text file and save it in an Excel workbook format:
'Create a New Instance of Excel
Dim Oexcel As Object
DIM OBOOK As Object
Dim Osheet As Object
Set Oexcel = CreateObject ("Excel.Application")
'Open the text file
Set Obook = OEXCEL.WORKBOOKS.OPEN ("c: /test.txt")
'Save as Excel Workbook and Quit Excel
OBOOK.SAVEAS "C: /book1.xls", XLWORKBOOKNORMAL
OExcel.quit For more information on "File I / O" in the Visual Basic application, see the following Microsoft Knowledge Base Article:
172267 Recedit.vbp Demonstrates File I / O In Visual Basic The following article also discusses and provides sample code for "file I / O" in Visual Basic for Applications:
File Access with Visual Basic for Applications (file access in Visual Basic for Applications)
Using ADO Transfer Data to Worksheet Use the Microsoft Jet OLE DB provider, you can add records to a table of existing Excel workbooks. "Table" in Excel is only a zone with definition name. The first line in the region must contain the title (or field name), and all subsequent lines contain records. The following steps explain how to use names
MyTable empty table creation workbook:
Start a new workbook in Excel. Add the following title to the A1: B1 cells in Sheet1: A1: FirstName B1: LastName Sets the format of cell B1 to right. Select A1: B1. On the Insert menu, select Name and select Definition. Enter the name myTable and click OK. Save the new workbook as C: /book1.xls and exit Excel. To use ADO to add a record to
In MyTable, you can use the code similar to the following:
'Create a New Connection Object for Book1.xls
DIM CONN As New Adodb.connection
Conn.open "provider = microsoft.jet.Oledb.4.0;" & _
"Data Source = C: /book1.xls; Extended Properties = Excel 8.0;"
Conn.execute "INSERT INTO MyTable (FirstName, Lastname" & _
Values ('Bill', 'Brown') "
Conn.execute "INSERT INTO MyTable (FirstName, Lastname" & _
"VALUES ('Joe', 'Thomas')"
Conn.close After adding a record to the table in this way, the format in the workbook will remain. In the previous example, the format of the new field added to the B column is set to right or right. Each record added to the row will inherit the format of the row it in front.
Note that when a record is added to one or more cells in a worksheet, the record will overwrite any data in these cells; that is, when adding new records, worksheets The line will not "go down". This should be considered when designing data in a worksheet. For additional information about using ADO access to an Excel workbook, see the following Microsoft Knowledge Base:
195951 HOWTO: Query and Update Excel Data Using ADO from ASP
Using DDE to transfer data to Excel in terms of Excel communication and transfer data, DDE is an alternative method of "automation"; however, DDE is no longer a preferred method for communicating with other application due to "automated" and COM. This method should be used only when there is no other available solution.
To use DDE to transfer the data to Excel, you can:
Use the linkPoke method to send data to a specific cell area, - or - Use the Linkexecute method to send Excel will execute the command. The following code example illustrates how to create a session of DDE and Excel to be able to send data to a unit in a worksheet, and execute commands. To use this example to successfully establish DDE with
Linktopic Excel | The session of mybook.xls, named
Mybook.xls's workbook must be opened in the running Excel instance.
Note: In this example,
Text1 represents the Visual Basic form
Text box control:
'Initiate a Dde Communication with Excel
Text1.LinkMode = 0
Text1.Linktopic = "Excel | MyBook.xls"
TEXT1.LINKITEM = "R1C1: R2C3"
Text1.LinkMode = 1
'Poke the text in text1 to the r1c1: r2c3 in mybook.xls
Text1.text = "One" & Vbtab & "Two" & Vbtab & "Three" & VBCR & _
"Four" & Vbtab & "Five" & Vbtab & "Six"
Text1.LinkPoke
'Execute Commands to SELECT CELL A1 (Same As R1c1) and Change The Font
'Format
Text1.Linkexecute "[SELECT (" R1C1 ")]"
Text1.Linkexecute "[Font.properties", "Bold", 10)] "
'Terminate The Dde Communication
Text1.LinkMode = 0 is used in Excel
When LinkPoke, you need to be in the row-column (R1C1) annotation
LinkItem specifies the area. If you want to send the data to multiple cells, you can use the column to separate, the row is separated by the carriageway.
In use
When the linkexecute requests the Excel to execute the command, you must provide Excel Macro Language (XLM) syntax command for Excel. The XLM documentation is not included in Excel 97 and later. For more information on how to get XLM documents, see the following Microsoft Knowledge Base Article: 143466 Download The Excel 97 Macro Functions Help File for XLM Documentation Not recommended Using DDE solutions with Excel communication. The Automation feature provides the greatest flexibility and allows you to access more new features provided by Excel.
Refer to additional information, click the article number below to see the article in the Microsoft Knowledge Base:
306022 How to: Use Visual Basic .NET to transfer data to the Excel workbook
