Hi all!
I'm new to the Excel group; I develop in Access. Here's my
question/dillemma:

I have a client that needs to see there data in XLS format, thus I created a
script from within Access to dump a specific Crosstab Query into an XLS work
sheet. The client now wants to see the data grouped by specific criteria
(Divisions) within seperate workbooks in 1 spreadsheet. I almost sure that I
can't specify workbooks from with MS Acces; thus I need a way of automating
this process from with Excel. Obviously this can be achieved by creating a
macro, but I don't know where to start. Example:

I want to look at the 1st column (Division) and copy every corresponding
cell that meets that specific Division criteria to a worksheet.

Any thoughts?
Thanks in advance!
Anthony Viscomi

A cross tab query is Access's version of pivot tables. Why not create a pivot
table in Excel that queries the access database. (Menu) Data - Pivot Table
in Excel. Take a look at it and give it a try. Doing it this way also
eliminates the pesky 65,535 row limitation in Excel. If not you could also
look at using the query function in excel which could be used to grab data
from your Cross Tab... If you need some more help just ask.

Hope this Helps...

"Anthony Viscomi" wrote:

Hi all!
I'm new to the Excel group; I develop in Access. Here's my
question/dillemma:

I have a client that needs to see there data in XLS format, thus I created a
script from within Access to dump a specific Crosstab Query into an XLS work
sheet. The client now wants to see the data grouped by specific criteria
(Divisions) within seperate workbooks in 1 spreadsheet. I almost sure that I
can't specify workbooks from with MS Acces; thus I need a way of automating
this process from with Excel. Obviously this can be achieved by creating a
macro, but I don't know where to start. Example:

I want to look at the 1st column (Division) and copy every corresponding
cell that meets that specific Division criteria to a worksheet.

Any thoughts?
Thanks in advance!
Anthony Viscomi

Great, I'll try this rught away.
"Jim Thomlinson" wrote in message
...
A cross tab query is Access's version of pivot tables. Why not create a
pivot
table in Excel that queries the access database. (Menu) Data - Pivot
Table
in Excel. Take a look at it and give it a try. Doing it this way also
eliminates the pesky 65,535 row limitation in Excel. If not you could also
look at using the query function in excel which could be used to grab data
from your Cross Tab... If you need some more help just ask.

Hope this Helps...

"Anthony Viscomi" wrote:

Hi all!
I'm new to the Excel group; I develop in Access. Here's my
question/dillemma:

I have a client that needs to see there data in XLS format, thus I
created a
script from within Access to dump a specific Crosstab Query into an XLS
work
sheet. The client now wants to see the data grouped by specific criteria
(Divisions) within seperate workbooks in 1 spreadsheet. I almost sure
that I
can't specify workbooks from with MS Acces; thus I need a way of
automating
this process from with Excel. Obviously this can be achieved by creating
a
macro, but I don't know where to start. Example:

I want to look at the 1st column (Division) and copy every corresponding
cell that meets that specific Division criteria to a worksheet.

Any thoughts?
Thanks in advance!
Anthony Viscomi

Pivot table won't work, the cell ranges (# of rows, columns will always
reamin the same) may differ from month to month. I need to create some sort
of script that looks for specific values (that I'll define) and copies or
moves the entire range of cells for that value to a new worksheet. I
envision the execution of this script at startup/opening.

"Jim Thomlinson" wrote in message
...
A cross tab query is Access's version of pivot tables. Why not create a
pivot
table in Excel that queries the access database. (Menu) Data - Pivot
Table
in Excel. Take a look at it and give it a try. Doing it this way also
eliminates the pesky 65,535 row limitation in Excel. If not you could also
look at using the query function in excel which could be used to grab data
from your Cross Tab... If you need some more help just ask.

Hope this Helps...

"Anthony Viscomi" wrote:

Hi all!
I'm new to the Excel group; I develop in Access. Here's my
question/dillemma:

I have a client that needs to see there data in XLS format, thus I
created a
script from within Access to dump a specific Crosstab Query into an XLS
work
sheet. The client now wants to see the data grouped by specific criteria
(Divisions) within seperate workbooks in 1 spreadsheet. I almost sure
that I
can't specify workbooks from with MS Acces; thus I need a way of
automating
this process from with Excel. Obviously this can be achieved by creating
a
macro, but I don't know where to start. Example:

I want to look at the 1st column (Division) and copy every corresponding
cell that meets that specific Division criteria to a worksheet.

Any thoughts?
Thanks in advance!
Anthony Viscomi

Don't run the Excel Pivot Table off of the Cross tab query. Run it directly
off of the souce table or create a source query. The pivot table can be set
to refresh on open, just make sure that the source mdb file stays in the same
spot.

Otherwise, I assume your are just importing the cross tab to Excel. You need
to place a script into the ThisWorkbook module in the OnOpen event.

How familiar are you with VBA?

"Anthony Viscomi" wrote:

Pivot table won't work, the cell ranges (# of rows, columns will always
reamin the same) may differ from month to month. I need to create some sort
of script that looks for specific values (that I'll define) and copies or
moves the entire range of cells for that value to a new worksheet. I
envision the execution of this script at startup/opening.

"Jim Thomlinson" wrote in message
...
A cross tab query is Access's version of pivot tables. Why not create a
pivot
table in Excel that queries the access database. (Menu) Data - Pivot
Table
in Excel. Take a look at it and give it a try. Doing it this way also
eliminates the pesky 65,535 row limitation in Excel. If not you could also
look at using the query function in excel which could be used to grab data
from your Cross Tab... If you need some more help just ask.

Hope this Helps...

"Anthony Viscomi" wrote:

Hi all!
I'm new to the Excel group; I develop in Access. Here's my
question/dillemma:

I have a client that needs to see there data in XLS format, thus I
created a
script from within Access to dump a specific Crosstab Query into an XLS
work
sheet. The client now wants to see the data grouped by specific criteria
(Divisions) within seperate workbooks in 1 spreadsheet. I almost sure
that I
can't specify workbooks from with MS Acces; thus I need a way of
automating
this process from with Excel. Obviously this can be achieved by creating
a
macro, but I don't know where to start. Example:

I want to look at the 1st column (Division) and copy every corresponding
cell that meets that specific Division criteria to a worksheet.

Any thoughts?
Thanks in advance!
Anthony Viscomi

Yes, I am exporting the cross-tab from Access into Excel. My VBA skills
aren't too bad. If someone could provide me with a "shell/outline" of what I
need to do; I am able to "fill in the blanks" with my own parameters.

Thanks!
"Jim Thomlinson" wrote in message
...
Don't run the Excel Pivot Table off of the Cross tab query. Run it
directly
off of the souce table or create a source query. The pivot table can be
set
to refresh on open, just make sure that the source mdb file stays in the
same
spot.

Otherwise, I assume your are just importing the cross tab to Excel. You
need
to place a script into the ThisWorkbook module in the OnOpen event.

How familiar are you with VBA?

"Anthony Viscomi" wrote:

Pivot table won't work, the cell ranges (# of rows, columns will always
reamin the same) may differ from month to month. I need to create some
sort
of script that looks for specific values (that I'll define) and copies or
moves the entire range of cells for that value to a new worksheet. I
envision the execution of this script at startup/opening.

"Jim Thomlinson" wrote in
message
...
A cross tab query is Access's version of pivot tables. Why not create a
pivot
table in Excel that queries the access database. (Menu) Data - Pivot
Table
in Excel. Take a look at it and give it a try. Doing it this way also
eliminates the pesky 65,535 row limitation in Excel. If not you could
also
look at using the query function in excel which could be used to grab
data
from your Cross Tab... If you need some more help just ask.

Hope this Helps...

"Anthony Viscomi" wrote:

Hi all!
I'm new to the Excel group; I develop in Access. Here's my
question/dillemma:

I have a client that needs to see there data in XLS format, thus I
created a
script from within Access to dump a specific Crosstab Query into an
XLS
work
sheet. The client now wants to see the data grouped by specific
criteria
(Divisions) within seperate workbooks in 1 spreadsheet. I almost sure
that I
can't specify workbooks from with MS Acces; thus I need a way of
automating
this process from with Excel. Obviously this can be achieved by
creating
a
macro, but I don't know where to start. Example:

I want to look at the 1st column (Division) and copy every
corresponding
cell that meets that specific Division criteria to a worksheet.

Any thoughts?
Thanks in advance!
Anthony Viscomi

When you export from access are you creating a new workbook everytime, or
exactly how are you getting the data to Excel. If you create a new workbook
each time this gets a lot more difficult because the new workbook won't have
any code in it. If you are creating a new sheet you will need to know the
name of the new sheet. If you are overwiriting an existing sheet in an
existing workbook then this is not too bad.

"Anthony Viscomi" wrote:

Yes, I am exporting the cross-tab from Access into Excel. My VBA skills
aren't too bad. If someone could provide me with a "shell/outline" of what I
need to do; I am able to "fill in the blanks" with my own parameters.

Thanks!
"Jim Thomlinson" wrote in message
...
Don't run the Excel Pivot Table off of the Cross tab query. Run it
directly
off of the souce table or create a source query. The pivot table can be
set
to refresh on open, just make sure that the source mdb file stays in the
same
spot.

Otherwise, I assume your are just importing the cross tab to Excel. You
need
to place a script into the ThisWorkbook module in the OnOpen event.

How familiar are you with VBA?

"Anthony Viscomi" wrote:

Pivot table won't work, the cell ranges (# of rows, columns will always
reamin the same) may differ from month to month. I need to create some
sort
of script that looks for specific values (that I'll define) and copies or
moves the entire range of cells for that value to a new worksheet. I
envision the execution of this script at startup/opening.

"Jim Thomlinson" wrote in
message
...
A cross tab query is Access's version of pivot tables. Why not create a
pivot
table in Excel that queries the access database. (Menu) Data - Pivot
Table
in Excel. Take a look at it and give it a try. Doing it this way also
eliminates the pesky 65,535 row limitation in Excel. If not you could
also
look at using the query function in excel which could be used to grab
data
from your Cross Tab... If you need some more help just ask.

Hope this Helps...

"Anthony Viscomi" wrote:

Hi all!
I'm new to the Excel group; I develop in Access. Here's my
question/dillemma:

I have a client that needs to see there data in XLS format, thus I
created a
script from within Access to dump a specific Crosstab Query into an
XLS
work
sheet. The client now wants to see the data grouped by specific
criteria
(Divisions) within seperate workbooks in 1 spreadsheet. I almost sure
that I
can't specify workbooks from with MS Acces; thus I need a way of
automating
this process from with Excel. Obviously this can be achieved by
creating
a
macro, but I don't know where to start. Example:

I want to look at the 1st column (Division) and copy every
corresponding
cell that meets that specific Division criteria to a worksheet.

Any thoughts?
Thanks in advance!
Anthony Viscomi

Good point; I am overwriting an existing sheet. Actually, if I could figure
out how to export an Access query and desgnate a worksheet; then I would
need to do anything from within Excel. All of my criteria would be specified
from within the Access queries. I've posted the question of naming
worksheets from an Access export multiple times in Access groups, but had no
real answers.
"Jim Thomlinson" wrote in message
...
When you export from access are you creating a new workbook everytime, or
exactly how are you getting the data to Excel. If you create a new
workbook
each time this gets a lot more difficult because the new workbook won't
have
any code in it. If you are creating a new sheet you will need to know the
name of the new sheet. If you are overwiriting an existing sheet in an
existing workbook then this is not too bad.

"Anthony Viscomi" wrote:

Yes, I am exporting the cross-tab from Access into Excel. My VBA skills
aren't too bad. If someone could provide me with a "shell/outline" of
what I
need to do; I am able to "fill in the blanks" with my own parameters.

Thanks!
"Jim Thomlinson" wrote in
message
...
Don't run the Excel Pivot Table off of the Cross tab query. Run it
directly
off of the souce table or create a source query. The pivot table can be
set
to refresh on open, just make sure that the source mdb file stays in
the
same
spot.

Otherwise, I assume your are just importing the cross tab to Excel. You
need
to place a script into the ThisWorkbook module in the OnOpen event.

How familiar are you with VBA?

"Anthony Viscomi" wrote:

Pivot table won't work, the cell ranges (# of rows, columns will
always
reamin the same) may differ from month to month. I need to create some
sort
of script that looks for specific values (that I'll define) and copies
or
moves the entire range of cells for that value to a new worksheet. I
envision the execution of this script at startup/opening.

"Jim Thomlinson" wrote in
message
...
A cross tab query is Access's version of pivot tables. Why not create
a
pivot
table in Excel that queries the access database. (Menu) Data -
Pivot
Table
in Excel. Take a look at it and give it a try. Doing it this way
also
eliminates the pesky 65,535 row limitation in Excel. If not you
could
also
look at using the query function in excel which could be used to
grab
data
from your Cross Tab... If you need some more help just ask.

Hope this Helps...

"Anthony Viscomi" wrote:

Hi all!
I'm new to the Excel group; I develop in Access. Here's my
question/dillemma:

I have a client that needs to see there data in XLS format, thus I
created a
script from within Access to dump a specific Crosstab Query into an
XLS
work
sheet. The client now wants to see the data grouped by specific
criteria
(Divisions) within seperate workbooks in 1 spreadsheet. I almost
sure
that I
can't specify workbooks from with MS Acces; thus I need a way of
automating
this process from with Excel. Obviously this can be achieved by
creating
a
macro, but I don't know where to start. Example:

I want to look at the 1st column (Division) and copy every
corresponding
cell that meets that specific Division criteria to a worksheet.

Any thoughts?
Thanks in advance!
Anthony Viscomi

In the project explorer you will see the object model for the Excel Spread
sheet. I assume that you have at least 2 sheet. The RawData sheet and the
Destination sheet.

Make sure you have option explicit at the top of each module or sheet in the
code window...

Select the raw data sheet. In the properties window. Change (Name) from
Sheet1 to shtRawData and for the destination sheet (Name) to shtDestination.
This will make for easier more compact coding

insert a module into the project. In the module paste...

public sub On_Open
dim rngFrom as range
dim rngTo as range

set rngFrom = shtRawData.Range("A2")
set rngTo = shtDestination.range("A2")

do while rngfrom.value < "" 'Stop condition
if rngFrom.offset(0,1).value = "Whaterver" then
rngfrom.entirerow.copy rngTo
set rngTo = rngTo.Offset(.,0)
endif
set rngFrom = rngFrom.offset(1,0)
Loop

set rngFrom = Nothing
set rngTo = Nothing

end sub

Hope this helps...

"Anthony Viscomi" wrote:

Good point; I am overwriting an existing sheet. Actually, if I could figure
out how to export an Access query and desgnate a worksheet; then I would
need to do anything from within Excel. All of my criteria would be specified
from within the Access queries. I've posted the question of naming
worksheets from an Access export multiple times in Access groups, but had no
real answers.
"Jim Thomlinson" wrote in message
...
When you export from access are you creating a new workbook everytime, or
exactly how are you getting the data to Excel. If you create a new
workbook
each time this gets a lot more difficult because the new workbook won't
have
any code in it. If you are creating a new sheet you will need to know the
name of the new sheet. If you are overwiriting an existing sheet in an
existing workbook then this is not too bad.

"Anthony Viscomi" wrote:

Yes, I am exporting the cross-tab from Access into Excel. My VBA skills
aren't too bad. If someone could provide me with a "shell/outline" of
what I
need to do; I am able to "fill in the blanks" with my own parameters.

Thanks!
"Jim Thomlinson" wrote in
message
...
Don't run the Excel Pivot Table off of the Cross tab query. Run it
directly
off of the souce table or create a source query. The pivot table can be
set
to refresh on open, just make sure that the source mdb file stays in
the
same
spot.

Otherwise, I assume your are just importing the cross tab to Excel. You
need
to place a script into the ThisWorkbook module in the OnOpen event.

How familiar are you with VBA?

"Anthony Viscomi" wrote:

Pivot table won't work, the cell ranges (# of rows, columns will
always
reamin the same) may differ from month to month. I need to create some
sort
of script that looks for specific values (that I'll define) and copies
or
moves the entire range of cells for that value to a new worksheet. I
envision the execution of this script at startup/opening.

"Jim Thomlinson" wrote in
message
...
A cross tab query is Access's version of pivot tables. Why not create
a
pivot
table in Excel that queries the access database. (Menu) Data -
Pivot
Table
in Excel. Take a look at it and give it a try. Doing it this way
also
eliminates the pesky 65,535 row limitation in Excel. If not you
could
also
look at using the query function in excel which could be used to
grab
data
from your Cross Tab... If you need some more help just ask.

Hope this Helps...

"Anthony Viscomi" wrote:

Hi all!
I'm new to the Excel group; I develop in Access. Here's my
question/dillemma:

I have a client that needs to see there data in XLS format, thus I
created a
script from within Access to dump a specific Crosstab Query into an
XLS
work
sheet. The client now wants to see the data grouped by specific
criteria
(Divisions) within seperate workbooks in 1 spreadsheet. I almost
sure
that I
can't specify workbooks from with MS Acces; thus I need a way of
automating
this process from with Excel. Obviously this can be achieved by
creating
a
macro, but I don't know where to start. Example:

I want to look at the 1st column (Division) and copy every
corresponding
cell that meets that specific Division criteria to a worksheet.

Any thoughts?
Thanks in advance!
Anthony Viscomi

The code doesn't like the line:
Set rngTo = rngTo.Offset(., 0)
"Jim Thomlinson" wrote in message
...
In the project explorer you will see the object model for the Excel Spread
sheet. I assume that you have at least 2 sheet. The RawData sheet and the
Destination sheet.

Make sure you have option explicit at the top of each module or sheet in
the
code window...

Select the raw data sheet. In the properties window. Change (Name) from
Sheet1 to shtRawData and for the destination sheet (Name) to
shtDestination.
This will make for easier more compact coding

insert a module into the project. In the module paste...

public sub On_Open
dim rngFrom as range
dim rngTo as range

set rngFrom = shtRawData.Range("A2")
set rngTo = shtDestination.range("A2")

do while rngfrom.value < "" 'Stop condition
if rngFrom.offset(0,1).value = "Whaterver" then
rngfrom.entirerow.copy rngTo
set rngTo = rngTo.Offset(.,0)
endif
set rngFrom = rngFrom.offset(1,0)
Loop

set rngFrom = Nothing
set rngTo = Nothing

end sub

Hope this helps...

"Anthony Viscomi" wrote:

Good point; I am overwriting an existing sheet. Actually, if I could
figure
out how to export an Access query and desgnate a worksheet; then I would
need to do anything from within Excel. All of my criteria would be
specified
from within the Access queries. I've posted the question of naming
worksheets from an Access export multiple times in Access groups, but had
no
real answers.
"Jim Thomlinson" wrote in
message
...
When you export from access are you creating a new workbook everytime,
or
exactly how are you getting the data to Excel. If you create a new
workbook
each time this gets a lot more difficult because the new workbook won't
have
any code in it. If you are creating a new sheet you will need to know
the
name of the new sheet. If you are overwiriting an existing sheet in an
existing workbook then this is not too bad.

"Anthony Viscomi" wrote:

Yes, I am exporting the cross-tab from Access into Excel. My VBA
skills
aren't too bad. If someone could provide me with a "shell/outline" of
what I
need to do; I am able to "fill in the blanks" with my own parameters.

Thanks!
"Jim Thomlinson" wrote in
message
...
Don't run the Excel Pivot Table off of the Cross tab query. Run it
directly
off of the souce table or create a source query. The pivot table can
be
set
to refresh on open, just make sure that the source mdb file stays in
the
same
spot.

Otherwise, I assume your are just importing the cross tab to Excel.
You
need
to place a script into the ThisWorkbook module in the OnOpen event.

How familiar are you with VBA?

"Anthony Viscomi" wrote:

Pivot table won't work, the cell ranges (# of rows, columns will
always
reamin the same) may differ from month to month. I need to create
some
sort
of script that looks for specific values (that I'll define) and
copies
or
moves the entire range of cells for that value to a new worksheet.
I
envision the execution of this script at startup/opening.

"Jim Thomlinson" wrote in
message
...
A cross tab query is Access's version of pivot tables. Why not
create
a
pivot
table in Excel that queries the access database. (Menu) Data -
Pivot
Table
in Excel. Take a look at it and give it a try. Doing it this way
also
eliminates the pesky 65,535 row limitation in Excel. If not you
could
also
look at using the query function in excel which could be used to
grab
data
from your Cross Tab... If you need some more help just ask.

Hope this Helps...

"Anthony Viscomi" wrote:

Hi all!
I'm new to the Excel group; I develop in Access. Here's my
question/dillemma:

I have a client that needs to see there data in XLS format, thus
I
created a
script from within Access to dump a specific Crosstab Query into
an
XLS
work
sheet. The client now wants to see the data grouped by specific
criteria
(Divisions) within seperate workbooks in 1 spreadsheet. I almost
sure
that I
can't specify workbooks from with MS Acces; thus I need a way of
automating
this process from with Excel. Obviously this can be achieved by
creating
a
macro, but I don't know where to start. Example:

I want to look at the 1st column (Division) and copy every
corresponding
cell that meets that specific Division criteria to a worksheet.

Any thoughts?
Thanks in advance!
Anthony Viscomi