VBS读取excel写入到数组(转)

t1k H9lSpS8QJ1^p w!m0’**************************************** 

]bP'g#v/|;~Y0' 输入：
#Z+Y HqBo0'             sFileName： Excel文件51Testing软件测试网7w;PK!Mh
'             sSheetName：表单名称
!DqJg`m
yv0' 返回：
y6O j$z3@W T0'             包含Excel数据的数组

7O s|H(O)~%l.V0'*****************************************
kN%K@_qh-T+o0Function ReadFile(sFileName,sSheetName)51Testing软件测试网gZ%O;MM},e
       Dim oExcel51Testing软件测试网^m5hzJ W}6D^zU
       Dim oSheet51Testing软件测试网1X8ZZ?wr*@4v%wY
       Dim oRange51Testing软件测试网+M"b3E] _
       Dim arrRange
1u-?*K1}*bk+}9M0 
4gT/Kw*Dz_Ss4}0       On Error Resume Next51Testing软件测试网BDC:h:sX_
           ' 创建Excel应用程序对象
w%j f[l'Aq0              Set Excel = CreateObject("Excel.Application")51Testing软件测试网ud;i4{$nF_/i
              If err.Number <> 0 Then51Testing软件测试网 IJ0Z\1b(y@ [d:S
                     MsgBox "未能初始化Excel" & vbCrLf & _51Testing软件测试网.dCt(f/]*h#D
                                 "请确保Excel已安装", vbCritical51Testing软件测试网4?y?-XyY1f
                     Exit Function51Testing软件测试网4x`;I7K/sJ6QK
              End If
q(C~k0^1W0e3V0       On Error Goto 0
i,G/pmQ4a+H0                   
__
gVIH7a2I0       On Error Resume Next
~4Dp,dQI%T0                     ' 打开Excel文件51Testing软件测试网{4Z(tHdVPk8n\f
                     oExcel.Workbooks.Open(sFileName)51Testing软件测试网k?!]S6Q1h
                  If err.Number <> 0 Then51Testing软件测试网DD1N`vp[8xqr
                            MsgBox "未能加载Excel文件" & vbCrLf & _51Testing软件测试网fJ)b,[3t5O)gHF
                                   "请确保Excel文件路径正确或格式正确", vbCritical51Testing软件测试网;Bl E!x0@P#@
                            Exit Function
-{%[1P&U,XW%rhR
dQ0                     End If
J
B2gq1E
m0       On Error Goto 0
lo;X h}n,s NVi~-I0     
2}gg^6zf1E6]sVS0       ' 获取表格的使用范围
)M;I P0U;X6\f6t0       Set Sheet = oExcel.Worksheets(sSheetName).UsedRange51Testing软件测试网k2l4I9To}
       ' 获取从A列到Z列，从第1行到第1000行的范围i中的所有值
6A#p X5A|Nq3t0       Set Range = oSheet.Range("A1:Z1000")
n{.VN
W-af&mYF0       '把Excel数据转换到数组51Testing软件测试网C%_K}8G*T1|3zB
       arrRange = oRange.Value51Testing软件测试网'JI,Di\]
N
       ' 关闭工作簿51Testing软件测试网9n)~8ewhg
       oExcel.WorkBooks.Item(1).Close
}T9m"y%N){VJ`0       ' 退出Excel
u5`w~o&j?!J0       oExcel.Quit
;zx1`+S@1u8^0       Set Excel = Nothing
2dhm8S\{)[T0    ' 返回包含Excel数据的数组
b'b(j{9] j_0     ReadFile = arrRange51Testing软件测试网R%Ux8ls3e iv,c$W
End Function

 51Testing软件测试网&}W l mN:cMC&y

7`| O4Lc@0示例：读某一单元格的数据51Testing软件测试网'wXvZv-Z3pQN

arrRange = ReadFile("E:\test.xls","Sheet1")51Testing软件测试网QF o#H7}/DGo
'arrRange为2维数组51Testing软件测试网(KqwKj8vm-p5X
Msgbox arrRange(2,1)51Testing软件测试网/IB:`;u)| GH n