Running macro in Access to extract data from Excel

[Ayo]

Is there a way to create a macro that will perform these actions.
1.) Open an excel file
2.) Extract data from cells in the excel file and put them in text boxes on
a form in Access
3.) Save the data on the form to a table
4.) Close the excel file

 

[John W. Vinson]

Is there a way to create a macro that will perform these actions.
1.) Open an excel file
2.) Extract data from cells in the excel file and put them in text boxes on
a form in Access
3.) Save the data on the form to a table
4.) Close the excel file

I'd do it in VBA rather than in a Macro (if you're used to Excel Macros they
*are* VBA; Access macros are much more limited). Adding the Form would be a
major complication - it would be much easier to transfer the data directly
into a Table, and have a form bound to that table to display and edit the
data. See the VBA help for "TransferSpreadsheet".

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

Is there a way to create a macro that will perform these actions.
1.) Open an excel file
2.) Extract data from cells in the excel file and put them in text boxes on
a form in Access

Data is not really in a form, the form is a way of looking at your data and
it is often best to base your form on a query.

3.) Save the data on the form to a table

So what you really want to do is build a table with new data coming from
your Excel worksheet.

Link to your Excel worksheet from Access, this allows you to use the sheet
as a table in access although you cannot modify the data. You can however
build a query using the data in the worksheet.

Build a query with the data you need and then change its type to a make
table query and actually create your new data table in Access. Now you can
use the worksheet to make an append query specifying the criteria you want to
append new data from your Excel sheet to your new table. You can create a
macro in access to run this append query and you could even schedule this
macro to fire at specified times.

When you link to an external data source like the Excel worksheet, it
creates a live link to all the new data as it is added to the sheet. You
could fire the access append macro every night appending the days new data to
your new Access table.

Build a query based on this table as a control source for whatever form you
want. you do not need to open or close Excel, the link remains live all the
time, you cannot however open the excel sheet if the access databas eis open
at the same time.