如何使用路径和文件名，你正在工作作为一个变量在VB脚本

Question

我目前正在第一次为Excel应用程序编写VB脚本。我需要得到的路径和我正在工作的文件的名称，并将其传递到一个变量，这样我就可以用它来复制一些数据回到相同的文件从另一个文件具有相同的名称在不同的文件夹。

Answer 1

如何获取工作簿的文件路径和文件名:

Option Explicit
Private Sub Path()
        Dim sFilePath As String
        Dim sFileName As String
        'This returns the path of the workbook
        'without a final  and without the name
        'i.e. C:Test
        sFilePath = ThisWorkbook.Path
        MsgBox sFilePath
        'This returns the fill path of the workbook
        'including the file name, such as
        'C:TestBook1.xlsm
        sFilePath = ThisWorkbook.FullName
        MsgBox sFilePath
        'This returns the name of the workbook
        sFileName = ThisWorkbook.Name
        MsgBox sFileName
End Sub

打开VB编辑器(按ALT+F11或在Developer选项卡下-> Visual Basic)。

右键单击Microsoft Excel Objects -> Insert -> Module并粘贴代码并运行它以查看结果。

更新

要打开带有"data"的工作簿sFileName，您可以使用&连接字符串和变量，如下所示:

Option Explicit
Private Sub Path()
        Dim sFileName As String
        'This returns the name of the workbook without .xls extension
        sFileName = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
        MsgBox "R:Downloads" & sFileName & "data.xlsx"
        Workbooks.Open Filename:="R:Downloads" & sFileName & "data.xlsx"
End Sub

设置sFileName的行简单地取book1.xlsx并返回book1。我这样做是为了处理任何文件扩展名(即，而不是查找和替换"。xlsx")。

Answer 2

类似

Dim completeName as String
completeName = ThisWorkbook.fullname
Dim file_Name as String
file_Name= ThisWorkbook.name
Dim file_path as String
file_path = ThisWorkbook.path