我正在尝试使用VBA将充满.txt文件的目录转换为.xls。我正在使用以下代码:
Sub TXTconvertXLS()
'Variables
Dim wb As Workbook
Dim strFile As String
Dim strDir As String
'Directories
strDir = "\\xx\xx\xx\xx\Desktop\Test\Test1\"
strFile = Dir(strDir & "*.txt")
'Loop
Do While strFile <> ""
Set wb = Workbooks.Open(strDir & strFile)
With wb
.SaveAs Replace(wb.FullName, ".txt", ".xls"), 50
.Close True
End With
Set wb = Nothing
Loop
End Sub
问题是:当我运行它时,它立即指出在目录中已经存在一个文件,该文件的名称试图保存。即使目录中肯定没有.xls,它显示的名称甚至带有.xls扩展名!任何帮助将不胜感激-谢谢!
最佳答案
您似乎在strFile = Dir
之前缺少Loop
。没有它,您将重新处理相同的TXT文件。
Do While strFile <> ""
Set wb = Workbooks.Open(strDir & strFile)
With wb
.SaveAs Replace(wb.FullName, ".txt", ".xls"), 50
.Close False '<-already saved in the line directly above
End With
Set wb = Nothing
strFile = Dir '<- stuffs the next filename into strFile
Loop
见Dir Function