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