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

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
mainrotorAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
'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
 
PatHartmanConnect With a Mentor Commented:
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
 
mainrotorAuthor Commented:
Rey and Pat,
I will try your suggestions. Thank you.

mrotor
0
 
Martin LissOlder than dirtCommented:
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
 
Rey Obrero (Capricorn1)Commented:
@Martin Liss

use this format for hyperlink  http:#a40513035
0
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.

All Courses

From novice to tech pro — start learning today.