批量转换TXT为XLS使用VBA [英] Batch Convert TXT to XLS Using VBA

问题描述

我想转换一个目录充满.txt文件使用VBA为.xls。我使用下面的code：

I am trying to convert a directory full of .txt files to .xls using VBA. I am using the following code:

    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在目录中的呢！任何帮助将大大AP preciated - ！谢谢

The issue is: when I run it, it immediately states that there is already a file with the name it's trying to save with in the directory. The name it shows even has a .xls extension, even if there are assuredly no .xls's in the directory yet! Any help would be greatly appreciated - thanks!

推荐答案

您似乎缺少 strFile = DIR 在循环播放。没有它，你都重新处理同一个TXT文件。

You seem to be missing strFile = Dir before Loop. Without it you are reprocessing the same TXT file.

    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函数

这篇关于批量转换TXT为XLS使用VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！