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

10-06 07:11