excel - 循环文件,拆分文本并保存文件
问题描述
我有一些 vba 代码循环遍历文件夹中的所有 csv 文件。它保存文件但不将文本拆分为列。
当我使用 F8 单步执行代码时,我可以看到文本被拆分为列,但是在重新打开修改后的文件时,我看不到已拆分的文本。
我正在尝试打开每个文件,将文本拆分为列,然后保存并转到下一个文件。我想对我选择的文件夹中的所有文件执行此操作。每个文件夹可能包含数千个文件。
Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
'SOURCE: www.TheSpreadsheetGuru.com
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With
'In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings
'Target File Extension (must include wildcard "*")
myExtension = "*.csv*"
'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)
'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(filename:=myPath & myFile)
'Ensure Workbook has opened before moving on to next line of code
DoEvents
'Change First Worksheet's Background Fill Blue
wb.Worksheets(1).Range("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
"|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1) _
, Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array( _
19, 1), Array(20, 1), Array(21, 1)), TrailingMinusNumbers:=True
Range("A1").Select
'Save and Close Workbook
wb.Close SaveChanges:=True
'Ensure Workbook has closed before moving on to next line of code
DoEvents
'Get next file name
myFile = Dir
Loop
'Message Box when tasks are completed
MsgBox "Task Complete!"
ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
解决方案
您可能会将文件保存回 csv。您应该以这种方式将文件保存在 xlsx 中:
wb.SaveAs Filename:=split(myfile, ".")(0), FileFormat:=xlOpenXMLWorkbook
请也应用@Luuklag 的建议。
推荐阅读
- django - 允许最终用户在 Django 中从模型中添加/删除字段
- npm - 一个 package.json 文件中的两个模块
- vba - 使用 2 个分隔字符的 VBA 双拆分数组值
- java - R2dbc 枚举类型支持(mssql、oracle、mariadb 等)
- javascript - 自定义 JSON Reporter 以显示抓取的数据 - WebdriverIO
- kubernetes - Kubernetes Volume 将 ConfigMap 挂载到 pod 中的文件,而不覆盖所有其他文件
- python - 将每日/每小时/分钟水位数据转换为每日平均值(使用 pandas - python)
- python - 有人可以向我解释这个“字典”版本的斐波那契数列是如何工作的吗?
- django - 如何以编程方式获取 {% url '...' %} 的值?
- python - 如何在决策树回归器中定义“最小样本拆分”和“最小样本叶”?