Solved

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

Posted on 2014-12-22
6
274 Views
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.

mrotor
0
Comment
Question by:mainrotor
6 Comments
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
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 ......
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
'open excel from access

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


      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
or
2. save the excel file after manipulation and imprt to access table with

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

Author Comment

by:mainrotor
Comment Utility
Rey and Pat,
I will try your suggestions. Thank you.

mrotor
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
@Martin Liss

use this format for hyperlink  http:#a40513035
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

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

8 Experts available now in Live!

Get 1:1 Help Now