我想问一下您，如何用<em>VBA</em>在<em>EXCEL</em>

sPath = ThisWorkbook.Path

这一条是路径
sPath = 你要的路径

比如
sPath = "C:\文件夹名字"

sub CopyContentsTo()
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. 如果要循e799bee5baa6e997aee7ad94e59b9ee7ad94338环调用不同路径的不同文件的不同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
......
用 thisworkbook.path 应该能解决问题，是不是你少了一个路径分隔符“\”，下面这样试过吗？
thisworkbook.path & "\"

写在函数外面的dim 都是全局变量
一般都写zhidao在 最最最上面的
比如版
dim 全局变量1 as string '本模块使用
public dim 全局变量2 as string '所有模块通用
sub test()
全局变量1 ="你好"
全局变量2="我很好权"
end sub