[SOLVED] How to save an excel file from xlsx to xlsb to make file smaller using vba and ms-access?

How to save an excel file from xlsx to xlsb to make file smaller using vba and ms-access?

I currently am ingesting excel files through a button on ms-access. The issue I am facing is that the excel files are rather large (~20MB). I noticed that if I change the file format to xlsb the files are significantly smaller (~2MB). However, I am not sure how this might affect my data validations because I need to iterate through each row of the excel files, and also multiple sheets. So not 100% if this is a viable method to make the file size smaller.

How I am currently creating excel objects though vba code linked to Access button:

public sub init(sheetName As String, filePath As Variant)
    Set objExcel = CreateObject("Excel.Application")
    Set objFile = objExcel.Workbooks.Open(filePath)
    Set objSheet = objFile.Worksheets(sheetname)
    Set sheet = objSheet

End Sub

Is there a way to revise the init method so that I dont have to open the file itself (because this is a time consuming process). If I can instead convert the file on the fly and then only use the xlsb object that is what would make this much faster.

Currently it takes 30 seconds to open a 20MB file using the above method. Whereas when I open the same file but converted to xlsb it takes a second.

When I researched this a lot of suggestions were to use: ActiveWorkbook.SaveAs FileName:= ActiveWorkbook.Path

Is this the most effective way? I need the quickest process possible, and I dont want to open the excel file because of time. I have hundreds of excel files that I need to import and validate.

Other resources I looked into:

Need to save xls workbook As xlsb with VBA

https://www.mrexcel.com/board/threads/convert-xlsx-workbook-to-xlsb-using-macro.1133145/

Batch convert .xls to .xlsx with VBA without opening the workbooks

Solution

The first link you posted has relevant code.

This can be done with Excel automation in Access VBA, consider:

Public Sub OpenWorkbook()
    Dim xlApp As Excel.Application, wbWorkbook As Excel.Workbook
    Set xlApp = CreateObject("Excel.Application")
    Set wbWorkbook = xlApp.Workbooks.Open("filepath\filename.xlsx")
    wbWorkbook.SaveAs "filepath\filename.xlsb", 50
    xlApp.Quit
End Sub

If that's too slow because of file size, there is no solution.