大家好
我是因为工作需要刚接触VBA宏的菜鸟,想请教大家关于抓取外部资料的问题
程式码如下
Sub test()
'
' test 宏
Dim tb As Workbook
Set tb = ThisWorkbook
Dim ob As Object
Set ob = ThisWorkbook.Sheets(1)
Dim shell
Dim StrPathName As String
Dim StrFileName As String
Set shell = CreateObject("shell.application") _
.browseforfolder(0, "choose a folder", 0, "档案路径")
If shell Is Nothing Then
End
Else
StrPathName = shell.Items.Item.path
End If
StrFileName = Dir(StrPathName & "\2015*")
If StrFileName <> " " Then
Workbooks.Open StrPathName & "\" & StrFileName
Else
MsgBox "开启档案错误"
End If
For i = 40 To 60
ob.Cells(i, "Z") = "=vlookup(RC[-22], '[StrFileName]试算'!C4:C24,21,0)"
ob.Cells(i, "AA") = ob.Cells(i, "Z") / ob.Cells(i, "X")
Columns("AA").NumberFormatLocal = "0.00%"
Next i
End Sub
问题出在执行循环中的vlookup函数时,系统不停要我更新StrFileName的数值
请问这有什么方法可以自动更新或是不同的程式写法吗?
麻烦大家了,谢谢