Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access table to Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access table to Excel
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 "Tushar" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access table to Excel
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/Ex...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 -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Tushar" wrote in message om... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access table to Excel
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access table to Excel
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. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Tushar Garg" wrote in message ... 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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access table to Excel
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access table to Excel
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. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Tushar Garg" wrote in message ... 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 . . . Excel Table A96 = 12/24/2002 A97 = 1 A98 = YEAR(A96) + A97 The logic needs to find this FYQ in Access and return all rows from this FYQ and everything below it. In other words, 20021, 20022, ...all need to be returned till the very last row. Hopefully this helps man. I am stumped on this so I could use some big time help. THANKS AGAIN FOR THIS *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access Table into Excel using VB | Excel Discussion (Misc queries) | |||
Open an Access table in Excel | Excel Discussion (Misc queries) | |||
How to retrieve the value within the table from Access into Excel? | Excel Discussion (Misc queries) | |||
Excel Pivot Table with Access | Excel Discussion (Misc queries) | |||
Linking a table in Access to a table in Excel using MS Query | Excel Programming |