• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 87
  • Last Modified:

MS Access import from Excel files without having Excel installed on PC

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?
0
boltweb
Asked:
boltweb
  • 2
2 Solutions
 
Gustav BrockCIOCommented:
I guess you already know the answer: No.

If you wish to use Excel it must, of course, be installed.
If you wish to use anything else, rewrite your code fit this.

But have you really not tried just to link the file? Use the linked table as source in a straight select query where you filter and convert the data and rename fields from F1, F2, etc. to what you may need. Then use this query as source for your further processing.

/gustav
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
I'm just going to expand a bit on what gustav said, so please don't select this comment when closing the question.  

Without Excel installed, you simply cannot do this:

Dim objXL As Object
Set objXL = CreateObject(FilePath)

 What your limited to then is features built into Access itself with its ISAM driver for Excel.  That means  TransferSpreadsheet and linking as a table.

 Those are your only choices.

 The way you have it now (calling Excel as an automation object) gives you the most overall control.   TransferSpreadsheet and linking will limit the things you can do (all you can do is move data).   So depending on what it is your doing in the code, you may find that you cannot do the things you want to do with those methods.

 Your only choice may be to re-install Excel.

Jim.
0
 
boltwebAuthor Commented:
Thank you Gustav and Jim for your assistance on this issue / JohnB
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now