只要解决问题,立刻结贴!另外送1000可用分!
有一个excel文件1.xls(有n个sheet),需要用vb读出每个sheet中每一个单元格里的数据(全是数值,无字符串).先用commondialog对话框打开文件,然后把数据读出来,赋值到一个2维数组.如果发现有字符串,则给出出错信息,指出1.xls的哪个sheet的哪行哪列的数据有错误.
要求给出代码,并有详细注释.如果要引用其他部件或控件,请详细说明.在线等!
有一个excel文件1.xls(有n个sheet),需要用vb读出每个sheet中每一个单元格里的数据(全是数值,无字符串).先用commondialog对话框打开文件,然后把数据读出来,赋值到一个2维数组.如果发现有字符串,则给出出错信息,指出1.xls的哪个sheet的哪行哪列的数据有错误.
要求给出代码,并有详细注释.如果要引用其他部件或控件,请详细说明.在线等!
Public Function ExcelToAry(FileName As String, ByRef RowCount As Integer, ByRef ColCount As Integer) As Variant()
Dim TextAry() As Variant
RowCount = 0
ColCount = 0
Dim oExcel As Excel.Application '用前期绑定
Set oExcel = New Excel.Application Dim wsBook As Workbook '定义工作簿
Dim wsSheet As Worksheet '定义工作表
With oExcel
.Visible = False
Set wsBook = .ActiveWorkbook
Set wsBook = .Workbooks.Open(FileName)
Set wsSheet = .ActiveWorkbook.Sheets(1) '用变量代替 1 ,实现不同sheet 的内容
End With
With wsSheet
If .Cells(1, 1) = "" Then
MsgBox "请从第一个单元格开始填写数据", vbOKOnly
Exit Function
End If
.Cells(1, 1).Select
RowCount = .Cells.CurrentRegion.Rows.Count
ColCount = .Cells.CurrentRegion.Columns.Count
ReDim TextAry(1 To RowCount, 1 To ColCount)
For i = 1 To RowCount
For j = 1 To ColCount
if isnumeric(Cells(i, j))=false then
msgbox "sheet" & 当前sheet & i & "," & j & "
出错",vbokonly
else
TextAry(i, j) = Cells(i, j)
endif
Next j
Next i
End With
ExcelToAry = TextAry
wsBook.Close False
oExcel.QUIT '退出
'释放内存
If Not (wsBook Is Nothing) Then
Set wsBook = Nothing ' Remove object variable
End If
If Not (wsSheet Is Nothing) Then
Set wsSheet = Nothing
End If
If Not (oExcel Is Nothing) Then
Set oExcel = Nothing ' Remove object variable
End If
end function
Dim ColCount As Long, RowCount As Long, k As Long, kk As Long
Dim xlApp As New Excel.Application, xlBook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim ArrTemp() As String xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Open(App.Path & "\KK.XLS")
Set xlsheet = xlBook.Worksheets(1)
ColCount = xlsheet.UsedRange.Cells.Columns.Count '得到总列
RowCount = xlsheet.UsedRange.Cells.Rows.Count '得到总行 ReDim ArrTemp(ColCount, RowCount)
For k = 1 To ColCount
For kk = 1 To RowCount
Debug.Print VarType(xlsheet.Cells(kk, k)) & " " & xlsheet.Cells(kk, k)
Select Case VarType(xlsheet.Cells(kk, k))
Case 0 '空
ArrTemp(k, kk) = "这是空的" '
Case 5 '数字型
ArrTemp(k, kk) = xlsheet.Cells(kk, k) '将所有信息放到ArrTemp这个数据中
Case 8 '字符型
ArrTemp(k, kk) = "这是字符" '
End Select
Next kk
Next k xlBook.Close
xlApp.Quit
Set xlApp = Nothing
MsgBox "完成"End Sub
看来就来晚了,只能蹭分了
哈哈。
找本vba都会了