VBA代码可更改文件夹中多个文件的文件格式 [英] VBA code to change file format of multiple files in a folder

问题描述

以下代码将保存为 .xml 的多个文件的文件格式更改为 .xlsx 文件.它只是打开特定文件夹中的文件，然后另存为" .xlsx .但是我不知道如何使它在目标文件夹中的所有文件上运行.到目前为止，它仅指向文件夹中的第一个文件.

The following code below changes the file format of multiple files saved as .xml to .xlsx files. It simply opens the files in a specific folder and "Saves as" .xlsx. However I don't know how to make it run on all the files in my target folder. As of now it is only pointing to the 1st file in the folder.

Sub m_convertformat()
'
' m_convertformat Macro

Dim wb As Workbook
Dim sht As Worksheet
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
    .Title = "Select A Target Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
    myPath = .SelectedItems(1) & "\"
End With

'In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
myExtension = "*.xls"

'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
Do While myFile <> ""
    'Set variable equal to opened workbook
    Set wb = Workbooks.Open(Filename:=myPath & myFile)

        'Change the format
         ActiveWorkbook.SaveAs Filename:= _
        "S:\Xyz\abc.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

        End With

    'Save and Close Workbook
    wb.Close SaveChanges:=True

    'Get next file name
    myFile = Dir
Loop

'Message Box when tasks are completed
MsgBox "Task Complete!"

End Sub

推荐答案

代码中需要调整一些内容，以使其与您编写的文字完全相同.请参见下面的重构代码.

There a few things in the code that need adjusting in order for it to work exactly as the text you wrote described. See the refactored code below.

'Target File Extension (must include wildcard "*")
myExtension = "*.xml" `- since you want to open xml files to save as xlsx

然后更改

 'Change the format
         ActiveWorkbook.SaveAs Filename:= _
        "S:\Xyz\abc.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

收件人

   'Change the format
    wb.SaveAs Filename:= wb.Path & "\" Replace(wb.Name,".xml",".xlsx"), _
            FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

然后删除此代码:结尾为

这篇关于VBA代码可更改文件夹中多个文件的文件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！