EXCEL VBA:返回路径

2013-08-17 16:50 阅读 8,207 次 评论关闭

VBA返回相应的路径信息,对于在Excel中使用编程来处理数据,极其重要。

下面,为你介绍与返回路径相关的各种代码与函数。

①返回应用程序完整路径

Application.Path

比如:D:\SoftWare\Office2003\OFFICE11

②返回当前工作薄的路径

ThisWorkbook.Path

比如:D:

③返回当前默认文件路径

Application.DefaultFilePath

比如:C:\Documents and Settings\Administrator\My Documents

④只返回路径

Application.ActiveWorkbook.Path

比如:D:

⑤返回路径及工作簿文件名

Application.ActiveWorkbook.FullName

比如:D:\Book1.xls

⑥返回工作簿文件名

Application.ActiveWorkbook.Name

比如:Book1.xls

上面的六种情况,在VBA环境下,你也可以通过如下的代码测试:

MsgBox Application.Path
ActiveSheet.Cells(1, 1).Value = Application.Path

MsgBox ThisWorkbook.Path
ActiveSheet.Cells(2, 1).Value = ThisWorkbook.Path

MsgBox Application.DefaultFilePath
ActiveSheet.Cells(3, 1).Value = Application.DefaultFilePath

MsgBox Application.ActiveWorkbook.Path
ActiveSheet.Cells(4, 1).Value = Application.ActiveWorkbook.Path

MsgBox Application.ActiveWorkbook.FullName
ActiveSheet.Cells(5, 1).Value = Application.ActiveWorkbook.FullName

MsgBox Application.ActiveWorkbook.Name
ActiveSheet.Cells(6, 1).Value = Application.ActiveWorkbook.Name

除此之外,和路径有关的一个函数Dir,是专门用来判断文件是否存在的函数,代码如下:

Application.ScreenUpdating = False
With Application.FileSearch
.FileType = msoFileTypeExcelWorkbooks
.LookIn = ThisWorkbook.Path
.SearchSubFolders = True
.Execute
If .Execute() > 0 Then
m = .FoundFiles.Count '当前目录及子目录所有工作薄总数
'MsgBox m
For Each f In .FoundFiles '在所有的工作薄里做一个循环
'MsgBox Dir(f) '列出每个工作薄的名称,只显示名称,如 dzwebs.xls ,无路径
If (Dir(f) <> "Total.xls") Then
Set xlsApp = New Excel.Application
Set xlsBook = Workbooks.Open(ThisWorkbook.Path & "\" & Dir(f))
Set MyWantGetsheet = xlsBook.Worksheets(1)

For i = 1 To 8
MyWantGetsheet.Cells(i, 6).Value = "www.dzwebs.net"
Next i
xlsBook.Save
xlsBook.Close
xlsApp.Quit
Set xlsApp = Nothing
Set xlsBook = Nothing
Set MyWantGetsheet = Nothing
Application.ScreenUpdating = True
End If
Next
End If

版权声明:本文著作权归原作者所有,欢迎分享本文,谢谢支持!
转载请注明:EXCEL VBA:返回路径 | 猎微网
分类:Excel VBA 标签:, , ,

评论已关闭!