vb: how to read from excel and write to excel?

This site provides users with the information about vb, visual basic, vb6, read from excel file, write to excel file, how to read from excel file, how to write to excel file, and more.

If you think that this site is helpful, please recommend your friends to visit our site.


How to read from excel file and write to excel file?

The following is the code for reading from excel file and writing to excel file:

1. Read from excel file


--------------------------------------------------------------------------------
' in Project-Reference- select Microsoft DAO 3.6 Object library

Dim myXL As Object ' Declare an object variable.
Public dbCurrent As Database
Public recCategories As Recordset

Private Sub Command1_Click()
Dim OutString, OutString1 As String
Set myXL = CreateObject("Excel.Application")
Workbooks.Open ("C:\test\test.xls")
OutString = CStr(myXL.ActiveSheet.Range("a2"))
OutString1 = CStr(myXL.ActiveSheet.Range("b2"))

'write to database

Set dbCurrent = OpenDatabase(App.Path & "\test.mdb")
Set recCategories = dbCurrent.OpenRecordset("excel")
Set recCategories = dbCurrent.OpenRecordset("excel")

recCategories.AddNew 'myXL.ActiveSheet.Cells("b2")
recCategories.Fields("Day") = OutString
recCategories.Fields("Amount") = OutString1

recCategories.Update
dbCurrent.Close
Set dbCurrent = Nothing
End Sub
--------------------------------------------------------------------------------

The following is an example of reading all the records (rows)

--------------------------------------------------------------------------------
Private Sub Command1_Click()
filepath = App.Path & "\tele.xls"
sheetname = "Sheet1$"
Set db = OpenDatabase(filepath, False, False, "Excel 8.0;HDR=yes;")
Set rs = db.OpenRecordset(sheetname)
rs.MoveFirst
Screen.MousePointer = 11
While rs.EOF <> True
List1.AddItem rs.Fields("Create Date") & " " & rs.Fields("AcctNo")

If (rs.Fields("Create Date") = "20000101") Then
Text1.Text = "cell is ok"
End If

rs.MoveNext
DoEvents
Wend
Screen.MousePointer = 0
End Sub

--------------------------------------------------------------------------------

2. Write to excel file

--------------------------------------------------------------------------------
' in Project-Reference- select Microsoft DAO 3.6 Object library

Option Explicit

Private tApp As Excel.Application
Private tWB As Excel.Workbook

Set tApp = New Excel.Application
tApp.Visible = False

Set tWB = tApp.Workbooks.Add
tApp.Visible = True
tWB.Sheets("Sheet1").Cells(1, 1).Value = "test text"

Set tWB = Nothing
Set tApp = Nothing