We have an Access database that needs to import data from .xls and .xslx Excel sheets. We have recently however uninstalled Excel and moved to Open office Calc instead.
Now we only have MS Access 2016 installed, without any other Office program installed. However, we need to automate Access to import the data from an Excel sheet through the VBA code. Currently, part of the code that reads the data from the Excel sheet looks like this:
Dim objXL As Object
Set objXL = CreateObject(FilePath)
Debug. Print Trim(objXL.Worksheets(1).Cells(1, 1).Value)
Set objXL = Nothing
I'm just giving you an example how is this script reading the data from the Excel sheet currently. It worked fine until we uninstalled Excel and just installed Access 2016. But now we are using use LibreOffice as an alternative for Excel the import does not work.
We are aware that simple import of the data to the Access GUI through the DoCmd.TransferSpreadsheet command will do the trick, but there would be quite a lot to rework that way.
My question is - Is there any faster way to do this, without doing a lot of rework on the script and without re-installing Excel again?