Access table to Excel

[Tushar]

Hello,

I want to download an Access table to an Excel worksheet. I have had
some advice on this earlier. My task has taken a new turn. I need to
screen the information in the Access table based on excel cell
information. Example,

Excel Cell 'A96' = 12/31/2002
Excel Cell 'A97' = 1

The Access Database, FY2004OLM, the date is in FYQ format, ex - 20021.
The table is called 'Download Table'. I need for a macro that will
take the year from the date in cell A96 and the quarter number from
cell A97 to form the FYQ format, 20021 and then use a query to search
for that in the Access DB. Once it has found teh FYQ, I need for the
macro to populate excel with all the rows found after and on the found
date. Any and all help if GREATLY APPRECIATED. Thanks in advance

Tushar

 

[Guest]

Not sure how to do this from Excel, but you can attach Excel worksheets as
tables in Access. Can you do that and achieve the desired affect? Run a query
built in Access and download from Access to Excel? Access has built in
wizards and tools specifcally for this type of thing and working from Access
is easier. If you work from the Excel side, your solution will have to be
built entirely by you in code.

Just a thought.

Hope that helps!

Kevin

 

[Dick Kusleika]

Tushar

It sounds like you want an External Data Table in Excel

http://www.dicks-clicks.com/excel/ExternalData.htm

And specifically, you want one with a parameter

http://www.dicks-clicks.com/excel/ExternalData6.htm#Parameters

You'll need another cell to build the parameter string, say A98, with a
formula like

=YEAR(A96)&A97

to give you 20021

 

[Tushar Garg]

Thanks guys for your help. That was really helpful. But there's three
parts to the problem. The first one you guys helped me answer. What
needs to happen is the query needs to lookup the date that is in excel
in the access table, then download all rows from that date till the end
of the records. Thirdly, I need to create a date constrained such that,
if the user typed in anything between, example, 10/1/2002 and
12/31/2002, the date should read 20031. Any input on this will be
great, thanks for all your help.

 

[Dick Kusleika]

Tushar

Is the FYQ field and the date field the same thing? I thought you wanted to
return all the records from the table that matched the FYQ in the Excel
cell. If you want all the records after that date, then your parameter
would be "greater than" instead of "equals"

In your first post, you said you wanted the year from A96 and the quarter
number from A97. Below, you seem to want the next quarter based on the
dates entered. I'm a little confused on this point. Maybe you can clarify.
If you can provide a small sample of your data and what you want returned,
that would be helpful too.

 

[Tushar Garg]

Access Table C=Column
FYQ C1 C2 C3 C4 C5
20021 1 3 4 5 7
20022 3 3 7 7 8
20023 5 7 9 0 0
20024
20031

 

[Dick Kusleika]

Tushar

Here's the step by step. I may be covering stuff you already know.

Data > Get External Data > New Database Query (maybe slightly different in
different versions)
Choose Data Source: Access Database*
File Open: Choose FY2004OLM.mdb
Choose Columns: All the columns from Download Table
Filter Data: FYQ (first column in table) is greater than or equal to 20021
(this is just a place holder, we'll make it a parameter based on a cell
later)
Sort by: Nothing
Finish: Click View data or edit query in MS Query, then click Finish

MS Query will open and we'll change the criteria to a prompt parameter. You
should have FYQ on the first line of the criteria pane and >=20021 right
below it. Change the Value line from >=20021 to

=[Enter FYQ]

File > Return Data to Microsoft Excel
Enter 20021 when prompted and click OK
Select a range where the data will start. I selected C96.

On the External Data Toolbar, there's a button called Parameters. Click it.
In the Parameters dialog, you should have a parameter named Enter FYQ (and
it should be the only one). With that parameter selected, change the option
button to "Get the value from the following cell" and click in the RefEdit
control directly below it and select A98. Mine put =Sheet1!$A$98 in the
RefEdit control. (We just changed a prompt parameter to a cell parameter.)
Click on the "Refresh automatically..." check box and click OK.

Now you have an external data table with one criterion that is a cell
parameter. If you change A96 or A97 (thereby changing A98), the query table
will refresh to show the new data. Change A96 to 1/3/03, A98 should change
to 20031 and your query table should change to show 1 row (based on the
sample you provided.)

Hopefully that gets you going in the right direction. Post back if you hit
a snag.