[VBA ] Excel巨集請教

[amanda1126 (香)]

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

麻煩大家了,謝謝

--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 1.163.101.45
※ 文章網址: https://www.ptt.cc/bbs/Visual_Basic/M.1548168439.A.7A9.html
→ newacc: 因為你的StrFileName是字串的一部分
→ newacc: 用"=vlookup(...["& StrFileName & "]試算......"試試