[AccessD] Import CrossTab query to Excel 2007

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



More information about the AccessD mailing list