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.

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.

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.

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.

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.