Solved

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

Posted on 2016-08-20
4
49 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
 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Direct Mail software 4 45
Tags from access to excel 3 31
Running sum query 6 33
MS Access - update query inner join will not work with long text field 11 16
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now