excel - 将文件夹中的 .xlsx 文件保存为 .csv 文件

Question

我尝试使用此脚本将 xlsx 文件转换为 csv。

我希望旧文件位于该文件夹中，并且 csv 文件上的名称与 xlsx 文件完全相同。

我在 csv 扩展名上获得了 . 额外信息，例如 filename..csv。

Sub ConvertCSVToXlsx()

    Dim myfile As String
    Dim oldfname As String, newfname As String
    Dim workfile
    Dim folderName As String

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

'   Capture name of current file
    myfile = ActiveWorkbook.Name

'   Set folder name to work through
    folderName = "C:\Test\"

'   Loop through all CSV filres in folder
    workfile = Dir(folderName & "*.xlsx")
    Do While workfile <> ""
'       Open CSV file
        Workbooks.Open Filename:=folderName & workfile
'       Capture name of old CSV file
        oldfname = ActiveWorkbook.FullName
'       Convert to XLSX
        newfname = folderName & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & ".CSV"
        ActiveWorkbook.SaveAs Filename:=newfname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWorkbook.Close
'       Delete old CSV file
        Kill oldfname
        Windows(myfile).Activate
        workfile = Dir()
    Loop

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

Answer 1

非常接近。您的注释在代码中有点令人困惑。

如果您要使用 left(len()-4，那么您需要更改部分以添加不带句点的 csv。新名称 = 旧名称 & "CSV"

只需对 saveas 行进行一些编辑

您不会终止原始工作簿，而是将其从文件夹中删除。

原始工作簿不再打开，因为您将其另存为新文件名。

Sub ConvertCSVToXlsx()

    Dim myfile As String
    Dim oldfname As String, newfname As String
    Dim workfile
    Dim folderName As String
    Dim wb As Workbook
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    '   Capture name of current file
    myfile = ActiveWorkbook.Name

    '   Set folder name to work through
    folderName = "C:\New folder\"

    '   Loop through all CSV filres in folder
    workfile = Dir(folderName & "*.xlsx")
    Do While workfile <> ""
        '       Open CSV file
        Workbooks.Open Filename:=folderName & workfile
        Set wb = ActiveWorkbook
        '       Capture name of old CSV file
        oldfname = Left(wb.FullName, Len(wb.FullName) - 4)
        '       Convert to XLSX
        newfname = oldfname & "CSV"
        wb.SaveAs Filename:=newfname, FileFormat:=xlCSV, CreateBackup:=False
        wb.Close
        workfile = Dir()
    Loop

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub