Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data too large for Excel, need to query Access data for results
I've got a large database within Excel, and will soon exceed 70,000
rows. Excel's limit, as we know is 65,536 Its used to support VLOOKUP and IF functions, and return prices and purchasing data for items on another worksheet, which usually contain 1000 items or less. Short of paring down my database, it there a way to keep the data in MS Access, and query the lookup answers from within Excel, and have Excel just display the correct prices and other data pertinant to that line? Sue |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data too large for Excel, need to query Access data for results
Pulling data from Access into Excel isn't difficult. Here's a starting point
http://support.microsoft.com/kb/295646 It explains how to pull Access data into Excel using ADO. You don't need to do lots of coding if your queries are fairly standardized, and you may find it is much faster than having 60,000+ rows in Excel "Susan" wrote: I've got a large database within Excel, and will soon exceed 70,000 rows. Excel's limit, as we know is 65,536 Its used to support VLOOKUP and IF functions, and return prices and purchasing data for items on another worksheet, which usually contain 1000 items or less. Short of paring down my database, it there a way to keep the data in MS Access, and query the lookup answers from within Excel, and have Excel just display the correct prices and other data pertinant to that line? Sue |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data too large for Excel, need to query Access data for results
Sue,
To add to Duke's info: You can get the data into Access quite easily with its Import feature. Or get Office 12, which apparently extends the rows.count to something like 1 million. NickHK "Susan" wrote in message oups.com... I've got a large database within Excel, and will soon exceed 70,000 rows. Excel's limit, as we know is 65,536 Its used to support VLOOKUP and IF functions, and return prices and purchasing data for items on another worksheet, which usually contain 1000 items or less. Short of paring down my database, it there a way to keep the data in MS Access, and query the lookup answers from within Excel, and have Excel just display the correct prices and other data pertinant to that line? Sue |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data too large for Excel, need to query Access data for results
NickHK wrote: Sue, To add to Duke's info: You can get the data into Access quite easily with its Import feature. Or get Office 12, which apparently extends the rows.count to something like 1 million. NickHK Nick, I didn't see a reply from Duke, but I do thank you for the heads up on Office 12. Susan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data too large for Excel, need to query Access data for results
Susan,
<Duke's comment Pulling data from Access into Excel isn't difficult. Here's a starting point http://support.microsoft.com/kb/295646 It explains how to pull Access data into Excel using ADO. You don't need to do lots of coding if your queries are fairly standardized, and you may find it is much faster than having 60,000+ rows in Excel </Duke's comment NickHK "Susan" wrote in message oups.com... NickHK wrote: Sue, To add to Duke's info: You can get the data into Access quite easily with its Import feature. Or get Office 12, which apparently extends the rows.count to something like 1 million. NickHK Nick, I didn't see a reply from Duke, but I do thank you for the heads up on Office 12. Susan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data too large for Excel, need to query Access data for results
Susan,
But bear in mind that your code would then not be compatible with previous versions of Excel, ones with rows.count<1 million. Nick "Susan" wrote in message oups.com... NickHK wrote: Sue, To add to Duke's info: You can get the data into Access quite easily with its Import feature. Or get Office 12, which apparently extends the rows.count to something like 1 million. NickHK Nick, I didn't see a reply from Duke, but I do thank you for the heads up on Office 12. Susan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data too large for Excel, need to query Access data for results
Hi Nick - I read the article in the microsoft knowledge base you submitted and I was wondering if you could help me with a similar question. I'm trying to do the same thing as Sue but the otherway round. I have a data list in Excel which is added to every now and then. I'd like to automate the transfer of this list into an access table from where I run some reports. I don't mind if this is an append everytime a few lines is added or a complete transfer of all the lines once every now and then. Appreciate any advice in this regard. Cheers -- skuzapo ------------------------------------------------------------------------ skuzapo's Profile: http://www.excelforum.com/member.php...o&userid=27430 View this thread: http://www.excelforum.com/showthread...hreadid=519893 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data too large for Excel, need to query Access data for results
skuzapo,
Why not just link your Excel file to Access; File Get External Data Link Tables. Then you can base your report on a query that includes only the new records, however you decide that. NickHK "skuzapo" wrote in message ... Hi Nick - I read the article in the microsoft knowledge base you submitted and I was wondering if you could help me with a similar question. I'm trying to do the same thing as Sue but the otherway round. I have a data list in Excel which is added to every now and then. I'd like to automate the transfer of this list into an access table from where I run some reports. I don't mind if this is an append everytime a few lines is added or a complete transfer of all the lines once every now and then. Appreciate any advice in this regard. Cheers -- skuzapo ------------------------------------------------------------------------ skuzapo's Profile: http://www.excelforum.com/member.php...o&userid=27430 View this thread: http://www.excelforum.com/showthread...hreadid=519893 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data too large for Excel, need to query Access data for results
NickHK wrote: Susan, <Duke's comment Pulling data from Access into Excel isn't difficult. Here's a starting point http://support.microsoft.com/kb/295646 It explains how to pull Access data into Excel using ADO. You don't need to do lots of coding if your queries are fairly standardized, and you may find it is much faster than having 60,000+ rows in Excel </Duke's comment NickHK Nick, seeing that I'm using Excel 2003, I went here http://support.microsoft.com/kb/246335/EN-US/ Followed it to the letter, clicked on the button, and got no responsen and Excel was non-responsive.. Question: I paste the code on the userform view code, after any existing code, which was 2 lines identifying the button? TIA for any leads. Susan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why are access query results different in Excel | Excel Discussion (Misc queries) | |||
Excel Data Query from Access | Excel Discussion (Misc queries) | |||
Access query results to Excel | Excel Discussion (Misc queries) | |||
Getting Access Query Data to Excel | Excel Programming | |||
Copy the results of an Access query into an Excel spreadsheet | Excel Programming |