使用VBA打开以制表符分隔的.txt文件以保存为.xlsx格式 [英] Using VBA to open a tab delimited .txt file to save to .xlsx format

问题描述

我正在尝试在Excel中使用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:

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

当然，这只是一小段代码，我认为代码的第一部分是最相关的.我只是开始检查转换后的.txt文件，因为它们是保存后大小的10％.事实证明，将二十列压缩为三列，并删除了所有空格和制表符.不知道发生了什么，因为我不经常使用VBA.

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.

我在想钥匙在这里:

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

末尾的1表示制表符定界.不知道它将对同时打开的.xls文件做什么，但是接下来我会担心.

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.

感谢您可以提供的任何指点.

Thanks for any pointers you can give.

编辑.

我改变了代码，以区别对待.txt和.xls，就像我应该首先要做的那样.这是当前代码:

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:

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

推荐答案

让我们在您有更多空间的地方再次尝试使用您自己的代码.请尝试以下操作并阅读我的意见.我认为您会发现它有效:

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

我也在辩论您是否真的需要该If语句.看起来您正在做完全相同的事情，并使用相同的约定来命名工作簿.您可能不需要它.我离开它是因为您没有专门询问它.您可以跳过它，只保存我认为的工作簿.

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语句来选择用于打开工作簿的方法...

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

'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

为了安全起见.

我会让我的羞耻挂在那儿... OpenText 方法不会返回对象，因此您必须设置 WB 在打开对象后，使用 Set WB = Workbooks(file)设置对象，并假设 file 是包含扩展名的完整文件名.我对那个不好.

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.

这篇关于使用VBA打开以制表符分隔的.txt文件以保存为.xlsx格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！