The following article tells the use of Excel in VB, and how to add data to Excel, provide a variety of ways. The following is the code Summary: with Automation, you can transfer data to a worksheet One Cell At A Time: Dim OEXCEL AS OBJECT DIM OBOOK AS Object Dim Osheet As Object
'Start a new workbook in excel set out = createObject ("excel.application") set comook = 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 "Osheet.Range (" A1: B1 "). Font.bold = true osheet.range (" a2 "). Value =" doe "osheet.range (" b2 "). Value =" john "
'Save the workbook and quit excel comook.saveas "c: /book1.xls" OEXCEL.QUIT
'------------------------------------- -------------------
An Array of Data Can Be Transferred to a Range of Multiple Cells At ONCE: DIM OEXCEL AS OBJECT DIM OBOOK As Os Object Dim Osheet As Object
'Start a new workbook in excel set out = createObject ("excel.application") set comook = OEXCEL.WORKBOOKS.ADD
'Create An Array With 3 Column 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 comook.saveas "c: /book1.xls" OExcel. QuitIf 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
'------------------------------------- -------------------
Excel 2000 introduced the CopyFromRecordset method that allows you to transfer an ADO (or DAO) recordset to a range on a worksheet. The following code illustrates how you could automate Excel 2000, Excel 2002, or Office Excel 2003 and transfer the contents of the Orders table in the Northwind Sample Database 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 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 = 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 comook.saveas "c: /book1.xls" OExcel.quit'close the connection quit'close conn.close
'------------------------------------- -------------------
The following code demonstrates how you could automate Excel 2000, Excel 2002, or Office Excel 2003 to create a new QueryTable in an Excel worksheet 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 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. OLDB.4.0; Data Source = "& _ Snwind &"; Osheet.Range ("A1"), "Select * from Orders" oqrytable.refreshstyle = Xlinsertirerows oqrytable.refresh False
'Save the workbook and quit excel comook.saveas "c: /book1.xls" OEXCEL.QUIT
'------------------------------------- -------------------
Use the ClipboardThe Windows Clipboard can also be used as a mechanism for transferring data to a worksheet. To paste data into multiple cells on a worksheet, you can copy a string where columns are delimited by tab characters and rows are delimited by carriage returns. The following code illustrates how Visual Basic can use 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.clearClipboard.Settext SDATA
'Create a New Workbook in Excel Dim OEXCEL As Object Dim Obook As Object Set Oexcel = CreateObject ("Excel.Application") Set Obook = OEXCEL.WORKS.ADD
'Paste The Data Obook.Worksheets (1) .Range ("a1"). Select Obook.worksheets (1) .paste
'Save the workbook and quit excel comook.saveas "c: /book1.xls" OEXCEL.QUIT
'------------------------------------- -------------------
'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 = 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
'------------------------------------- -------------------
'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 comook = OEXCEL.WORKBOOKS.OPEN ("c: /test.txt")
'Save as Excel Workbook and Quit Excel Obook.saveas "C: /book1.xls", XLWORKBOOKNORMAL OEXCEL.QUIT
'------------------------------------- -------------------
'Create a new connection object for book1.xls Dim conn as new adodb.connection conn "provider = microsoft.jet.Oledb.4.0;" & _ "data source = c: /book1.xls; extended property = Excel 8.0 "Conn.execute" Insert Into MyTable (Firstname, Lastname) "& _" Values ('Bill', 'Brown' Insert Into MyTable (Firstname, Lastname "& _" Values ('Joe' , 'Thomas') "Conn.close
'------------------------------------- -------------------
'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 "[font.properties" [font.properties " Times New Roman "", "Bold", 10)] "
'Terminate The Dde Communication Text1.LinkMode = 0' --------------------------------------- ----------------------------- full text is http://support.microsoft.com/kb/247412/en-us/
