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
279 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
[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
6 Comments
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 250 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 ......
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 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
              .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
ID: 40526206
Rey and Pat,
I will try your suggestions. Thank you.

mrotor
0
 
LVL 47

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.
0
 
LVL 120

Expert Comment

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

use this format for hyperlink  http:#a40513035
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

742 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