vb: how to read from excel and write to excel?
Website Design
Website Promotion
Graphic Design
Programming
Free Software
Computer Tips
Discount Stores
|
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
|