• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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
0
mainrotor
Asked:
mainrotor
2 Solutions
 
PatHartmanCommented:
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
 
Rey Obrero (Capricorn1)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
 
mainrotorAuthor Commented:
Rey and Pat,
I will try your suggestions. Thank you.

mrotor
0
 
Martin LissRetired ProgrammerCommented:
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now