VBA to Open Xlsx/Xlsm/Xls excel files and Convert to another format

[Hollando]

Hello all,

I need help with this build .

Basically I want to be able to create a path within the code to search this folder for excel files with Xlsx/Xlsm/Xls, open, save as, same name, but new extension. In this case I am converting older files to Xlsb in order to save space.

There are three main environments I will need to do this in

1. A Users Desktop
2. A Users Documents
3. Sharedrive Folder

As always any suggestions would be greatly appreciated. Thank you in advance.

Regards,

Hollando

 

[Hollando]

Try:

wb.SaveAs wb.Name & ".xlsb", FileFormat:=50

YES! Thank you very much sir!

I have two additional ask that I may not have considered.

1. The Save As copy (in Binary format includes in the description the extention. Ex. Book2.xlsx.xlsb). Is there a way to only include the name and extention ex. Book1.xlsb
2. The Save as copy is saved whereever the open workbook from the macro is. Is there a way to have it saved in the original path?

 

[Andrew Poulsom]

Try:

wb.SaveAs Replace(wb.FullName, ".xlsx", ".xlsb"), FileFormat:=50

 

[Hollando]

Same result.

 

[Andrew Poulsom]

What result?

 

[Hollando]

What result?

File Saved with the original extension in description. Ex. being the file was a book1 and the extension is .xlsx. Now the file name is book.xlsx and the extension is .xlsb.

 

[Andrew Poulsom]

The code replaces .xlsx with .xlsb so the saved file can't be named eg Book2.xlsx.xlsb.

 

[Hollando]

The code replaces .xlsx with .xlsb so the saved file can't be named eg Book2.xlsx.xlsb.

Hey Andrew. I found a way around this issue. Thank you.

The only question I have remaining is how do I get the file that gets saved as an xlsb. saved within the same path. Right now the file is saved in the area that the Workbook containing the macro is in.

 

[Andrew Poulsom]

Well, wb.FullName is the path of the workbook you opened, so that's where it's saved. If that's the same path as the workbook containing the code it's coincidence.

 

[pavin]

Well, wb.FullName is the path of the workbook you opened, so that's where it's saved. If that's the same path as the workbook containing the code it's coincidence.

Just incase, if anyone looking for the udpated code: The following code worked well for me, with some changes highlitened.

Sub Test()
Dim wb As Workbook
For Each c In Sheets(1).Range("A2:A4")
    fPath = c.Value
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
    fName = Dir(fPath & "*.xl*")
    Do While fName <> ""
        Debug.Print fPath
        Debug.Print fName
         Set wb = [COLOR=#ff0000]Workbooks[/COLOR].Open(fPath & fName)
         wb.SaveAs [COLOR=#ff0000]Mid(wb.Name, 1, InStrRev(wb.Name, ".") - 1)[/COLOR] & ".xlsb", [COLOR=#ff0000]FileFormat:=xlExcel12, CreateBackup:=False
[/COLOR]         wb.Close False
         fName = Dir
    Loop
Next
End Sub