Public ExcelTable As Workbook 'excel 工作表对象
Private TextBoxA() As Control '以编辑框为基础动态构造数据网格
Private FormWidth As Integer '窗体宽度
'以下子程序是用来建立数据网格
Sub CreateGrid(No, Data)
For i = 1 To CInt (UBound(Data, 1))
For j = 1 To CInt (UBound(Data, 2))
Set a = Form1.Controls.Add( "VB.TextBox" , "textbox" & CStr (i) & CStr (j) & CStr (No))
ReDim TextBoxA(1 To i, 1 To j) '重新定义动态数组!
Set TextBoxA(i, j) = a
With TextBoxA(i, j) '设置文本框属性
.Text = Data(i, j)
.Visible = True
.Height = 200
.Width = 500
.Top = .Height * (i - 1)
.Left = .Width * (j - 1) + FormWidth
End With
Next
Next
End Sub
Private Sub Command1_Click()
Set ExcelTable = CreateObject( "Excel.sheet" ) '建立对象实例
ExcelTable.Application.Workbooks.Open (App.Path + "\address.xls" ) '打开数据文档address.xls
For i = 1 To ExcelTable.Application.Worksheets.Count '获得工作表数目
Data = ExcelTable.Application.Worksheets(i).UsedRange.Value '获取每个工作表的数据
DataType = VarType(Data) '跳过没有数据的空表
Select Case DataType
Case vbArray + vbVariant
Call CreateGrid(i, Data) '传递数据,建立以文本框为基础的数据网格
Case vbEmpty
End Select
Next
End Sub
|