vba 如何使用vba获取当前工作目录？

How to get current working directory using vba?

提问by Ullan

I am using MS Excel 2010 and trying to get the current directory using the below code,

我正在使用 MS Excel 2010 并尝试使用以下代码获取当前目录，

    path = ActiveWorkbook.Path

But ActiveWorkbook.Path returns blank.

但 ActiveWorkbook.Path 返回空白。

回答by jacouh

I've tested this:

我已经测试过这个：

When I open an Excel document D:\db\tmp\test1.xlsm:

当我打开一个 Excel 文档时D:\db\tmp\test1.xlsm：

• CurDir()returns C:\Users\[username]\Documents

• ActiveWorkbook.Pathreturns D:\db\tmp

• CurDir()返回 C:\Users\[username]\Documents

• ActiveWorkbook.Path返回 D:\db\tmp

So CurDir()has a system default and can be changed.

所以CurDir()有一个系统默认值，可以更改。

ActiveWorkbook.Pathdoes not change for the same saved Workbook.

ActiveWorkbook.Path对于同一个保存的工作簿，不会更改。

For example, CurDir()changes when you do "File/Save As" command, and select a random directory in the File/Directory selection dialog. Then click on Cancel to skip saving. But CurDir()has already changed to the last selected directory.

例如，CurDir()当您执行“文件/另存为”命令并在文件/目录选择对话框中选择一个随机目录时会发生变化。然后单击取消跳过保存。但是CurDir()已经改到上次选择的目录了。

回答by AndASM

You have several options depending on what you're looking for. Workbook.Pathreturns the path of a saved workbook. Application.Pathreturns the path to the Excel executable. CurDirreturns the current working path, this probably defaults to your My Documents folder or similar.

您有多种选择，具体取决于您要查找的内容。 Workbook.Path返回已保存工作簿的路径。Application.Path返回 Excel 可执行文件的路径。CurDir返回当前工作路径，这可能默认为您的我的文档文件夹或类似文件夹。

You can also use the windows scripting shell object's .CurrentDirectory property.

您还可以使用 Windows 脚本外壳对象的 .CurrentDirectory 属性。

Set wshell = CreateObject("WScript.Shell")
Debug.Print wshell.CurrentDirectory

But that should get the same result as just

但这应该得到与刚才相同的结果

Debug.Print CurDir

回答by Monty Wild

It would seem likely that the ActiveWorkbook has not been saved...

ActiveWorkbook 似乎尚未保存...

Try CurDir()instead.

试试吧CurDir()。

回答by Mohamed Tahir

Your code: path = ActiveWorkbook.Path

您的代码： path = ActiveWorkbook.Path

returns blank because you haven't saved your workbook yet.

返回空白，因为您还没有保存您的工作簿。

To overcome your problem, go back to the Excel sheet, save your sheet, and run your code again.

要解决您的问题，请返回 Excel 工作表，保存您的工作表，然后再次运行您的代码。

This time it will not show blank, but will show you the path where it is located (current folder)

这次它不会显示为空白，而是会显示它所在的路径（当前文件夹）

I hope that helped.

我希望这有帮助。

回答by Agus Sapurta Sijabat

Use Application.ActiveWorkbook.Pathfor just the path itself (without the workbook name) or Application.ActiveWorkbook.FullNamefor the path with the workbook name.

使用Application.ActiveWorkbook.Path的只是路径本身（没有工作簿的名称），或者Application.ActiveWorkbook.FullName与工作簿的名称的路径。

回答by ashleedawg

This is the VBA that I use to open the current pathin an Explorerwindow:

这是我用来在资源管理器窗口中打开当前路径的 VBA ：

Shell Environ("windir") & "\explorer.exe """ & CurDir() & "",vbNormalFocus

---

---

Microsoft Documentation:

微软文档：

• CurDirFunction
• EnvironFunction
• ShellFunction

• CurDir功能
• Environ功能
• Shell功能

回答by NOTSermsak

If you really mean pure working Directory, this should suit for you.

如果您的意思是纯粹的工作目录，这应该适合您。

Solution A:

解决方案一：

Dim ParentPath As String: ParentPath = "\"
Dim ThisWorkbookPath As String
Dim ThisWorkbookPathParts, Part As Variant
Dim Count, Parts As Long

ThisWorkbookPath = ThisWorkbook.Path
ThisWorkbookPathParts = Split(ThisWorkbookPath, _
                        Application.PathSeparator)

Parts = UBound(ThisWorkbookPathParts)
Count = 0
For Each Part In ThisWorkbookPathParts
    If Count > 0 Then
        ParentPath = ParentPath & Part & "\"
    End If
    Count = Count + 1
    If Count = Parts Then Exit For
Next

MsgBox "File-Drive = " & ThisWorkbookPathParts _
       (LBound(ThisWorkbookPathParts))
MsgBox "Parent-Path = " & ParentPath

But if don't, this should be enough.

但如果没有，这应该就足够了。

Solution B:

解决方案B：

Dim ThisWorkbookPath As String

ThisWorkbookPath = ThisWorkbook.Path
MsgBox "Working-Directory = " & ThisWorkbookPath 

回答by josef

Use these codes and enjoy it.

使用这些代码并享受它。

Public Function GetDirectoryName(ByVal source As String) As String()
Dim fso, oFolder, oSubfolder, oFile, queue As Collection
Set fso = CreateObject("Scripting.FileSystemObject")
Set queue = New Collection

Dim source_file() As String
Dim i As Integer        

queue.Add fso.GetFolder(source) 'obviously replace

Do While queue.Count > 0
    Set oFolder = queue(1)
    queue.Remove 1 'dequeue
    '...insert any folder processing code here...
    For Each oSubfolder In oFolder.SubFolders
        queue.Add oSubfolder 'enqueue
    Next oSubfolder
    For Each oFile In oFolder.Files
        '...insert any file processing code here...
        'Debug.Print oFile
        i = i + 1
        ReDim Preserve source_file(i)
        source_file(i) = oFile
    Next oFile
Loop
GetDirectoryName = source_file
End Function

And here you can call function:

在这里你可以调用函数：

Sub test()
Dim s
For Each s In GetDirectoryName("C:\New folder")
Debug.Print s
Next
End Sub