Solved

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

Posted on 2016-08-20
4
41 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you Gustav and Jim for your assistance on this issue / JohnB
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You are welcome!

/gustav
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

771 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

12 Experts available now in Live!

Get 1:1 Help Now