Darryl Collins
darryl at whittleconsulting.com.au
Wed Apr 2 20:31:41 CDT 2014
Exactly Bill... With the logic / caveat of 'breaking' the CSV / TAB output before you hit the 65545 line limit and append the rest of the data in "FileOutput_2.TXT" if you are restricted to using XL2003 or less. So you might need to output multiple TXT files, which can then be dealt with in Excel as the end user sees fit. Although that depends what you want to do with the data in Excel. For example, if the 2003 Excel user want to summarise the data (say using a Pivot Table), then you can hook the PT into an external data source - include a text file - if it is readable and work from there. So there may be other sneaky options here depending on the requirements. Cheers Darryl. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson Sent: Thursday, 3 April 2014 12:24 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access to Excel 65K row barrier Yes I agree I like that method. However it is an Excel method of the Range object so you would have to automate Excel using a ADO or DAO recordset. However that will still overrun the excel 2003 worksheet so one would have to automate Excel 2007+, which Bob says he does not have .... Writing from Access to a text file was, I believe, Bob's only option... as has already been suggested. I have had problems with tab separated text files, but obviously they have a fan in Stuart. On Apr 2, 2014 8:45 PM, "Paul Hartland" <paul.hartland at googlemail.com> wrote: > I used to use CopyFromRecordset all the time used to work for all my > needs and was very fast as far as I recall. > > Paul > > > On 3 April 2014 00:51, Stuart McLachlan <stuart.mclachlan at gmail.com> > wrote: > > > What is this CopyFromRS you speak of? > > > > Access doesn't seen to know about it :) > > > > > > On 2 Apr 2014 at 23:37, Darryl Collins wrote: > > > > > Yes, agreed. That is one of the better ways to go. I like to use > > > CopyFromRS in many instances as it allows you to move large volumes of > > > data quickly between Access and Excel. Depends on the task and > > > requirement though. > > > > > > From memory it is the "Export To Excel" functionality where the > > > 65545 row limit is bothersome, but I could be wrong. Never really > > > use that approach so I haven't looked at it for years. > > > > > > Cheers > > > Darryl. > > > > > > > > > > > > -----Original Message----- > > > From: accessd-bounces at databaseadvisors.com > > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart > > > McLachlan Sent: Thursday, 3 April 2014 10:32 AM To: Access > > > Developers discussion and problem solving Subject: Re: [AccessD] > > > Access to Excel 65K row barrier > > > > > > No limit at all if you open the query as a recordset and write each > > > record out sequentially to a Tab delimited file. That is always my > > > preferred method. > > > > > > -- > > > Stuart > > > > > > On 2 Apr 2014 at 23:09, Darryl Collins wrote: > > > > > > > Yes, I think you are correct. Even today some process will > > > > limit you to 65K rows. Legacy stuff that lurks deep in the machine... > > > > > > > > Heh... took me a while to 'get' the baseball reference, but now > > > > I understand :) > > > > > > > > > > > > > > > > -----Original Message----- > > > > From: accessd-bounces at databaseadvisors.com > > > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill > > > > Benson Sent: Thursday, 3 April 2014 10:03 AM To: Access > > > > Developers discussion and problem solving Subject: Re: [AccessD] > > > > Access to Excel 65K row barrier > > > > > > > > Are you sure that he is referring to the catcher and not the > > > > pitcher? > > > > > > > > I didn't contradict for once just in case this was a limitation > > > > of Access rather than Excel, though my thinking lacks much > > > > logic. On Apr 2, 2014 6:48 PM, "Darryl Collins" > > > > <darryl at whittleconsulting.com.au> wrote: > > > > > > > > > Double?? > > > > > > > > > > Try 65,545 Rows (2003) vs 1,048,576 Rows (2007+) > > > > > > > > > > Much more than double! > > > > > > > > > > Cheers > > > > > Darryl. > > > > > > > > > > -----Original Message----- > > > > > From: accessd-bounces at databaseadvisors.com [mailto: > > > > > accessd-bounces at databaseadvisors.com] On Behalf Of John > > > > > Serrano > > > > > Sent: Thursday, 3 April 2014 7:24 AM To: Access Developers > > > > > discussion and problem solving Subject: Re: [AccessD] Access > > > > > to Excel 65K row barrier > > > > > > > > > > Bob, > > > > > > > > > > The newer version of Excel has double the row size however as > > > > > time goes on will this suffice? I guess the real question is > > > > > why are you taking data from a query into excel? Do you need > > > > > to perform additional analysis on the data or just report it out? > > > > > > > > > > Nevertheless, you could change your references in your library > > > > > to Office 14.0 I beleive it is... > > > > > > > > > > > > > > > On Wed, Apr 2, 2014 at 4:12 PM, Bob Heygood > > > > > <accesspro at gmail.com> > > > > > wrote: > > > > > > > > > > > Hello to the list, > > > > > > > > > > > > I have been using the Access 2003 to export the results of a > > > > > > query to Excel. All has been well until the query has grown > > > > > > to over 65K rows/records. My client has Off 2010. > > > > > > > > > > > > I just don't want to upgrade to a newer version of Office. > > > > > > I have Google docs and SkyDrive office. > > > > > > > > > > > > So, the question is how to get him an Excel file in Off 2007 > > > > > > or > > > > > > 2010 format ? I can't create too much work on his end. He so > > > > > > far is not able to splice two files together. > > > > > > > > > > > > > > > > > > TIA > > > > > > > > > > > > Bob Heygood > > > > > > > > > > > > -- > > > > > > AccessD mailing list > > > > > > AccessD at databaseadvisors.com > > > > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > > > > Website: http://www.databaseadvisors.com > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > John Serrano > > > > > -- > > > > > AccessD mailing list > > > > > AccessD at databaseadvisors.com > > > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > > > Website: http://www.databaseadvisors.com > > > > > > > > > > -- > > > > > AccessD mailing list > > > > > AccessD at databaseadvisors.com > > > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > > > Website: http://www.databaseadvisors.com > > > > > > > > > -- > > > > AccessD mailing list > > > > AccessD at databaseadvisors.com > > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > > Website: http://www.databaseadvisors.com > > > > > > > > -- > > > > AccessD mailing list > > > > AccessD at databaseadvisors.com > > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > > Website: http://www.databaseadvisors.com > > > > > > > > > > > > > -- > > > AccessD mailing list > > > AccessD at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > > > > -- > > > AccessD mailing list > > > AccessD at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > > > > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > -- > Paul Hartland > paul.hartland at googlemail.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com