dim sPath$,sFilename$,sSheetname$
dim sTemp$,i%
sPath="H:\HRleader\DOCMENT\shift handover report\LINE\L18-DG DN" '可以自己修改
sFilename="L18-DN.xls" ‘同上
sSheetname="October" ’同上
sTemp="'"& sPath & "\[" & sFilename & "]" & sSheetname & "'!"
With Thisworkbook.Worksheets("Sheet1")
For i=5 to 80
With .Range("A"& i - 3 )
.FormulaR1C1="=" & Temp & "$C$" & i
.value=.value
End With
Next i
End With
End Sub
P.S. 如果要循環調用不同路徑的不同文件的不同Sheet，
可以給sPath,sFilename,sSheetname賦數組，
如
dim i%,Sheetname(1 to 12)
For i = 1 to 12
Sheetname(i)=Array ("January","February","March","April","May","June", _
"July","August","September","October","November","December")(i)
Next i
則可以在後續的程序中調用Sheetname(1)到Sheetname(12)
如
.....
For k = 1 to 12
sTemp="'"& sPath(i) & "\[" & sFilename(j) & "]" & sSheetname(k) & "'!"
.......sTemp......
Next k
......