|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 xyd617 于 2018-12-14 22:31 编辑
代码如下:多工作簿提取数字,但是有些工作簿不满足条件,取不到数字,无法忽略,导致出错,3021 如何解决?
Sub zd2() '2-2?????????
Dim cnn As Object
Dim sql$, mypath$, myname$
Dim arr, brr(1 To 100000, 1 To 9) '16?????????????????????????
Dim rs As Object
Application.ScreenUpdating = False
Set cnn = CreateObject("ADODB.Connection")
mypath = ThisWorkbook.Path & "\"
myname = Dir(mypath & "*.xl*")
Do While myname <> ""
If myname <> ThisWorkbook.Name Then
a = Split(myname, ".")(0)
n = n + 1
If n = 1 Then
cnn.Open "provider=microsoft.ace.oledb.12.0;extended properties='Excel 12.0;hdr=NO';data source=" & mypath & myname
Else
t = "[Excel 12.0;hdr=NO;Database=" & mypath & myname & ";]."
End If
sql = "select iff([F1] is null,"",F1),F2,F3,F4,F5,F6,F41,F42 from " & t & "[?????????$A9:BF208] where left(F1,2)<>'???'" '????????????
Set rs = cnn.Execute(sql)
arr = rs.GetRows
For i = 0 To UBound(arr, 2)
M = M + 1
brr(M, 1) = a
For j = 0 To 7 '14???16-2??
brr(M, j + 2) = arr(j, i)
Next
Next
End If
myname = Dir()
Loop
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Sheets("2-2?????????").Range("a4:i100000") = "" '?????????????????
Sheets("2-2?????????").Range("a4").Resize(M, 9) = brr '16?????????????????????????
Application.ScreenUpdating = True
End Sub
|
|