循环浏览文件夹并将所有 txt 文件另存为 Excel 文件(特定格式)

Question

我想将.txt文件从文件夹导入Excel并将它们另存为Excel文件。

代码不完整，但通过文件夹部分的循环在它们两个中都不起作用。

它曾经工作过，但它一次打开了太多文件夹并冻结了。我不得不在没有保存的情况下退出程序，从那时起它就不起作用了。

这是第一个代码：

Sub getDataFromWbs()
Dim wb As Workbook, ws As Worksheet
Set fso = CreateObject(“Scripting.FileSystemObject”)
Set fldr = fso.GetFolder("E:MA5_SensordatenTestTEST2")
For Each wbFile In fldr.Files
If fso.GetExtensionName(wbFile.Name) = “.txt” Then
Set wb = Workbooks.Open(wbFile.Path)
wb.Close
End If
Next wbFile
End Sub

我收到以下错误Set fso = CreateObject(“Scripting.FileSystemObject”)

运行时错误 424
需要对象

我还尝试了dir方法：

Sub LoopImport2()
'Loop through all files in a folder
Dim FN As Variant
Application.ScreenUpdating = False
FN = Dir("E:MA5_SensordatenTestTEST2*.txt")
While FN <> ""
'Insert the actions to be performed on each file
Workbooks.OpenText Filename = FN, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1)), DecimalSeparator:=",", TrailingMinusNumbers:=True
ActiveSheet.Name = FN
ActiveSheet.Cells.NumberFormat = "0.00"
'Set the FN to the next file
FN = Dir
Wend
End Sub

并收到此错误：

运行时错误 1004 应用程序或对象定义错误

有了这部分：

Workbooks.OpenText Filename = FN, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1)), DecimalSeparator:=",", TrailingMinusNumbers:=True

Answer 1

在您的第一个代码中，请尝试：

Dim fso As Object和“Scripting.FileSystemObject”看起来都有奇怪的双引号字符。尝试使用"Scripting.FileSystemObject".

在第二个中，请用FileName:= FN更改Filename = FN。