Sub 自动取数()
On Error GoTo line
Dim Wb As
Workbook
'定义工作簿变量
Dim Temp As
String
'定义文本变量,零时存放文件地址
Dim arr(136 To 147) As
Double
'定义双精度变量,用于零时存放单元格值
Dim i%
'定义整数变量
Dim j$
'定义字符型变量,用于零时存放表格名称,如"华信","江苏"等
Application.ScreenUpdating = False
'关闭刷屏
i = 3
'从第3列,即C列开始填写
Do While Cells(2, i)
<> "备注"
'在备注之前的数据都要填写
j = Cells(2, i)
'取得第2行第i列单元格的值,如"华信","江苏"等
Temp = ThisWorkbook.Path & "\" & j &
".xls"
Set Wb = GetObject(Temp)
'打开相应的工作簿
If
Wb.Worksheets("sheet1").Range("G9").Value <> "" Then
Cells(136, i) = Wb.Worksheets("sheet1").Range("G9").Value /
10000
Wb.Close False
'关闭工作簿
Set Wb = Nothing
'释放变量
Workbooks("年报对标.xlsm").Activate
i = i + 1
Loop
line:
MsgBox "名称为【" & j & "】的文件不存在!"
Application.ScreenUpdating = True
End Sub
加载中,请稍候......