Can an Excel worksheet be opened in Access, manipulated, and then save the data into an Access table?

Posted on 2014-12-22
Medium Priority
Last Modified: 2015-01-25
Hi Experts,
Can an Excel worksheet be opened in Access, manipulated, and then save the data into an Access table?  If so, how?  If possible please provide code samples.  Thank you very much in advance.

Question by:mainrotor
LVL 41

Assisted Solution

PatHartman earned 1000 total points
ID: 40513026
Since the goal is to import the data, there is no point in manipulating it using OLE automation.  Just import it and manipulate it in Access.

The simplest way to import a spreadsheet is to use the TransferSpreadsheet method.  Look it up in help or use intellisense to guide you.

DoCmd.TransferSpreadsheet ......
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1000 total points
ID: 40513035
'open excel from access

sub openXl()
dim xlObj as object
set xlObj=createobject("excel.application")
      xlObj.Workbooks.open "C:\folderx\myExcel.xlsx"
      with xlObj

      end with

end sub

that is how to open excel from access.

as far as your other request, give more details

to save the data to an Access table
1. you can read the info cell by cell and insert to access table using recordset
2. save the excel file after manipulation and imprt to access table with

docmd.transferspreadsheet acimport,, "tableName",  "C:\folderx\myExcel.xlsx", true

Author Comment

ID: 40526206
Rey and Pat,
I will try your suggestions. Thank you.

LVL 52

Expert Comment

by:Martin Liss
ID: 40569892
I've requested that this question be closed as follows:

Accepted answer: 250 points for PatHartman's comment #a40513026
Assisted answer: 250 points for Rey Obrero (Microsoft Access MVP)'s comment #a40513035

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40564659
@Martin Liss

use this format for hyperlink  http:#a40513035

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
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…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…

607 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