无法从 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