使用 VBA 打开一个制表符分隔的 .txt 文件以保存为 .xlsx 格式

Using VBA to open a tab delimited .txt file to save to .xlsx format

提问by user3264015

I'm trying to use VBA in Excel to automate turning a .txt file (tab delimited) into a .xlsx file instead. Here's what I have:

我正在尝试在 Excel 中使用 VBA 来自动将 .txt 文件（制表符分隔）转换为 .xlsx 文件。这是我所拥有的：

Set WB = Workbooks.Open(folder + file, , , 1)
If Right(file, 3) = "txt" Or Right(file, 3) = "xls" Then
    Application.DisplayAlerts = False
    WB.SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 4) + "_" + metricDate + "_.xlsx", _
        FileFormat:=51
    Application.DisplayAlerts = True
Else
    Application.DisplayAlerts = False
    WB.SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 5) + "_" + metricDate + "_.xlsx", _
        FileFormat:=51
    Application.DisplayAlerts = True
End If
WB.Close

This is just a snip of code, of course, and the first part of the code is the most relavant, I think. I've only started checking the transformed .txt files, as they are 10% he size they should be after saving. Turns out, the twenty columns are smushed into three, and all spaces and tabs have been removed. Not sure what is going on, as I don't use VBA very often.

当然，这只是一小段代码，我认为代码的第一部分是最相关的。我只开始检查转换后的 .txt 文件，因为它们是保存后应该大小的 10%。结果是，二十列变成了三列，并且所有的空格和制表符都被删除了。不确定发生了什么，因为我不经常使用 VBA。

I'm thinking the key is here:

我认为关键在这里：

Set WB = Workbooks.Open(folder + file, , , 1)

The 1 at the end signifies tab delimited. Not sure what it will do for the .xls files it also opens, but I'll worry about that next.

末尾的 1 表示制表符分隔。不确定它会为它打开的 .xls 文件做什么，但接下来我会担心。

Thanks for any pointers you can give.

感谢您提供的任何指示。

---

---

Edit.

编辑。

I changed the code around to treat .txt and .xls differently, as I should have done in the first place. Here is the current code:

我更改了代码以区别对待 .txt 和 .xls，正如我一开始就应该做的那样。这是当前的代码：

Dim WB As Workbook
'Dim WBS As Workbooks

If Right(file, 3) = "txt" Then
    Set WB = Workbooks.OpenText Filename:=folder + file, DataType:=xlDelimited, Tab:=True
    Application.DisplayAlerts = False
    WB(1).SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 4) + "_" + metricDate + "_.xlsx", _
        FileFormat:=51
    Application.DisplayAlerts = True
    WB.Close
ElseIf Right(file, 3) = "xls" Then
    Set WB = Workbooks.Open(folder + file)
    Application.DisplayAlerts = False
    WB.SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 4) + "_" + metricDate + "_.xlsx", _
        FileFormat:=51
    Application.DisplayAlerts = True
    WB.Close
Else
    Set WB = Workbooks.Open(folder + file)
    Application.DisplayAlerts = False
    WB.SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 5) + "_" + metricDate + "_.xlsx", _
        FileFormat:=51
    Application.DisplayAlerts = True
    WB.Close
End If

采纳答案by MattB

Lets try this again with your own code down here where I have more room. Try the following and read my comments above. I think you'll find it works:

让我们在这里用你自己的代码再试一次，我有更多的空间。尝试以下操作并阅读我上面的评论。我想你会发现它有效：

'I'm adding this line.  I'm assuming you have it in your code, but just to be certain...
Dim WB As Excel.Workbook
'This line opens your tab delimeted text file.
Set WB = Workbooks.OpenText(Filename:=folder + file, DataType:=xlDelimited, Tab:=True
If Right(file, 3) = "txt" Or Right(file, 3) = "xls" Then
    'This section turns off alerts, saves the workbook opened in the previous step as xlsx and turns alerts back on.
    Application.DisplayAlerts = False
    WB.SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 4) + "_" + metricDate + "_.xlsx", FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
Else
    'Again, this section saves the workbook opened in the previous step as xlsx.
    Application.DisplayAlerts = False
    WB.SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 5) + "_" + metricDate + "_.xlsx", FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
End If
WB.Close

I'm also debating whether or not you really need that If statement. It looks like you're doing the exact same thing and naming the workbook using the same convention. You might not need it. I left it because you didn't ask about it specifically. You could skip it and just save the workbook I think.

我也在争论你是否真的需要那个 If 语句。看起来您正在做完全相同的事情并使用相同的约定命名工作簿。你可能不需要它。我离开它是因为你没有特别询问它。您可以跳过它并保存我认为的工作簿。

Edit: You need the If statement to pick the method you use to open your workbook...

编辑：您需要使用 If 语句来选择用于打开工作簿的方法...

'I'm adding this line.  I'm assuming you have it in your code, but just to be certain...
Dim WB As Excel.Workbook
If Right(file, 3) = "txt" then
    'This line opens your tab delimeted text file.
    Set WB = Workbooks.OpenText(Filename:=folder + file, DataType:=xlDelimited, Tab:=True
Else
    'This line opens your xls and xlsx books
    Set WB = Workbooks.Open(folder + file) 'no additional parameters should be needed
End If
Application.DisplayAlerts = False
WB.SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 4) + "_" + metricDate + "_.xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
WB.Close

If you're iterating over a number of these input workbooks you may want to do

如果您要迭代许多这些输入工作簿，您可能想要做

Set WB = Nothing

just to be safe.

只是为了安全。

Edit: I'll let my shame hang out up there... the OpenTextmethod does not return an object, so you'll have to set the WBobject using Set WB = Workbooks(file)after you've opened it assuming fileis the full filename including extension. My bad on that one.

编辑：我会让我的耻辱挂在那里......该OpenText方法不返回一个对象，所以你必须在打开它后设置WB对象 usingSet WB = Workbooks(file)假设file是完整的文件名，包括扩展名。我对那个不好。