Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Construct Coluns via VBA
How can I construct columns based off an Access query. The query is a
crosstab, therefore the number of columns will vary depending on the criteria passed to it. I need to find a solution to export the data from the query into an Excel spreadsheet. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Construct Coluns via VBA
The functional equivalent of a cross tab in Excel is a pivot table. Have you
considered adding a pivot table to a sheet and referencing it to the source Access Data. Click on Data - Pivot table and select Access as the data source. You will not reference the cross tab but rather the query or table that was used to create the cross tab. There is no 65,536 row limit on doing this and you get all of the benefits of a dynamic pivot table as opposed to a static cross tab import. -- HTH... Jim Thomlinson "scuba79" wrote: How can I construct columns based off an Access query. The query is a crosstab, therefore the number of columns will vary depending on the criteria passed to it. I need to find a solution to export the data from the query into an Excel spreadsheet. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Construct Coluns via VBA
I can't use the pivot table function the spreadsheet must be static to
prevent end users from changing data around "Jim Thomlinson" wrote: The functional equivalent of a cross tab in Excel is a pivot table. Have you considered adding a pivot table to a sheet and referencing it to the source Access Data. Click on Data - Pivot table and select Access as the data source. You will not reference the cross tab but rather the query or table that was used to create the cross tab. There is no 65,536 row limit on doing this and you get all of the benefits of a dynamic pivot table as opposed to a static cross tab import. -- HTH... Jim Thomlinson "scuba79" wrote: How can I construct columns based off an Access query. The query is a crosstab, therefore the number of columns will vary depending on the criteria passed to it. I need to find a solution to export the data from the query into an Excel spreadsheet. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Construct Coluns via VBA
Why not lock the sheet down so that the end user can not change it around.
Unlock just the parts they are allowed to play with. -- HTH... Jim Thomlinson "scuba79" wrote: I can't use the pivot table function the spreadsheet must be static to prevent end users from changing data around "Jim Thomlinson" wrote: The functional equivalent of a cross tab in Excel is a pivot table. Have you considered adding a pivot table to a sheet and referencing it to the source Access Data. Click on Data - Pivot table and select Access as the data source. You will not reference the cross tab but rather the query or table that was used to create the cross tab. There is no 65,536 row limit on doing this and you get all of the benefits of a dynamic pivot table as opposed to a static cross tab import. -- HTH... Jim Thomlinson "scuba79" wrote: How can I construct columns based off an Access query. The query is a crosstab, therefore the number of columns will vary depending on the criteria passed to it. I need to find a solution to export the data from the query into an Excel spreadsheet. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Construct Coluns via VBA
Unfortunately I can't use the Pivot table at all, the database has security
being used and I can't pass the login information to get into the database... Thanks for the ideas... I'll figure something out... "Jim Thomlinson" wrote: Why not lock the sheet down so that the end user can not change it around. Unlock just the parts they are allowed to play with. -- HTH... Jim Thomlinson "scuba79" wrote: I can't use the pivot table function the spreadsheet must be static to prevent end users from changing data around "Jim Thomlinson" wrote: The functional equivalent of a cross tab in Excel is a pivot table. Have you considered adding a pivot table to a sheet and referencing it to the source Access Data. Click on Data - Pivot table and select Access as the data source. You will not reference the cross tab but rather the query or table that was used to create the cross tab. There is no 65,536 row limit on doing this and you get all of the benefits of a dynamic pivot table as opposed to a static cross tab import. -- HTH... Jim Thomlinson "scuba79" wrote: How can I construct columns based off an Access query. The query is a crosstab, therefore the number of columns will vary depending on the criteria passed to it. I need to find a solution to export the data from the query into an Excel spreadsheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting number and alphabet rows and coluns | Excel Discussion (Misc queries) | |||
Move data in repeated sets of coluns | Excel Worksheet Functions | |||
if else construct | Excel Worksheet Functions | |||
Construct a range in VB | Excel Discussion (Misc queries) | |||
Macro closes all coluns and not just the programmed ones, why? | Excel Programming |