Save files in a different format from a folder

**sahilkh18** · 04-06-2016, 07:54 PM

Hello

I have a folder which has multiple Excel files in it (extensions can differ).

I want to open this folder, loop through each file, and save it as a binary file in the same folder. Also, I would like to keep the original name of the file for the new binary file.

I have the following code, which I have written. However, I am not able to capture the same file name and progress further.

Can someone help me please?

Sub cons()

Dim myfile As String, myfolder As String, z As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'create a new workbook and rename it to store the name of the workbooks in the folder
Workbooks.Add.SaveAs "C:\Users\skhur1\Desktop\Named workbook", FileFormat:=50

'storing folder path
myfolder = "C:\Users\skhur1\Desktop\New folder\"

'storing files path
myfile = Dir(myfolder)

'loop to open the folder and loop through the files saved in it.

Do While Len(myfile) > 0

'opening each file one by one
Workbooks.Open (myfolder & myfile)

'Saving the name of each file in Cell A1. This step can be skipped, if there is another way..
Workbooks("Named workbook").Sheets(1).Range("a1") = ActiveWorkbook.Name

'Trying to store the name of each file in a variable z

z = Workbooks("Named workbook").Sheets(1).Range("a1").Value

'activating the workbook with the name stored above
Windows(z).Activate

ActiveWorkbook.SaveAs (myfolder & z), FileFormat:=50

ActiveWorkbook.Close

myfile = Dir()

Loop

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

On the second loop, it gives me an error. I'm attaching the screenshot as well.

**AlphaFrog** · 04-06-2016, 09:06 PM

Sub cons()
    
    Dim myfile As String, myfolder As String, counter As Long
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    'create a new workbook and rename it to store the name of the workbooks in the folder
    'Workbooks.Add.SaveAs "C:\Users\skhur1\Desktop\Named workbook", FileFormat:=50
    
    'storing folder path
    myfolder = "C:\Users\skhur1\Desktop\New folder\"
    
    'storing files path
    myfile = Dir(myfolder & "*.xls*")
    
    'loop to open the folder and loop through the files saved in it.
    
    Do While Len(myfile) > 0
    
        If LCase(Right(myfile, 4)) <> "xlsb" Then
    
            'opening each file one by one
            With Workbooks.Open(myfolder & myfile)
                
                .SaveAs myfolder & Left(.Name, InStrRev(.Name, ".") - 1), FileFormat:=50
                
                .Close
                
                counter = counter + 1
                
            End With
            
        End If
    
        myfile = Dir
    
    Loop
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    MsgBox counter & " files resaved. "
    
End Sub

Save files in a different format from a folder

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Save files in a different format from a folder

Re: Save files in a different format from a folder

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Looping through all files in a folder - save as xlsx

[SOLVED] macro to open all files in a folder, save them, then close them

Macro to create a folder and then save all files in that folder

Run a folder with xls fles and save as txt files

VBA to create folder and then save multiple files In

[SOLVED] Cannot save excel files to a folder on a network

Save copy of folder with linked files

Bookmarks

Bookmarks

Posting Permissions