问题

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.

如今，我通过MS Access上的一个按钮导入Excel文件。我面临的问题是Excel文件相当大（约20MB）。我注意到，如果我将文件格式更改为xlsb，文件会显着变小（约2MB）。但是，我不确定这是否会影响我的数据验证，因为我需要遍历每行Excel文件以及多个工作表。所以我不确定这是否是减小文件大小的可行方法。

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

如何通过与Access按钮关联的VBA代码当前创建Excel对象：

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.

有没有一种方法可以修改init方法，以便我不必打开文件本身（因为这是一个耗时的过程）。如果我可以在运行时转换文件，然后只使用xlsb对象，那将使这个过程更快。

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.

目前，使用上述方法打开一个20MB的文件需要30秒。而当我打开相同的文件，但转换为xlsb格式，只需要1秒。

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

当我进行研究时，有很多建议使用：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.

这是最有效的方法吗？我需要尽可能快的过程，而且我不想打开Excel文件因为时间问题。我有数百个需要导入和验证的Excel文件。

Other resources I looked into:

我查看了其他资源：

https://stackoverflow.com/questions/31654526/need-to-save-xls-workbook-as-xlsb-with-vba

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

https://stackoverflow.com/questions/29167539/batch-convert-xls-to-xlsx-with-vba-without-opening-the-workbooks

https://stackoverflow.com/questions/31654526/need-to-save-xls-workbook-as-xlsb-with-vba

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

https://stackoverflow.com/questions/29167539/batch-convert-xls-to-xlsx-with-vba-without-opening-the-workbooks

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

答案1

得分: 0

第一个链接您发布了相关的代码。

这可以通过在Access VBA中进行Excel自动化来实现，考虑：

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

如果由于文件大小而导致速度过慢，那就没有解决办法。

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