?
Solved

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

Posted on 2016-08-20
4
Medium Priority
?
77 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 1800 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 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 51

Expert Comment

by:Gustav Brock
ID: 41764962
You are welcome!

/gustav
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

762 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