大家好
我是因為工作需要剛接觸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的數值
請問這有甚麼方法可以自動更新或是不同的程式寫法嗎?
麻煩大家了,謝謝