Solved

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

Posted on 2016-08-20
4
60 Views
Last Modified: 2016-08-22
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
Comment
Question by:boltweb
  • 2
4 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 450 total points
ID: 41763537
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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 50 total points
ID: 41763587
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
 
LVL 1

Author Closing Comment

by:boltweb
ID: 41764893
Thank you Gustav and Jim for your assistance on this issue / JohnB
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41764962
You are welcome!

/gustav
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SetProperty Foreground Colour 5 15
Populating datasheet subform from VBA 6 15
Treeview control in 64 bit Office. 2 23
query sort by digit 5 10
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question