Darryl Collins
darryl at whittleconsulting.com.au
Mon Jul 29 00:04:19 CDT 2013
"Is the code able to refresh the data as well?" Well, yes in effect as you call it down 'brand new' each and everytime. You would basically remove / destroy the older version and replace with a new x-tab. In running the query Access will update it before it's imported into Excel. Cheers Darryl -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Nicholas Lee Sent: Monday, 29 July 2013 1:58 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Import CrossTab query to Excel 2007 Hi Darryl, Thanks for the information. I'm knee for the VBA code as I'm totally new to Access environment. Is the code able to refresh the data as well? Thanks in advance. Nicholas -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins Sent: Saturday, 27 July, 2013 12:16 To: Access Developers discussion and problem solving Subject: Re: [AccessD] Import CrossTab query to Excel 2007 Ok... I see what you mean. Using XL2010 I cannot see the x-tabs queries in an Access DB for import - only SELECT queries and tables. How silly - I wonder why they did that? As I have said, I have never tried to use that method and either import it using code, or export it from Access so I have not noticed that before. Why not Export the x-tab query from Access into Excel instead? That certainly works using the manual option on the ribbon and I do that occasionally. Worth considering. If it is something you want to do a lot, than I would recommend you use VBA code. If you have no idea how to use code I can send you an example, but it won't be for a couple of days. I have always used ADO and the 'copy from recordset' method, which will pull the x-tab into Excel fast and consistently. There maybe a size restriction on how big the x-tab can be (?), but I haven't hit that and some of mine are many thousands of rows in length and maybe 20+ columns in width. You can certainly export a xtab query from Access to a (new) excel workbook without issue. I do that occasionally if the xtab is large and I want to poke around with filter options in Excel with it.... Anyway, let me know what works best for you. cheers Darryl ________________________________________ From: accessd-bounces at databaseadvisors.com <accessd-bounces at databaseadvisors.com> on behalf of Nicholas Lee <Nicholas at nagase.com.my> Sent: Friday, 26 July 2013 3:40 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Import CrossTab query to Excel 2007 Darryl, Thanks. I like the method of "hook an excel pivot table into access". So, there is no way to import the whole crosstab query into Excel, right? Nicholas -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins Sent: Friday, 26 July, 2013 12:15 To: Access Developers discussion and problem solving Subject: Re: [AccessD] Import CrossTab query to Excel 2007 I usually use copyfromRS (copy from recordset) from Excel with an ADO hook into Access for this sort of thing. I know that works fine and is very reliable once you set it up. Just be aware that some JET functions will fail (such as using NZ for example) when using this method. There are ways around this sort of thing (Use iif(is Null..)) rather than NZ for instance). The other (sometimes more powerful options) is to hook an Excel Pivot table into access. A 3rd option maybe to export the report from Access and then import it into Excel as a sheet. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Nicholas Lee Sent: Friday, 26 July 2013 1:37 PM To: Access Developers discussion and problem solving Subject: [AccessD] Import CrossTab query to Excel 2007 Hi All, Please help. I wish to import crosstab query into Excel 2007 (DATA->FROM ACCESS). The pop up box show almost all the table and query except crosstab. What should I do? Nicholas -- 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