时间戳

首页 > 解决方案 > 将文件夹中的 .xlsx 文件保存为 .csv 文件

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

问题描述

我尝试使用此脚本将 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

标签： excelvba

解决方案

很接近了。您的评论在代码中有点混乱。

如果要使用 left(len()-4 则需要更改部分以添加不带句点的 csv。newfname = oldfname & "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

推荐阅读