Home > Article > Computer Tutorials > Use VB to write data in the control to Excel row by row and column by column
Please quote excel,
dim xls as new excel.application
dim wb as excel.workbook
dim sht as excle.worksheet
'The above is the form variable
'form load
set wb =xls.workboods.add("F:\data.xls")
set sht =wb.worksheets(1)
'---' button:
dim r as integer
r=sht.range("A65536").end(xlsup).row 1
sht(r,1)=text1.text
sht(r,2)=text2.text
wb.save
'---when form unload
wb.close
xls.quit
Option Explicit
Dim xls As Excel.Application Dim wb As Excel.Workbook Dim sht As Excel.Worksheet
Private Sub Command1_Click() TryOpenXls Dim r As Integer r = sht.Range("A65536").End(xlUp).Row 1 If r = 2 And sht.Range("A1").Value = """ And sht.Range("B1").Value = "" Then r = 1 sht.Cells(r, 1) = Text1.Text sht.Cells(r, 2) = Text2.Text wb.Save End Sub
Private Sub TryOpenXls() On Error Resume Next Dim x As String Dim path As String path = App.path & "\abc.xls" Err.Clear x = xls.Name If Err.Number 0 Then Set xls = New Excel.Application End If x = wb.Name If Err.Number 0 Then On Error GoTo 0 If Len(Dir(path)) = 0 Then Set wb = xls.Workbooks.Add wb.SaveAs path Else Set wb = xls.Workbooks.Open(path) End If End If
On Error GoTo 0 Set sht = wb.Worksheets(1) End Sub
Private Sub Form_Unload(Cancel As Integer) On Error Resume Next Set sht = Nothing If Not wb Is Nothing Then wb.Save: wb.Close If Not xls Is Nothing Then xls.Quit Set wb = Nothing Set xls = Nothing End Sub
I didn’t read your program
Give you some reference
Leave a message if you have any questions
set e = CreateObject("Excel.Application")
e.visible = 0
set nb = e.workbooks.add
set ns = nb.worksheets(1)
for i = 1 to 10
for j = 1 to 10
ns.cells(i,j) = cstr(i) & "," & cstr(j)
next
next
ns.saveas "c:\a.xlsx"
e.quit
set e = CreateObject("Excel.Application")
e.visible = 0
set nb = e.workbooks.add
set ns = nb.worksheets(1)
for i = 1 to 10
for j = 97 to 105
k = chr(j)
ns.range(k&i).value = "The"&i&"th"
next:next
ns.saveas "c:\a.xls"
e.quit
Private Sub Command1_Click()
Dim XlApp As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
XlApp.Visible = True
Set xlBook = XlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
For i=1 to 20 'Your array has 21 numbers, not 20 numbers
xlSheet.Range(xlSheet.Cells(1,i).Value=a1(i)
xlSheet.Range(xlSheet.Cells(2,i).Value=b1(i)
xlSheet.Range(xlSheet.Cells(3,i).Value=c1(i)
xlSheet.Range(xlSheet.Cells(4,i).Value=d1(i)
xlSheet.Range(xlSheet.Cells(5,i).Value=e1(i)
xlSheet.Range(xlSheet.Cells(6,i).Value=f1(i)
Next i
End Sub
The above is the detailed content of Use VB to write data in the control to Excel row by row and column by column. For more information, please follow other related articles on the PHP Chinese website!