VBA 将完整文件名拆分为列(目录、文件夹、文件夹、文件名、扩展名等)

VBA 将完整文件名拆分为列(目录、文件夹、文件夹、文件名、扩展名等)

无法从 Google 找到答案。尝试编写一个 VBA excel 宏,根据斜线“\”将完整文件名拆分为多个列...示例:在一个特定文件夹中,我有 100 个文件。想将这 100 个文件名拆分为:

     Filename:  C:\Users\Public\Finance\Reports\BalanceSheet.xls

A 列:Z:\Users\Public\Finance\Reports\BalanceSheet.xls

B 列: Z:

C 列:用户

D 列:公共

E 栏:财务

F 列:报告

G 列:资产负债表.xls

H 列:xls

当然,对文件夹中的所有文件(目前为 100 个文件)执行此操作。我知道 AF 列是重复的,但请原谅我。

谢谢你!!

答案1

创建新模块(通用模块,不是类模块!)。将以下代码插入其中:

Public Function GetComponent(str As String, delim As String, num As Integer) As String
Dim tmp() As String
On Error GoTo ErrorHandler
tmp = Split(str, delim)
Select Case num
    Case 0 To UBound(tmp)
        GetComponent = tmp(num)
    Case UBound(tmp) + 1
        GetComponent = Split(tmp(num - 1), ".")(1)
    Case Else
        GetComponent = ""
    End Select
Exit Function
ErrorHandler:
GetComponent = ""
End Function

现在,此功能在公式生成器中的用户定义函数部分中可用。

如果源数据在 A1 中,则输入公式(使用公式生成器或“手动”输入)

=GetComponent($A1,"\",COLUMN()-2)

放入 B1 单元格,然后将其水平渐变到足以显示所有文件名组件。

答案2

拆分filename,循环遍历每个部分来设置单元格的值。

Public Sub SplitName()

Dim filename As String

filename = "C:\Users\Public\Finance\Reports\BalanceSheet.xls"
Dim columnsname() As String

columnsname = Split(filename, "\")

ActiveSheet.Cells(1, 1).Value = filename 'Set value for Column A

Dim count As Integer
count = 2 'Column counter: will start with Column B

For Each part In columnsname

ActiveSheet.Cells(1, count).Value = part
count = count + 1

Next

columnsname = Split(filename, ".")
ActiveSheet.Cells(1, count).Value = columnsname(1)

End Sub

答案3

Sub File_SplitName()

     Dim fileName_Full As String
     Dim fileName_Component() As String

     fileName_Full = ActiveWorkbook.FullName
     fileName_Component = Split(fileName_Full, "\")

     On Error Resume Next

    Sheets("B").Range("AI61").Value = fileName_Full
    Sheets("B").Range("AI62").Value = fileName_Component(0)
    Sheets("B").Range("AI63").Value = fileName_Component(1)
    Sheets("B").Range("AI64").Value = fileName_Component(2)
    Sheets("B").Range("AI65").Value = fileName_Component(3)
    Sheets("B").Range("AI66").Value = fileName_Component(4)

End Sub

答案4

另一种方法:只需使用文本到列的方法:

Option Explicit
Sub pathSplitter()
    Dim rPaths As Range
    Dim ws As Worksheet

'Declare worksheet and range to split
Set ws = Worksheets("sheet2") 'adjust as needed

With ws 'assumes range starts in A1.  Adjust if that is not the case
    Set rPaths = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1))
End With

'set all the arguments so as to avoid any interference from previous usages
rPaths.TextToColumns Destination:=Cells(1, 2), DataType:=xlDelimited, textqualifier:=xlTextQualifierDoubleQuote, _
     consecutivedelimiter:=False, Tab:=False, semicolon:=False, comma:=False, Space:=False, _
     other:=True, otherchar:="\"

ws.UsedRange.EntireColumn.AutoFit
End Sub

相关内容