Use VB to write data in the control to Excel row by row and column by column

Use VB to write the 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

How to import the data calculated in vb into excel

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

VB saves array data to Excel

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!