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

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

1 个解决方案

#1

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 OpenText method does not return an object, so you'll have to set the WB object using Set WB = Workbooks(file) after you've opened it assuming file is the full filename including extension. My bad on that one.

编辑:我会让我的耻辱挂在那里...... OpenText方法不会返回一个对象,所以你必须在打开它之后使用设置WB =工作簿(文件)设置WB对象假设文件是包含扩展名的完整文件名。那个我不好。

#1