0

I am trying to convert multiple .xml files in my folder to .xlsx files. I have over 100 files and it would be tedious to manually save as each file as .xlsx. So I thought of using a macro. The code here only runs for a specific file, how do I make it work for all the files in my folder.

Sub macroconvt()
'
' macroconvt Macro
'

'
    ChDir _
        "S:\Research and Analysis\Interns\2016\Summer\New RCA Macro\Layout files\converted excel files"
    ActiveWorkbook.SaveAs Filename:= _
        "S:\Research and Analysis\Interns\2016\Summer\New RCA Macro\Layout files\converted excel files\FIRM_Limit Excessive Hours_19JUL16.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Thank you

Karan
  • 75
  • 2
  • 11
  • 2
    Possible duplicate of [Loop through files in a folder using VBA?](http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba). See that link to iterate the files in a folder. – Scott Holtzman Jul 21 '16 at 19:18

1 Answers1

0

Use the FileSystemObject, something like this:

Dim FSO, tFolder, tFiles, tFile As Object
Dim fp As String
Dim wb as workbook

Set FSO = CreateObject("Scripting.FileSystemObject") ' create FSO via late binding

fp = "Put your folder path here"

Set tFolder = FSO.GetFolder(fp) ' set FSO folder
Set tFiles = tFolder.Files ' get files collection

For Each tFile In tFiles

        Set wb= Application.Workbooks.Open(tFile.Path) ' open the file
        ' do what you want with the workbook e.g. wb.SaveAs etc

Next tFile
Absinthe
  • 3,258
  • 6
  • 31
  • 70