VBA从路径获取文件名并将其存储到单元格 [英] VBA Get File Name From Path and Store it to a Cell

问题描述

我正在处理一些代码，希望找到所选文件的路径，提取文件名，然后将文件名写入工作表中的单元格.这是我到目前为止的内容:

I'm working on some code that I would like to find the path of a selected file, extract the file name, and then write the file name to a cell on the sheet. Here's what I have so far:

Private Sub CommandButton3_Click()

Sheets("Raw Data").Unprotect

Application.DisplayAlerts = False
Sheets("Raw Data").Delete
Sheets.Add After:=Worksheets(1)
Worksheets(2).Name = "Raw Data"
Application.DisplayAlerts = True

Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim n As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant

    SaveDriveDir = CurDir
    MyPath = "H:"
    ChDrive MyPath
    ChDir MyPath

    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", MultiSelect:=True)
    If IsArray(FName) Then
        Application.ScreenUpdating = False
        Set basebook = ThisWorkbook
        For n = LBound(FName) To UBound(FName)
            Set mybook = Workbooks.Open(FName(n))
            Set sourceRange = mybook.Worksheets(1).Cells
            SourceRcount = sourceRange.Rows.Count
            Set destrange = basebook.Sheets("Raw Data").Cells
            sourceRange.Copy destrange
            mybook.Close True
        Next
    End If

    ChDrive SaveDriveDir
    ChDir SaveDriveDir
    Application.ScreenUpdating = True

    Sheets("Main").Select
    Cells(5, 4).Value = FName

    Sheets("CS-CRM Raw Data").Select
    ActiveSheet.Cells(1, 1).Select

Sheets("Raw Data").Protect

End Sub

到目前为止，代码将从此行获取路径:

So far the code will get the path from this line:

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", MultiSelect:=True)

它将通过以下几行将其写入单元格:

And it will write it to a cell with these lines:

Sheets("Main").Select
Cells(5, 4).Value = FName

但是，每次我尝试获取它而只是获取文件名时，它都不起作用.我将收到一条错误消息，或者它将再次发布整个路径.有人知道最好的方法吗?

However, every time I try to get it to just get the file name it doesn't work. I'll either get an error message or it will just post the entire path again. Does anyone know the best way to do this?

推荐答案

这里是一种将 GetOpenFileName()的结果解析为三个部分的方法:

Here is a way to parse the result of GetOpenFileName() into three parts:

1. 路径
2. 文件名
3. 文件扩展名

..

Sub qwerty()
    Dim f As String, Path As String, _
        FileName As String, FileType As String

    f = Application.GetOpenFilename()

    ary = Split(f, "\")
    bry = Split(ary(UBound(ary)), ".")
    ary(UBound(ary)) = ""
    Path = Join(ary, "\")
    FileName = bry(0)
    FileType = bry(1)

    Range("A1") = Path
    Range("A2") = FileName
    Range("A3") = FileType
End Sub

例如:

这篇关于VBA从路径获取文件名并将其存储到单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！