首页 文章

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

提问于
浏览 次
0

我正在尝试在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

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

我在想钥匙在这里：

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

最后的1表示制表符分隔 . 不知道它会对它打开的.xls文件做什么，但我会担心下一步 .

感谢您提供的任何指示 .

编辑 .

我改变了代码来区别对待.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
excel vba excel-vba xlsx

1 回答

  • 0

    让我们再次尝试使用您自己的代码在这里我有更多的空间 . 尝试以下内容并阅读上面的评论 . 我想你会发现它有效：

    '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声明 . 看起来你正在做同样的事情，并使用相同的约定命名工作簿 . 你可能不需要它 . 我离开了，因为你没有特别询问它 . 你可以跳过它，只是保存我认为的工作簿 .

    编辑：您需要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

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

    Set WB = Nothing

    为了安全起见 .

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

    回复于

相关问题