作者amanda1126 (香)
看板Visual_Basic
标题[VBA ] Excel巨集请教
时间Tue Jan 22 22:47:17 2019
大家好
我是因为工作需要刚接触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://webptt.com/cn.aspx?n=bbs/Visual_Basic/M.1548168439.A.7A9.html
1F：→ newacc: 因为你的StrFileName是字串的一部分 01/23 21:16
2F：→ newacc: 用"=vlookup(...["& StrFileName & "]试算......"试试 01/23 21:18