473,372 Members | 1,312 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Access Tables to Excel Worksheet Export

Hello, I've recently been asked to work with Microsoft Access, and
while I feel quite comfortable with Excel, I'm at a complete loss with
databases. If someone could help me with this issue I'm having I'd be
most appreciative. The database is already constructed, I'm just
wanting to export the data to an excel file.

In short, I'm hoping to export two Tables (or queries...not sure which
to use - they both seem to have the same data) in a Microsoft Access
database to a single blank worksheet (already created, lets call it:
'Data') in Microsoft Excel one right after another (order is
irrelevant). Let's call one of the Access tables: TCC, and the other
TCP.

The TCC and TCP tables have 5 and 6 columns respectively (although I
only wish the first 4 columns to be sent to the excel file), and have
similar titles (not exactly the same in all cases) but the contents of
each column is consistent with the ones on the other ( i.e. Column 4
on both are formatted as dates, column 3 contains numbers, column 2
contains text, and column 1 contains a name, but when I try to export
it to excel (not sure if I'm doing it right - I'm really clueless when
it comes to Access), I get an ID number. I would like if possible to
retain the name, as shown when viewing in the query, but this isn't a
life-or-death issue.

Now here is where it gets tricky:
I only wish for the entries whose dates (in the 4th column) are
between or include two dates on a different worksheet (called 'Entry
Form', located in the same excel file to be listed/pasted on the
'Data' worksheet. More specifically, cell C9 contains the beginning
date and C10 is the ending date. If/When one of these dates are
changed, I need the list of data exported from the two tables to be
updated. Also, I can't have any changes made to the excel file to
modify the data in the Access file. I'm not sure what other info you
may need, the dates in the 4th column for both TCC and TCP have a
field name of "Week Ending"

Thank you so much,
Stefan

Mar 16 '07 #1
1 9743
On Mar 15, 7:15 pm, smacz...@gmail.com wrote:
Hello, I've recently been asked to work with Microsoft Access, and
while I feel quite comfortable with Excel, I'm at a complete loss with
databases. If someone could help me with this issue I'm having I'd be
most appreciative. The database is already constructed, I'm just
wanting to export the data to an excel file.

In short, I'm hoping to export two Tables (or queries...not sure which
to use - they both seem to have the same data) in a Microsoft Access
database to a single blank worksheet (already created, lets call it:
'Data') in Microsoft Excel one right after another (order is
irrelevant). Let's call one of the Access tables: TCC, and the other
TCP.

The TCC and TCP tables have 5 and 6 columns respectively (although I
only wish the first 4 columns to be sent to the excel file), and have
similar titles (not exactly the same in all cases) but the contents of
each column is consistent with the ones on the other ( i.e. Column 4
on both are formatted as dates, column 3 contains numbers, column 2
contains text, and column 1 contains a name, but when I try to export
it to excel (not sure if I'm doing it right - I'm really clueless when
it comes to Access), I get an ID number. I would like if possible to
retain the name, as shown when viewing in the query, but this isn't a
life-or-death issue.

Now here is where it gets tricky:
I only wish for the entries whose dates (in the 4th column) are
between or include two dates on a different worksheet (called 'Entry
Form', located in the same excel file to be listed/pasted on the
'Data' worksheet. More specifically, cell C9 contains the beginning
date and C10 is the ending date. If/When one of these dates are
changed, I need the list of data exported from the two tables to be
updated. Also, I can't have any changes made to the excel file to
modify the data in the Access file. I'm not sure what other info you
may need, the dates in the 4th column for both TCC and TCP have a
field name of "Week Ending"

Thank you so much,
Stefan

1) you can use DoCmd.TransferSpreadsheet (http://www.mvps.org/access/
general/gen0008.htm) to export data from your Access database to an
Excel spreadsheet; you can create a query that will return only the
first four columns of a table, and use this as the 'source' of this
export.
2) in order to have both tables output to the same worksheet, you will
need to create a UNION query (http://www.devguru.com/technologies/
jetsql/QuickRef/union.html) that will combine the data from each table
(or, more specifically, the subset of the data that your queries will
return): Example query: SELECT field1, field2, field3, field4 FROM
TCC UNION SELECT field1, field2, field3, field4 FROM TCP;.
A more visual example of a union query can be found at
http://en.wikipedia.org/wiki/Union_(SQL)
3) in order to know the date constraints that need to be applied,
you'll need to use Automation to access the Excel spreadsheet
containing those constraints (http://www.mvps.org/access/modules/
mdl0006.htm); you'll need to open that spreadsheet, read the values,
and apply them to your query using either a "parameter query" or by
using VBA to construct a SQL query manually and then execute it using
DAO or ADO.
4) as for executing this process "when one of those dates are
changed", that's not exactly possible; you'll need your database to
periodically check that spreadsheet to see if it has changed, unless
of course you want to add code to your Excel spreadsheet that's
executed when those cells are changed, and calls a macro in your
database initiating the export process.

Would it be simpler to just use DoCmd.transferspreadsheet to dump the
data, and manually manipulate it? How automated does this process
need to be?
Mar 16 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: sridevi | last post by:
Hello How to export data from ms-access database to excel worksheet using ASP. mainly i need to export data to multiple worksheets. it is very urgent to us. i have a sample code which works...
3
by: Gaffar | last post by:
Hello, how to export ms-access tables information to excel sheet using ASP. please give sample code. it is very urgent to me or if u have no idea please give any news group to solve my...
4
by: Gary Wright | last post by:
I have an Access 2K database split into front and back. Quite often the users want to do some data analysis that I have not created a report for so they want to export some subset of the data into...
4
by: Anthony Cuttitta Jr. | last post by:
I'm working on some procedures where Access queries are exported to Excel, and then later on, those same workbooks are openned, and I need to target a specific original sheet. Sometimes there will...
3
by: Regnab | last post by:
Despite having at least 2 days VBA programming experience, this one has got me... I'm trying to export multiple tables into one worksheet in Excel. I've adapted code that I've got from this forum,...
4
by: Jae | last post by:
I'm writing a web application that exports and imports excel files. The application gets a list of users and their info and displays it in a datagrid .The user then selects to save the file as a...
6
by: Elena | last post by:
I'm trying to export data to an Excel worksheet. I can export the data in the cell values perfectly. I need the code to change a header and footer for the worksheet, not for the columns. Is...
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
3
by: jmarcrum | last post by:
I want to export a report (that contains two separate queries, 1. Current year data, and 2. split-year data) from access into excel, but everytime I run my code and export the data to excel, it looks...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.