首页 > 办公 > Excel > 正文

Excel vba返回的路径

2024-08-23 19:28:23
字体:
来源:转载
供稿:网友

   ①返回应用程序完整路径

  Application.Path

  比如:D:SoftWareOffice2003OFFICE11

  ②返回当前工作薄的路径

  ThisWorkbook.Path

  比如:D:

  ③返回当前默认文件路径

  Application.DefaultFilePath

  比如:C:Documents and SettingsAdministratorMy 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

发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表