Excel in Access

[Mike T.]

Hello,

I have moved all my work from Excel to Access coz Excel
couldn't handle the amounts of data and multiple tables.
Nobody who I produce reports for can handle Access and
want their reports in Excel (so that they can manipulate
them within Excel) - rather than say, RTF or SNP.

The reports are a bit complicated so they don't work at
all nicely if exported to Excel, and lose a lot of data
(eg charts).

I need to learn how to work with Excel within Access - DAO
or OLE or whichever it is. I'll maybe need to feed pre-
made templates or something. Can anyone offer tips or
sources of help?

I need to learn it pretty darn well in the next week.
(possible have to automate emails too, but hopefully not)

Thanks all.

Mike

 

[Allan Thompson]

Mike,

Without knowing more about your data and Excel reporting needs, its hard to
give a definitive answer. The easiest approach, and hopefully the one you
can use, is to use DoCmd.TransferSpreadsheet (see TransferSpreadsheet Method
under MS Access help). This command allows you to export data from tables
or queries to Excel. Since you can use query datasets, the data you export
to Excel can be data joined from multiple tables, calculated data, summary
data, and/or filtered data.. The Excel sheet that you transfer to can be
setup as a template with the additional formulas, charts, etc already in
place. Macros or VBA code can be used to automate the data transfer to
multiple Excel files.

If this approach sounds workable and you need more direction, let me know.
I can also suggest other alternatives if this does not sound viable. The
automation of e-mails, if needed, shouldn't be too difficult either.

Allan
--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184

 

[Mike]

Thanks Allan,

This sounds like the right kind of track I need to be on.

I am aware of how you can create a pivot form which can be
opened up in Excel edit mode - this seems to hold the
Excel file within the database (although I could be
wrong) - it's not what I want but holding the excel file
internally might be useful to me. I'll give you more
detail:

I work for a hospital. There are 30 hospitals that send
patients to us for treatment (these are referrals). When
with us, I have allocated information into different
groups -
1. patients who we have only got the referral for
2. patients who we have seen at a clinic
3. patients who we have have agreed to admit (booked list)
4. patients who have been admitted
5. patients who we see at a follow-up clinic

Within these groups referrals can have a different status
(there is a referral key which carries across all
activity) - eg. point 2 can be patients sent back to the
original hospital/patients on hold/Active patients
(waiting for appointment or outcome).

I have created 5 reports in line with the above, and
introduced a grouping level to show the referral status
(forces new page). (There is also a higher grouping for
hospital of origin).

Thanks for bearing with me on this...

I have a form that enables you to view all reports or a
selected report - either showing all hospitals within them
or filtered by hospital.

Now is where I need help... they want to see the report in
Excel - ideally 1 workbook with 5 sheets (I'd remove the
page break for the status grouping - so all statuses will
be on the same sheet). But 5 workbooks would be fine.
Now I would need to run through a process as follows:

-Select 1st hospital in table
-run all 5 reports (shoved in Excel if poss) (if have data)
-attach them to an email
-send to email (held in the email fild of the hospital
table)
-Select 2nd hospital...

This would be done weekly. As you can imagine, the
reports would be referenced on many occasions - and I'd
rather not have 150 (30hospitals*5reports) Excel templates.

Will the transferspreadsheet allow me to make this work,
how?

I really appreciate you getting back to me on this.

Many thanks,

Basil

 

[Mike]

PS Michael's my middle name.

-----Original Message-----
Mike,

Without knowing more about your data and Excel reporting needs, its hard to
give a definitive answer. The easiest approach, and hopefully the one you
can use, is to use DoCmd.TransferSpreadsheet (see TransferSpreadsheet Method
under MS Access help). This command allows you to export data from tables
or queries to Excel. Since you can use query datasets, the data you export
to Excel can be data joined from multiple tables, calculated data, summary
data, and/or filtered data.. The Excel sheet that you transfer to can be
setup as a template with the additional formulas, charts, etc already in
place. Macros or VBA code can be used to automate the data transfer to
multiple Excel files.

If this approach sounds workable and you need more direction, let me know.
I can also suggest other alternatives if this does not sound viable. The
automation of e-mails, if needed, shouldn't be too difficult either.

Allan
--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184





.

 

[Allan Thompson]

Basil,

The key question is whether you can represent the data from your Access
reports using queries. If so, the TransferSpreadsheet method should work.
The key to making the process more efficient is using VBA, in particular its
looping capability.

See if this makes sense for you.
- Create a template in Excel that has 5 sheets, one for each report type
- In Access, create 5 queries (or modify existing), one for each report.
For the WHERE clause of each query, use something like "WHERE HospitalID =
Forms!frmSelect!cboHospitalID " (cboHospitalID being a combo box on the form
frmSelect)
- create a VBA procedure that loops through all the hospitals using a DAO or
ADO recordset (e.g. recordset name = rsHosp)
- within the loop, you can create an Excel file for each hopital by doing
the following:
1) set the value of cboHospitalID = rsHosp!HospitalID
2) reset a variable that contains the target Excel filename e.g...\Excel
Files\[HospitalID].xls
3) use the Kill statement (see Visual Basic for Applications help in MS
Access) to delete any prior version of the target file
4) copy the source Excel template file to the target file using the FileCopy
statement
5) use the TransferSpreadsheet method to transfer data from each of your 5
queries (which will be filtered for the current hospital) to the appropriate
sheet in the target Excel file
6) e-mail the Excel file
7) move to the next hospital

Let me know if this sounds workable.

Allan

--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184

 

[Mike]

Allan,

First of all, thanks so much - you're a star.

That sounds workable. I already have 5 seperate queries,
and I use a filter (actioned in VBA if required) to
specify the hospital. I have no idea what DAO/ADO
recordsets are though - but I'll look into it and learn -
know any good sources?

I think it would help to explain the grouping levels:
1st - Originating Hospital
2nd - Referral Status (or referral stage depending on
report)
3rd - Referral key (since 1 patient may have >1
appointment)

A couple of worries/doubts in it as a result...

The Referral Status grouping level effectively converts
the field to subtitles in each of the hospital reports.
some of the detail fields and Referral Status header
labels are calculated textboxes that show different data
depending on the Referral Status (subtitle).

Would I be able to recreate this? There would also need
to be a blank row forced between each grouping stage
within the sheet - the detail size is variable so it would
have to be built into the process - is it possible to run
Excel VBA on the workbook from within the Access code?

Secondly due to the referral key grouping, I set "Hide
duplicates" to yes on many fields in the report. Can this
copy across?

If we can sort out the above queries - it sounds great,
and I think I could manage it! Would even remove the need
for email automation!

IF we can't sort out the queries above is it feasible to
have 5 Excel workbooks set up for each report and filter
the different Referral Statuses into seperate sheets -
wouldn't fix the hide duplicates problem though... and
would cause further work - would rather be able to solve
the queries I think!

Thanks loads Allan.

Basil

-----Original Message-----
Basil,

The key question is whether you can represent the data from your Access
reports using queries. If so, the TransferSpreadsheet method should work.
The key to making the process more efficient is using VBA, in particular its
looping capability.

See if this makes sense for you.
- Create a template in Excel that has 5 sheets, one for each report type
- In Access, create 5 queries (or modify existing), one for each report.
For the WHERE clause of each query, use something like "WHERE HospitalID =
Forms!frmSelect!cboHospitalID " (cboHospitalID being a combo box on the form
frmSelect)
- create a VBA procedure that loops through all the hospitals using a DAO or
ADO recordset (e.g. recordset name = rsHosp)
- within the loop, you can create an Excel file for each hopital by doing
the following:
1) set the value of cboHospitalID = rsHosp!HospitalID
2) reset a variable that contains the target Excel filename e.g...\Excel
Files\[HospitalID].xls
3) use the Kill statement (see Visual Basic for Applications help in MS
Access) to delete any prior version of the target file
4) copy the source Excel template file to the target file using the FileCopy
statement
5) use the TransferSpreadsheet method to transfer data from each of your 5
queries (which will be filtered for the current hospital) to the appropriate
sheet in the target Excel file
6) e-mail the Excel file
7) move to the next hospital

Let me know if this sounds workable.

Allan

--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184

Thanks Allan,

This sounds like the right kind of track I need to be on.

I am aware of how you can create a pivot form which can be
opened up in Excel edit mode - this seems to hold the
Excel file within the database (although I could be
wrong) - it's not what I want but holding the excel file
internally might be useful to me. I'll give you more
detail:

I work for a hospital. There are 30 hospitals that send
patients to us for treatment (these are referrals). When
with us, I have allocated information into different
groups -
1. patients who we have only got the referral for
2. patients who we have seen at a clinic
3. patients who we have have agreed to admit (booked list)
4. patients who have been admitted
5. patients who we see at a follow-up clinic

Within these groups referrals can have a different status
(there is a referral key which carries across all
activity) - eg. point 2 can be patients sent back to the
original hospital/patients on hold/Active patients
(waiting for appointment or outcome).

I have created 5 reports in line with the above, and
introduced a grouping level to show the referral status
(forces new page). (There is also a higher grouping for
hospital of origin).

Thanks for bearing with me on this...

I have a form that enables you to view all reports or a
selected report - either showing all hospitals within them
or filtered by hospital.

Now is where I need help... they want to see the report in
Excel - ideally 1 workbook with 5 sheets (I'd remove the
page break for the status grouping - so all statuses will
be on the same sheet). But 5 workbooks would be fine.
Now I would need to run through a process as follows:

-Select 1st hospital in table
-run all 5 reports (shoved in Excel if poss) (if have data)
-attach them to an email
-send to email (held in the email fild of the hospital
table)
-Select 2nd hospital...

This would be done weekly. As you can imagine, the
reports would be referenced on many occasions - and I'd
rather not have 150 (30hospitals*5reports) Excel templates.

Will the transferspreadsheet allow me to make this work,
how?

I really appreciate you getting back to me on this.

Many thanks,

Basil
reporting
needs, its hard to export
data from tables charts,
etc already in in
message Access -
DAO

.

 

[Allan Thompson]

Ah! This is where the superiority of Access over Excel as a reporting tool
becomes evident. But this still may be doable. To handle the suppression
of duplicates in Excel, you need to use conditional formatting. See
http://www.contextures.com/xlCondFormat03.html#Duplicate for an example of
how to do this. You could apply the conditional formatting in advance to
the appropriate columns in your spreadsheet template.

Re some of your other questions:

[ The Referral Status grouping level effectively converts the field to
subtitles in each of the hospital reports.
some of the detail fields and Referral Status header labels are calculated
textboxes that show different data
depending on the Referral Status (subtitle).]
You could handle this by formatting the column using the Switch function
within your query. (See Visual Basic help if you are not familiar with the
Switch function. Access does a good job of hiding its availability)

[There would also need to be a blank row forced between each grouping stage
within the sheet - the detail size is variable so it would have to be built
into the process - is it possible to run Excel VBA on the workbook from
within the Access code?]
Yes, it is possible to use Excel VBA from Access. I'm not sure how you can
handle the multiple break levels, however. I have some ideas but would need
to see your report format before I could give you suggestions.

[I have no idea what DAO/ADO recordsets are though - but I'll look into it
and learn - know any good sources?]
Maybe it was just me, but years ago when I was teaching myself to work with
recordsets, I really struggled. And I was working from a book that was
highly recommended (written by Getz, Litwin and Reddick). If this is your
first time working with DAO, automation of Excel from within Access and
looping constructs, you would probably benefit from working with a
consultant/coach to get you started and to help you over the hurdles.

Allan

Allan,

First of all, thanks so much - you're a star.

That sounds workable. I already have 5 seperate queries,
and I use a filter (actioned in VBA if required) to
specify the hospital. I have no idea what DAO/ADO
recordsets are though - but I'll look into it and learn -
know any good sources?

I think it would help to explain the grouping levels:
1st - Originating Hospital
2nd - Referral Status (or referral stage depending on
report)
3rd - Referral key (since 1 patient may have >1
appointment)

A couple of worries/doubts in it as a result...

The Referral Status grouping level effectively converts
the field to subtitles in each of the hospital reports.
some of the detail fields and Referral Status header
labels are calculated textboxes that show different data
depending on the Referral Status (subtitle).

Would I be able to recreate this? There would also need
to be a blank row forced between each grouping stage
within the sheet - the detail size is variable so it would
have to be built into the process - is it possible to run
Excel VBA on the workbook from within the Access code?

Secondly due to the referral key grouping, I set "Hide
duplicates" to yes on many fields in the report. Can this
copy across?

If we can sort out the above queries - it sounds great,
and I think I could manage it! Would even remove the need
for email automation!

IF we can't sort out the queries above is it feasible to
have 5 Excel workbooks set up for each report and filter
the different Referral Statuses into seperate sheets -
wouldn't fix the hide duplicates problem though... and
would cause further work - would rather be able to solve
the queries I think!

Thanks loads Allan.

Basil

-----Original Message-----
Basil,

The key question is whether you can represent the data from your Access
reports using queries. If so, the TransferSpreadsheet method should work.
The key to making the process more efficient is using VBA, in particular its
looping capability.

See if this makes sense for you.
- Create a template in Excel that has 5 sheets, one for each report type
- In Access, create 5 queries (or modify existing), one for each report.
For the WHERE clause of each query, use something like "WHERE HospitalID =
Forms!frmSelect!cboHospitalID " (cboHospitalID being a combo box on the form
frmSelect)
- create a VBA procedure that loops through all the hospitals using a DAO or
ADO recordset (e.g. recordset name = rsHosp)
- within the loop, you can create an Excel file for each hopital by doing
the following:
1) set the value of cboHospitalID = rsHosp!HospitalID
2) reset a variable that contains the target Excel filename e.g...\Excel
Files\[HospitalID].xls
3) use the Kill statement (see Visual Basic for Applications help in MS
Access) to delete any prior version of the target file
4) copy the source Excel template file to the target file using the FileCopy
statement
5) use the TransferSpreadsheet method to transfer data from each of your 5
queries (which will be filtered for the current hospital) to the appropriate
sheet in the target Excel file
6) e-mail the Excel file
7) move to the next hospital

Let me know if this sounds workable.

Allan

--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184

Thanks Allan,

This sounds like the right kind of track I need to be on.

I am aware of how you can create a pivot form which can be
opened up in Excel edit mode - this seems to hold the
Excel file within the database (although I could be
wrong) - it's not what I want but holding the excel file
internally might be useful to me. I'll give you more
detail:

I work for a hospital. There are 30 hospitals that send
patients to us for treatment (these are referrals). When
with us, I have allocated information into different
groups -
1. patients who we have only got the referral for
2. patients who we have seen at a clinic
3. patients who we have have agreed to admit (booked list)
4. patients who have been admitted
5. patients who we see at a follow-up clinic

Within these groups referrals can have a different status
(there is a referral key which carries across all
activity) - eg. point 2 can be patients sent back to the
original hospital/patients on hold/Active patients
(waiting for appointment or outcome).

I have created 5 reports in line with the above, and
introduced a grouping level to show the referral status
(forces new page). (There is also a higher grouping for
hospital of origin).

Thanks for bearing with me on this...

I have a form that enables you to view all reports or a
selected report - either showing all hospitals within them
or filtered by hospital.

Now is where I need help... they want to see the report in
Excel - ideally 1 workbook with 5 sheets (I'd remove the
page break for the status grouping - so all statuses will
be on the same sheet). But 5 workbooks would be fine.
Now I would need to run through a process as follows:

-Select 1st hospital in table
-run all 5 reports (shoved in Excel if poss) (if have data)
-attach them to an email
-send to email (held in the email fild of the hospital
table)
-Select 2nd hospital...

This would be done weekly. As you can imagine, the
reports would be referenced on many occasions - and I'd
rather not have 150 (30hospitals*5reports) Excel templates.

Will the transferspreadsheet allow me to make this work,
how?

I really appreciate you getting back to me on this.

Many thanks,

Basil

-----Original Message-----
Mike,

Without knowing more about your data and Excel reporting
needs, its hard to
give a definitive answer. The easiest approach, and
hopefully the one you
can use, is to use DoCmd.TransferSpreadsheet (see
TransferSpreadsheet Method
under MS Access help). This command allows you to export
data from tables
or queries to Excel. Since you can use query datasets,
the data you export
to Excel can be data joined from multiple tables,
calculated data, summary
data, and/or filtered data.. The Excel sheet that you
transfer to can be
setup as a template with the additional formulas, charts,
etc already in
place. Macros or VBA code can be used to automate the
data transfer to
multiple Excel files.

If this approach sounds workable and you need more
direction, let me know.
I can also suggest other alternatives if this does not
sound viable. The
automation of e-mails, if needed, shouldn't be too
difficult either.

Allan
--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184


message
Hello,

I have moved all my work from Excel to Access coz Excel
couldn't handle the amounts of data and multiple tables.
Nobody who I produce reports for can handle Access and
want their reports in Excel (so that they can manipulate
them within Excel) - rather than say, RTF or SNP.

The reports are a bit complicated so they don't work at
all nicely if exported to Excel, and lose a lot of data
(eg charts).

I need to learn how to work with Excel within Access -
DAO
or OLE or whichever it is. I'll maybe need to feed pre-
made templates or something. Can anyone offer tips or
sources of help?

I need to learn it pretty darn well in the next week.
(possible have to automate emails too, but hopefully
not)

Thanks all.

Mike


.

.

 

[Mike]

Conditional formatting... spot on, didn't think of that.
I guess issues such as this are things that I can sort out
when I get to work on it. I think you've explained really
well that it is worth pursuing.

Regarding your thoughts on the queries I raised:

1. (Grouping levels stuff) - I currently use the switch
function to set what the field will show and what the
label will be (the label is actually a textbox) with the
criteria being the referral status. What you've suggested
makes sense for the detail in the column. But I don't
know how I could get the Status to show as subheadings
rather than another column, and create a new header
beneath each subheading showing the calculated label
(dependant on status). If I'm right on this, I think I've
got a workable solution - tell me if it can work/what you
think:
In Excel workbook:
-Create 3 rows at the top showing the 3 different header
possibilities (dependant on status).
-Have the status in the final column and hide it. (sort
data by status and then referral key to enable hide
duplicates to work - con.formatting will also have to look
at equal referral keys in the 2 rows)
-Put a filter on the hidden status column
-Create a combo box for patient to select status
(subheading)
-action from combo the relevant header row to be visible
and the status column appropriately filtered.

- This would have to be created for every sheet.

2. The above wouldn't require the breaks... otherwise I've
done similar things before in Excel - it is getting the
status as a sub-heading that I don't know.

3. I might try looking through internet/helpfiles until I
hit a hurdle - I've never actually had a coach/training or
read an IT book.

You've helped me so much, I really appreciate all the time
you must have spent and the advice that you have given.
I'm going to get to work on it. I'd value your thoughts
on my suggestion in 1. (If what I've written makes any
sense!).

Thanks Allan, there's a beer (or 2) waiting for you in
London!
I'll probably be back on for further problems...
especially in the emailing bits!

Basil

-----Original Message-----
Ah! This is where the superiority of Access over Excel as a reporting tool
becomes evident. But this still may be doable. To handle the suppression
of duplicates in Excel, you need to use conditional formatting. See
http://www.contextures.com/xlCondFormat03.html#Duplicate for an example of
how to do this. You could apply the conditional formatting in advance to
the appropriate columns in your spreadsheet template.

Re some of your other questions:

[ The Referral Status grouping level effectively converts the field to
subtitles in each of the hospital reports.
some of the detail fields and Referral Status header labels are calculated
textboxes that show different data
depending on the Referral Status (subtitle).]
You could handle this by formatting the column using the Switch function
within your query. (See Visual Basic help if you are not familiar with the
Switch function. Access does a good job of hiding its availability)

[There would also need to be a blank row forced between each grouping stage
within the sheet - the detail size is variable so it would have to be built
into the process - is it possible to run Excel VBA on the workbook from
within the Access code?]
Yes, it is possible to use Excel VBA from Access. I'm not sure how you can
handle the multiple break levels, however. I have some ideas but would need
to see your report format before I could give you suggestions.

[I have no idea what DAO/ADO recordsets are though - but I'll look into it
and learn - know any good sources?]
Maybe it was just me, but years ago when I was teaching myself to work with
recordsets, I really struggled. And I was working from a book that was
highly recommended (written by Getz, Litwin and Reddick). If this is your
first time working with DAO, automation of Excel from within Access and
looping constructs, you would probably benefit from working with a
consultant/coach to get you started and to help you over the hurdles.

Allan

Allan,

First of all, thanks so much - you're a star.

That sounds workable. I already have 5 seperate queries,
and I use a filter (actioned in VBA if required) to
specify the hospital. I have no idea what DAO/ADO
recordsets are though - but I'll look into it and learn -
know any good sources?

I think it would help to explain the grouping levels:
1st - Originating Hospital
2nd - Referral Status (or referral stage depending on
report)
3rd - Referral key (since 1 patient may have >1
appointment)

A couple of worries/doubts in it as a result...

The Referral Status grouping level effectively converts
the field to subtitles in each of the hospital reports.
some of the detail fields and Referral Status header
labels are calculated textboxes that show different data
depending on the Referral Status (subtitle).

Would I be able to recreate this? There would also need
to be a blank row forced between each grouping stage
within the sheet - the detail size is variable so it would
have to be built into the process - is it possible to run
Excel VBA on the workbook from within the Access code?

Secondly due to the referral key grouping, I set "Hide
duplicates" to yes on many fields in the report. Can this
copy across?

If we can sort out the above queries - it sounds great,
and I think I could manage it! Would even remove the need
for email automation!

IF we can't sort out the queries above is it feasible to
have 5 Excel workbooks set up for each report and filter
the different Referral Statuses into seperate sheets -
wouldn't fix the hide duplicates problem though... and
would cause further work - would rather be able to solve
the queries I think!

Thanks loads Allan.

Basil

-----Original Message-----
Basil,

The key question is whether you can represent the data from your Access
reports using queries. If so, the TransferSpreadsheet method should work.
The key to making the process more efficient is using VBA, in particular its
looping capability.

See if this makes sense for you.
- Create a template in Excel that has 5 sheets, one

for
each report type

- In Access, create 5 queries (or modify existing),

one
for each report.

For the WHERE clause of each query, use something like "WHERE HospitalID =
Forms!frmSelect!cboHospitalID " (cboHospitalID being a combo box on the form
frmSelect)
- create a VBA procedure that loops through all the hospitals using a DAO or
ADO recordset (e.g. recordset name = rsHosp)
- within the loop, you can create an Excel file for

each
hopital by doing

the following:
1) set the value of cboHospitalID = rsHosp!HospitalID
2) reset a variable that contains the target Excel filename e.g...\Excel
Files\[HospitalID].xls
3) use the Kill statement (see Visual Basic for Applications help in MS
Access) to delete any prior version of the target file
4) copy the source Excel template file to the target

file
using the FileCopy

statement
5) use the TransferSpreadsheet method to transfer data from each of your 5
queries (which will be filtered for the current

hospital)
to the appropriate

sheet in the target Excel file
6) e-mail the Excel file
7) move to the next hospital

Let me know if this sounds workable.

Allan

--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184


Thanks Allan,

This sounds like the right kind of track I need to be on.

I am aware of how you can create a pivot form which

can
be

opened up in Excel edit mode - this seems to hold the
Excel file within the database (although I could be
wrong) - it's not what I want but holding the excel file
internally might be useful to me. I'll give you more
detail:

I work for a hospital. There are 30 hospitals that send
patients to us for treatment (these are referrals). When
with us, I have allocated information into different
groups -
1. patients who we have only got the referral for
2. patients who we have seen at a clinic
3. patients who we have have agreed to admit (booked list)
4. patients who have been admitted
5. patients who we see at a follow-up clinic

Within these groups referrals can have a different status
(there is a referral key which carries across all
activity) - eg. point 2 can be patients sent back to the
original hospital/patients on hold/Active patients
(waiting for appointment or outcome).

I have created 5 reports in line with the above, and
introduced a grouping level to show the referral status
(forces new page). (There is also a higher grouping for
hospital of origin).

Thanks for bearing with me on this...

I have a form that enables you to view all reports or a
selected report - either showing all hospitals within them
or filtered by hospital.

Now is where I need help... they want to see the

report
in

Excel - ideally 1 workbook with 5 sheets (I'd remove the
page break for the status grouping - so all statuses will
be on the same sheet). But 5 workbooks would be fine.
Now I would need to run through a process as follows:

-Select 1st hospital in table
-run all 5 reports (shoved in Excel if poss) (if have data)
-attach them to an email
-send to email (held in the email fild of the hospital
table)
-Select 2nd hospital...

This would be done weekly. As you can imagine, the
reports would be referenced on many occasions - and I'd
rather not have 150 (30hospitals*5reports) Excel templates.

Will the transferspreadsheet allow me to make this work,
how?

I really appreciate you getting back to me on this.

Many thanks,

Basil

-----Original Message-----
Mike,

Without knowing more about your data and Excel reporting
needs, its hard to
give a definitive answer. The easiest approach, and
hopefully the one you
can use, is to use DoCmd.TransferSpreadsheet (see
TransferSpreadsheet Method
under MS Access help). This command allows you to export
data from tables
or queries to Excel. Since you can use query datasets,
the data you export
to Excel can be data joined from multiple tables,
calculated data, summary
data, and/or filtered data.. The Excel sheet that you
transfer to can be
setup as a template with the additional formulas, charts,
etc already in
place. Macros or VBA code can be used to automate the
data transfer to
multiple Excel files.

If this approach sounds workable and you need more
direction, let me know.
I can also suggest other alternatives if this does not
sound viable. The
automation of e-mails, if needed, shouldn't be too
difficult either.

Allan
--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184


"Mike T." <[email protected]>

wrote
in

message
Hello,

I have moved all my work from Excel to Access coz Excel
couldn't handle the amounts of data and multiple tables.
Nobody who I produce reports for can handle Access and
want their reports in Excel (so that they can manipulate
them within Excel) - rather than say, RTF or SNP.

The reports are a bit complicated so they don't

work
at

all nicely if exported to Excel, and lose a lot of data
(eg charts).

I need to learn how to work with Excel within Access -
DAO
or OLE or whichever it is. I'll maybe need to feed pre-
made templates or something. Can anyone offer

tips
or

sources of help?

I need to learn it pretty darn well in the next week.
(possible have to automate emails too, but hopefully
not)

Thanks all.

Mike


.



.

.

 

[Allan Thompson]

Thanks for your kind words. I'm glad to help when it is appreciated (but
tend to get annoyed when there is not even an acknowledgement).

Re the grouping question:
1)My thought was that you include a column in the Access query that would be
your header value (dependant on status). If you do this, it seems that you
would only need one row/cell on your spreadsheet that would show the
appropriate value based on your filter. Am I missing something?
2)Can you give me a sample of what you visualize for the heading, subheading
and detail ?
3)Are there any group footers?

Allan

Conditional formatting... spot on, didn't think of that.
I guess issues such as this are things that I can sort out
when I get to work on it. I think you've explained really
well that it is worth pursuing.

Regarding your thoughts on the queries I raised:

1. (Grouping levels stuff) - I currently use the switch
function to set what the field will show and what the
label will be (the label is actually a textbox) with the
criteria being the referral status. What you've suggested
makes sense for the detail in the column. But I don't
know how I could get the Status to show as subheadings
rather than another column, and create a new header
beneath each subheading showing the calculated label
(dependant on status). If I'm right on this, I think I've
got a workable solution - tell me if it can work/what you
think:
In Excel workbook:
-Create 3 rows at the top showing the 3 different header
possibilities (dependant on status).
-Have the status in the final column and hide it. (sort
data by status and then referral key to enable hide
duplicates to work - con.formatting will also have to look
at equal referral keys in the 2 rows)
-Put a filter on the hidden status column
-Create a combo box for patient to select status
(subheading)
-action from combo the relevant header row to be visible
and the status column appropriately filtered.

- This would have to be created for every sheet.

2. The above wouldn't require the breaks... otherwise I've
done similar things before in Excel - it is getting the
status as a sub-heading that I don't know.

3. I might try looking through internet/helpfiles until I
hit a hurdle - I've never actually had a coach/training or
read an IT book.

You've helped me so much, I really appreciate all the time
you must have spent and the advice that you have given.
I'm going to get to work on it. I'd value your thoughts
on my suggestion in 1. (If what I've written makes any
sense!).

Thanks Allan, there's a beer (or 2) waiting for you in
London!
I'll probably be back on for further problems...
especially in the emailing bits!

Basil

-----Original Message-----
Ah! This is where the superiority of Access over Excel as a reporting tool
becomes evident. But this still may be doable. To handle the suppression
of duplicates in Excel, you need to use conditional formatting. See
http://www.contextures.com/xlCondFormat03.html#Duplicate for an example of
how to do this. You could apply the conditional formatting in advance to
the appropriate columns in your spreadsheet template.

Re some of your other questions:

[ The Referral Status grouping level effectively converts the field to
subtitles in each of the hospital reports.
some of the detail fields and Referral Status header labels are calculated
textboxes that show different data
depending on the Referral Status (subtitle).]
You could handle this by formatting the column using the Switch function
within your query. (See Visual Basic help if you are not familiar with the
Switch function. Access does a good job of hiding its availability)

[There would also need to be a blank row forced between each grouping stage
within the sheet - the detail size is variable so it would have to be built
into the process - is it possible to run Excel VBA on the workbook from
within the Access code?]
Yes, it is possible to use Excel VBA from Access. I'm not sure how you can
handle the multiple break levels, however. I have some ideas but would need
to see your report format before I could give you suggestions.

[I have no idea what DAO/ADO recordsets are though - but I'll look into it
and learn - know any good sources?]
Maybe it was just me, but years ago when I was teaching myself to work with
recordsets, I really struggled. And I was working from a book that was
highly recommended (written by Getz, Litwin and Reddick). If this is your
first time working with DAO, automation of Excel from within Access and
looping constructs, you would probably benefit from working with a
consultant/coach to get you started and to help you over the hurdles.

Allan

Allan,

First of all, thanks so much - you're a star.

That sounds workable. I already have 5 seperate queries,
and I use a filter (actioned in VBA if required) to
specify the hospital. I have no idea what DAO/ADO
recordsets are though - but I'll look into it and learn -
know any good sources?

I think it would help to explain the grouping levels:
1st - Originating Hospital
2nd - Referral Status (or referral stage depending on
report)
3rd - Referral key (since 1 patient may have >1
appointment)

A couple of worries/doubts in it as a result...

The Referral Status grouping level effectively converts
the field to subtitles in each of the hospital reports.
some of the detail fields and Referral Status header
labels are calculated textboxes that show different data
depending on the Referral Status (subtitle).

Would I be able to recreate this? There would also need
to be a blank row forced between each grouping stage
within the sheet - the detail size is variable so it would
have to be built into the process - is it possible to run
Excel VBA on the workbook from within the Access code?

Secondly due to the referral key grouping, I set "Hide
duplicates" to yes on many fields in the report. Can this
copy across?

If we can sort out the above queries - it sounds great,
and I think I could manage it! Would even remove the need
for email automation!

IF we can't sort out the queries above is it feasible to
have 5 Excel workbooks set up for each report and filter
the different Referral Statuses into seperate sheets -
wouldn't fix the hide duplicates problem though... and
would cause further work - would rather be able to solve
the queries I think!

Thanks loads Allan.

Basil

-----Original Message-----
Basil,

The key question is whether you can represent the data
from your Access
reports using queries. If so, the TransferSpreadsheet
method should work.
The key to making the process more efficient is using
VBA, in particular its
looping capability.

See if this makes sense for you.
- Create a template in Excel that has 5 sheets, one for
each report type
- In Access, create 5 queries (or modify existing), one
for each report.
For the WHERE clause of each query, use something
like "WHERE HospitalID =
Forms!frmSelect!cboHospitalID " (cboHospitalID being a
combo box on the form
frmSelect)
- create a VBA procedure that loops through all the
hospitals using a DAO or
ADO recordset (e.g. recordset name = rsHosp)
- within the loop, you can create an Excel file for each
hopital by doing
the following:
1) set the value of cboHospitalID = rsHosp!HospitalID
2) reset a variable that contains the target Excel
filename e.g...\Excel
Files\[HospitalID].xls
3) use the Kill statement (see Visual Basic for
Applications help in MS
Access) to delete any prior version of the target file
4) copy the source Excel template file to the target file
using the FileCopy
statement
5) use the TransferSpreadsheet method to transfer data
from each of your 5
queries (which will be filtered for the current hospital)
to the appropriate
sheet in the target Excel file
6) e-mail the Excel file
7) move to the next hospital

Let me know if this sounds workable.

Allan

--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184


message
Thanks Allan,

This sounds like the right kind of track I need to be
on.

I am aware of how you can create a pivot form which can
be
opened up in Excel edit mode - this seems to hold the
Excel file within the database (although I could be
wrong) - it's not what I want but holding the excel file
internally might be useful to me. I'll give you more
detail:

I work for a hospital. There are 30 hospitals that send
patients to us for treatment (these are referrals). When
with us, I have allocated information into different
groups -
1. patients who we have only got the referral for
2. patients who we have seen at a clinic
3. patients who we have have agreed to admit (booked
list)
4. patients who have been admitted
5. patients who we see at a follow-up clinic

Within these groups referrals can have a different
status
(there is a referral key which carries across all
activity) - eg. point 2 can be patients sent back to the
original hospital/patients on hold/Active patients
(waiting for appointment or outcome).

I have created 5 reports in line with the above, and
introduced a grouping level to show the referral status
(forces new page). (There is also a higher grouping for
hospital of origin).

Thanks for bearing with me on this...

I have a form that enables you to view all reports or a
selected report - either showing all hospitals within
them
or filtered by hospital.

Now is where I need help... they want to see the report
in
Excel - ideally 1 workbook with 5 sheets (I'd remove the
page break for the status grouping - so all statuses
will
be on the same sheet). But 5 workbooks would be fine.
Now I would need to run through a process as follows:

-Select 1st hospital in table
-run all 5 reports (shoved in Excel if poss) (if have
data)
-attach them to an email
-send to email (held in the email fild of the hospital
table)
-Select 2nd hospital...

This would be done weekly. As you can imagine, the
reports would be referenced on many occasions - and I'd
rather not have 150 (30hospitals*5reports) Excel
templates.

Will the transferspreadsheet allow me to make this work,
how?

I really appreciate you getting back to me on this.

Many thanks,

Basil

-----Original Message-----
Mike,

Without knowing more about your data and Excel
reporting
needs, its hard to
give a definitive answer. The easiest approach, and
hopefully the one you
can use, is to use DoCmd.TransferSpreadsheet (see
TransferSpreadsheet Method
under MS Access help). This command allows you to
export
data from tables
or queries to Excel. Since you can use query datasets,
the data you export
to Excel can be data joined from multiple tables,
calculated data, summary
data, and/or filtered data.. The Excel sheet that you
transfer to can be
setup as a template with the additional formulas,
charts,
etc already in
place. Macros or VBA code can be used to automate the
data transfer to
multiple Excel files.

If this approach sounds workable and you need more
direction, let me know.
I can also suggest other alternatives if this does not
sound viable. The
automation of e-mails, if needed, shouldn't be too
difficult either.

Allan
--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting
Systems
www.fieldscope.com
860.242.4184


in
message
Hello,

I have moved all my work from Excel to Access coz
Excel
couldn't handle the amounts of data and multiple
tables.
Nobody who I produce reports for can handle Access
and
want their reports in Excel (so that they can
manipulate
them within Excel) - rather than say, RTF or SNP.

The reports are a bit complicated so they don't work
at
all nicely if exported to Excel, and lose a lot of
data
(eg charts).

I need to learn how to work with Excel within
Access -
DAO
or OLE or whichever it is. I'll maybe need to feed
pre-
made templates or something. Can anyone offer tips
or
sources of help?

I need to learn it pretty darn well in the next week.
(possible have to automate emails too, but hopefully
not)

Thanks all.

Mike


.



.

.

 

[Mike]

Thanks Allan.

Here is a (very) simplified example of what the report
looks like (I probably should have offered this a long
time ago!):
This is to show what grouping level each item is in
PH - Page header
HH - Originating hospital header
SH - Referral Status header
D - Detail
RF - Referral Key footer
here is an example - stuff in [] is variable:

PH: RPH Hospital logo

HHatients at Pre-Operative Clinic
Referring Trust: [Ealing Hosp]

SH:[Active Referrals] - (this is the status)
Patient Appt Date [Clinic] [Spec] - (labels)

D: JSmith 1/1/2004 Dr Jones Plastic surgery
..... (other patients)

SH:[On Hold Referrals]
Patient Appt Date [Clinic] [Hold reason] [hold date]

D: TClancy 12/12/2003 Pre-As1 Scan results 1/1/2004
.....

SH:[Returned to referrer since 12/12/2003]
Patient Appt Date [Outcome] [reas close] [close date]

D: TShaw 1/1/2004 Sent back BloodPressure 1/1/2004
.....

I hope you can make sense of that! there is actually a
footer on referral key directly after the detail section
which simply has a thin line in it (to keep patients with
multiple appointments all between 2 lines). A new page is
forced after the Status group footer.

1) I understand how you have (a) columns in the Access
query which shows the appropriate headings for the
different columns for that record. I also understand how
(b) the status can be put into a column in the query. But
what I don't understand is how you can (for (a)) get the
relevant heading columns to be shown in Excel as the
headers of the other columns, and (for b) how you can make
the column a 'subtitle' above the first column. I think
I'm confused. I envisaged having the labels preloaded and
a combobox actioning a filter on the status column (which
would also action the correct set of labels to be
displayed). Sorry if I'm getting you confused too!

2)and3)above

PS I grouped on referral key rather than patient simply
because patients can be active for more than 1 referral at
a time.

I'm reading on DAO now - doesn't seem too bad (early days
though!). Gonna have to figure out how I can shove the
data into say the 5th row of a particular sheet in a
workbook - can't see how to do this through
transferspreadsheet.

All the people on this website do an amazing job. I often
wonder why you all do it - do you get paid by microsoft?

Many thanks,

Basil

-----Original Message-----
Thanks for your kind words. I'm glad to help when it is appreciated (but
tend to get annoyed when there is not even an acknowledgement).

Re the grouping question:
1)My thought was that you include a column in the Access query that would be
your header value (dependant on status). If you do this, it seems that you
would only need one row/cell on your spreadsheet that would show the
appropriate value based on your filter. Am I missing something?
2)Can you give me a sample of what you visualize for the heading, subheading
and detail ?
3)Are there any group footers?

Allan

Conditional formatting... spot on, didn't think of that.
I guess issues such as this are things that I can sort out
when I get to work on it. I think you've explained really
well that it is worth pursuing.

Regarding your thoughts on the queries I raised:

1. (Grouping levels stuff) - I currently use the switch
function to set what the field will show and what the
label will be (the label is actually a textbox) with the
criteria being the referral status. What you've suggested
makes sense for the detail in the column. But I don't
know how I could get the Status to show as subheadings
rather than another column, and create a new header
beneath each subheading showing the calculated label
(dependant on status). If I'm right on this, I think I've
got a workable solution - tell me if it can work/what you
think:
In Excel workbook:
-Create 3 rows at the top showing the 3 different header
possibilities (dependant on status).
-Have the status in the final column and hide it. (sort
data by status and then referral key to enable hide
duplicates to work - con.formatting will also have to look
at equal referral keys in the 2 rows)
-Put a filter on the hidden status column
-Create a combo box for patient to select status
(subheading)
-action from combo the relevant header row to be visible
and the status column appropriately filtered.

- This would have to be created for every sheet.

2. The above wouldn't require the breaks... otherwise I've
done similar things before in Excel - it is getting the
status as a sub-heading that I don't know.

3. I might try looking through internet/helpfiles until I
hit a hurdle - I've never actually had a coach/training or
read an IT book.

You've helped me so much, I really appreciate all the time
you must have spent and the advice that you have given.
I'm going to get to work on it. I'd value your thoughts
on my suggestion in 1. (If what I've written makes any
sense!).

Thanks Allan, there's a beer (or 2) waiting for you in
London!
I'll probably be back on for further problems...
especially in the emailing bits!

Basil

-----Original Message-----
Ah! This is where the superiority of Access over Excel as a reporting tool
becomes evident. But this still may be doable. To handle the suppression
of duplicates in Excel, you need to use conditional

formatting. See
http://www.contextures.com/xlCondFormat03.html#Duplicate
for an example of

how to do this. You could apply the conditional formatting in advance to
the appropriate columns in your spreadsheet template.

Re some of your other questions:

[ The Referral Status grouping level effectively

converts
the field to

subtitles in each of the hospital reports.
some of the detail fields and Referral Status header labels are calculated
textboxes that show different data
depending on the Referral Status (subtitle).]
You could handle this by formatting the column using

the
Switch function

within your query. (See Visual Basic help if you are not familiar with the
Switch function. Access does a good job of hiding its availability)

[There would also need to be a blank row forced between each grouping stage
within the sheet - the detail size is variable so it would have to be built
into the process - is it possible to run Excel VBA on the workbook from
within the Access code?]
Yes, it is possible to use Excel VBA from Access. I'm not sure how you can
handle the multiple break levels, however. I have some ideas but would need
to see your report format before I could give you suggestions.

[I have no idea what DAO/ADO recordsets are though -

but
I'll look into it

and learn - know any good sources?]
Maybe it was just me, but years ago when I was teaching myself to work with
recordsets, I really struggled. And I was working from

a
book that was

highly recommended (written by Getz, Litwin and Reddick). If this is your
first time working with DAO, automation of Excel from within Access and
looping constructs, you would probably benefit from working with a
consultant/coach to get you started and to help you

over
the hurdles.

Allan

Allan,

First of all, thanks so much - you're a star.

That sounds workable. I already have 5 seperate queries,
and I use a filter (actioned in VBA if required) to
specify the hospital. I have no idea what DAO/ADO
recordsets are though - but I'll look into it and learn -
know any good sources?

I think it would help to explain the grouping levels:
1st - Originating Hospital
2nd - Referral Status (or referral stage depending on
report)
3rd - Referral key (since 1 patient may have >1
appointment)

A couple of worries/doubts in it as a result...

The Referral Status grouping level effectively converts
the field to subtitles in each of the hospital reports.
some of the detail fields and Referral Status header
labels are calculated textboxes that show different data
depending on the Referral Status (subtitle).

Would I be able to recreate this? There would also need
to be a blank row forced between each grouping stage
within the sheet - the detail size is variable so it would
have to be built into the process - is it possible to run
Excel VBA on the workbook from within the Access code?

Secondly due to the referral key grouping, I set "Hide
duplicates" to yes on many fields in the report. Can this
copy across?

If we can sort out the above queries - it sounds great,
and I think I could manage it! Would even remove the need
for email automation!

IF we can't sort out the queries above is it feasible to
have 5 Excel workbooks set up for each report and filter
the different Referral Statuses into seperate sheets -
wouldn't fix the hide duplicates problem though... and
would cause further work - would rather be able to solve
the queries I think!

Thanks loads Allan.

Basil

-----Original Message-----
Basil,

The key question is whether you can represent the data
from your Access
reports using queries. If so, the TransferSpreadsheet
method should work.
The key to making the process more efficient is using
VBA, in particular its
looping capability.

See if this makes sense for you.
- Create a template in Excel that has 5 sheets, one for
each report type
- In Access, create 5 queries (or modify existing), one
for each report.
For the WHERE clause of each query, use something
like "WHERE HospitalID =
Forms!frmSelect!cboHospitalID " (cboHospitalID being a
combo box on the form
frmSelect)
- create a VBA procedure that loops through all the
hospitals using a DAO or
ADO recordset (e.g. recordset name = rsHosp)
- within the loop, you can create an Excel file for each
hopital by doing
the following:
1) set the value of cboHospitalID = rsHosp! HospitalID
2) reset a variable that contains the target Excel
filename e.g...\Excel
Files\[HospitalID].xls
3) use the Kill statement (see Visual Basic for
Applications help in MS
Access) to delete any prior version of the target file
4) copy the source Excel template file to the target file
using the FileCopy
statement
5) use the TransferSpreadsheet method to transfer data
from each of your 5
queries (which will be filtered for the current hospital)
to the appropriate
sheet in the target Excel file
6) e-mail the Excel file
7) move to the next hospital

Let me know if this sounds workable.

Allan

--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184


message
Thanks Allan,

This sounds like the right kind of track I need to be
on.

I am aware of how you can create a pivot form

which
can

be
opened up in Excel edit mode - this seems to hold the
Excel file within the database (although I could be
wrong) - it's not what I want but holding the

excel
file

internally might be useful to me. I'll give you more
detail:

I work for a hospital. There are 30 hospitals

that
send

patients to us for treatment (these are

referrals).
When

with us, I have allocated information into different
groups -
1. patients who we have only got the referral for
2. patients who we have seen at a clinic
3. patients who we have have agreed to admit (booked
list)
4. patients who have been admitted
5. patients who we see at a follow-up clinic

Within these groups referrals can have a different
status
(there is a referral key which carries across all
activity) - eg. point 2 can be patients sent back

to
the

original hospital/patients on hold/Active patients
(waiting for appointment or outcome).

I have created 5 reports in line with the above, and
introduced a grouping level to show the referral status
(forces new page). (There is also a higher

grouping
for

hospital of origin).

Thanks for bearing with me on this...

I have a form that enables you to view all reports or a
selected report - either showing all hospitals within
them
or filtered by hospital.

Now is where I need help... they want to see the report
in
Excel - ideally 1 workbook with 5 sheets (I'd

remove
the

page break for the status grouping - so all statuses
will
be on the same sheet). But 5 workbooks would be fine.
Now I would need to run through a process as follows:

-Select 1st hospital in table
-run all 5 reports (shoved in Excel if poss) (if have
data)
-attach them to an email
-send to email (held in the email fild of the hospital
table)
-Select 2nd hospital...

This would be done weekly. As you can imagine, the
reports would be referenced on many occasions -

and
I'd

rather not have 150 (30hospitals*5reports) Excel
templates.

Will the transferspreadsheet allow me to make this work,
how?

I really appreciate you getting back to me on this.

Many thanks,

Basil

-----Original Message-----
Mike,

Without knowing more about your data and Excel
reporting
needs, its hard to
give a definitive answer. The easiest approach, and
hopefully the one you
can use, is to use DoCmd.TransferSpreadsheet (see
TransferSpreadsheet Method
under MS Access help). This command allows you to
export
data from tables
or queries to Excel. Since you can use query datasets,
the data you export
to Excel can be data joined from multiple tables,
calculated data, summary
data, and/or filtered data.. The Excel sheet

that
you

transfer to can be
setup as a template with the additional formulas,
charts,
etc already in
place. Macros or VBA code can be used to

automate
the

data transfer to
multiple Excel files.

If this approach sounds workable and you need more
direction, let me know.
I can also suggest other alternatives if this

does
not

sound viable. The
automation of e-mails, if needed, shouldn't be too
difficult either.

Allan
--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting
Systems
www.fieldscope.com
860.242.4184


in
message
Hello,

I have moved all my work from Excel to Access coz
Excel
couldn't handle the amounts of data and multiple
tables.
Nobody who I produce reports for can handle Access
and
want their reports in Excel (so that they can
manipulate
them within Excel) - rather than say, RTF or SNP.

The reports are a bit complicated so they don't work
at
all nicely if exported to Excel, and lose a lot of
data
(eg charts).

I need to learn how to work with Excel within
Access -
DAO
or OLE or whichever it is. I'll maybe need to feed
pre-
made templates or something. Can anyone offer tips
or
sources of help?

I need to learn it pretty darn well in the next week.
(possible have to automate emails too, but hopefully
not)

Thanks all.

Mike


.



.



.

.

 

[Mike]

Hi again!

The last post I put up has a bit more detail that you
kindly asked for, so I'd look at that first.

The reason for this second post is that I need another
shove on the Excel side of things. I've figured out
creating a recordset for the hospitals in Access... I'm
now not sure about the methodology of working with the
Excel file - i.e. how to specify which sheet/row the data
should be exported to. Do I use a combination of VBA in
the Access form followed by VBA created in Excel
workbook_open? Does the email part come into the
Access/Excel code - etc. It's not easy to find a simple
explaination for this on the net, and Northwind doesn't
help.

Thanks again Allan.

-----Original Message-----
Thanks for your kind words. I'm glad to help when it is appreciated (but
tend to get annoyed when there is not even an acknowledgement).

Re the grouping question:
1)My thought was that you include a column in the Access query that would be
your header value (dependant on status). If you do this, it seems that you
would only need one row/cell on your spreadsheet that would show the
appropriate value based on your filter. Am I missing something?
2)Can you give me a sample of what you visualize for the heading, subheading
and detail ?
3)Are there any group footers?

Allan

Conditional formatting... spot on, didn't think of that.
I guess issues such as this are things that I can sort out
when I get to work on it. I think you've explained really
well that it is worth pursuing.

Regarding your thoughts on the queries I raised:

1. (Grouping levels stuff) - I currently use the switch
function to set what the field will show and what the
label will be (the label is actually a textbox) with the
criteria being the referral status. What you've suggested
makes sense for the detail in the column. But I don't
know how I could get the Status to show as subheadings
rather than another column, and create a new header
beneath each subheading showing the calculated label
(dependant on status). If I'm right on this, I think I've
got a workable solution - tell me if it can work/what you
think:
In Excel workbook:
-Create 3 rows at the top showing the 3 different header
possibilities (dependant on status).
-Have the status in the final column and hide it. (sort
data by status and then referral key to enable hide
duplicates to work - con.formatting will also have to look
at equal referral keys in the 2 rows)
-Put a filter on the hidden status column
-Create a combo box for patient to select status
(subheading)
-action from combo the relevant header row to be visible
and the status column appropriately filtered.

- This would have to be created for every sheet.

2. The above wouldn't require the breaks... otherwise I've
done similar things before in Excel - it is getting the
status as a sub-heading that I don't know.

3. I might try looking through internet/helpfiles until I
hit a hurdle - I've never actually had a coach/training or
read an IT book.

You've helped me so much, I really appreciate all the time
you must have spent and the advice that you have given.
I'm going to get to work on it. I'd value your thoughts
on my suggestion in 1. (If what I've written makes any
sense!).

Thanks Allan, there's a beer (or 2) waiting for you in
London!
I'll probably be back on for further problems...
especially in the emailing bits!

Basil

-----Original Message-----
Ah! This is where the superiority of Access over Excel as a reporting tool
becomes evident. But this still may be doable. To handle the suppression
of duplicates in Excel, you need to use conditional

formatting. See
http://www.contextures.com/xlCondFormat03.html#Duplicate
for an example of

how to do this. You could apply the conditional formatting in advance to
the appropriate columns in your spreadsheet template.

Re some of your other questions:

[ The Referral Status grouping level effectively

converts
the field to

subtitles in each of the hospital reports.
some of the detail fields and Referral Status header labels are calculated
textboxes that show different data
depending on the Referral Status (subtitle).]
You could handle this by formatting the column using

the
Switch function

within your query. (See Visual Basic help if you are not familiar with the
Switch function. Access does a good job of hiding its availability)

[There would also need to be a blank row forced between each grouping stage
within the sheet - the detail size is variable so it would have to be built
into the process - is it possible to run Excel VBA on the workbook from
within the Access code?]
Yes, it is possible to use Excel VBA from Access. I'm not sure how you can
handle the multiple break levels, however. I have some ideas but would need
to see your report format before I could give you suggestions.

[I have no idea what DAO/ADO recordsets are though -

but
I'll look into it

and learn - know any good sources?]
Maybe it was just me, but years ago when I was teaching myself to work with
recordsets, I really struggled. And I was working from

a
book that was

highly recommended (written by Getz, Litwin and Reddick). If this is your
first time working with DAO, automation of Excel from within Access and
looping constructs, you would probably benefit from working with a
consultant/coach to get you started and to help you

over
the hurdles.

Allan

Allan,

First of all, thanks so much - you're a star.

That sounds workable. I already have 5 seperate queries,
and I use a filter (actioned in VBA if required) to
specify the hospital. I have no idea what DAO/ADO
recordsets are though - but I'll look into it and learn -
know any good sources?

I think it would help to explain the grouping levels:
1st - Originating Hospital
2nd - Referral Status (or referral stage depending on
report)
3rd - Referral key (since 1 patient may have >1
appointment)

A couple of worries/doubts in it as a result...

The Referral Status grouping level effectively converts
the field to subtitles in each of the hospital reports.
some of the detail fields and Referral Status header
labels are calculated textboxes that show different data
depending on the Referral Status (subtitle).

Would I be able to recreate this? There would also need
to be a blank row forced between each grouping stage
within the sheet - the detail size is variable so it would
have to be built into the process - is it possible to run
Excel VBA on the workbook from within the Access code?

Secondly due to the referral key grouping, I set "Hide
duplicates" to yes on many fields in the report. Can this
copy across?

If we can sort out the above queries - it sounds great,
and I think I could manage it! Would even remove the need
for email automation!

IF we can't sort out the queries above is it feasible to
have 5 Excel workbooks set up for each report and filter
the different Referral Statuses into seperate sheets -
wouldn't fix the hide duplicates problem though... and
would cause further work - would rather be able to solve
the queries I think!

Thanks loads Allan.

Basil

-----Original Message-----
Basil,

The key question is whether you can represent the data
from your Access
reports using queries. If so, the TransferSpreadsheet
method should work.
The key to making the process more efficient is using
VBA, in particular its
looping capability.

See if this makes sense for you.
- Create a template in Excel that has 5 sheets, one for
each report type
- In Access, create 5 queries (or modify existing), one
for each report.
For the WHERE clause of each query, use something
like "WHERE HospitalID =
Forms!frmSelect!cboHospitalID " (cboHospitalID being a
combo box on the form
frmSelect)
- create a VBA procedure that loops through all the
hospitals using a DAO or
ADO recordset (e.g. recordset name = rsHosp)
- within the loop, you can create an Excel file for each
hopital by doing
the following:
1) set the value of cboHospitalID = rsHosp! HospitalID
2) reset a variable that contains the target Excel
filename e.g...\Excel
Files\[HospitalID].xls
3) use the Kill statement (see Visual Basic for
Applications help in MS
Access) to delete any prior version of the target file
4) copy the source Excel template file to the target file
using the FileCopy
statement
5) use the TransferSpreadsheet method to transfer data
from each of your 5
queries (which will be filtered for the current hospital)
to the appropriate
sheet in the target Excel file
6) e-mail the Excel file
7) move to the next hospital

Let me know if this sounds workable.

Allan

--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184


message
Thanks Allan,

This sounds like the right kind of track I need to be
on.

I am aware of how you can create a pivot form

which
can

be
opened up in Excel edit mode - this seems to hold the
Excel file within the database (although I could be
wrong) - it's not what I want but holding the

excel
file

internally might be useful to me. I'll give you more
detail:

I work for a hospital. There are 30 hospitals

that
send

patients to us for treatment (these are

referrals).
When

with us, I have allocated information into different
groups -
1. patients who we have only got the referral for
2. patients who we have seen at a clinic
3. patients who we have have agreed to admit (booked
list)
4. patients who have been admitted
5. patients who we see at a follow-up clinic

Within these groups referrals can have a different
status
(there is a referral key which carries across all
activity) - eg. point 2 can be patients sent back

to
the

original hospital/patients on hold/Active patients
(waiting for appointment or outcome).

I have created 5 reports in line with the above, and
introduced a grouping level to show the referral status
(forces new page). (There is also a higher

grouping
for

hospital of origin).

Thanks for bearing with me on this...

I have a form that enables you to view all reports or a
selected report - either showing all hospitals within
them
or filtered by hospital.

Now is where I need help... they want to see the report
in
Excel - ideally 1 workbook with 5 sheets (I'd

remove
the

page break for the status grouping - so all statuses
will
be on the same sheet). But 5 workbooks would be fine.
Now I would need to run through a process as follows:

-Select 1st hospital in table
-run all 5 reports (shoved in Excel if poss) (if have
data)
-attach them to an email
-send to email (held in the email fild of the hospital
table)
-Select 2nd hospital...

This would be done weekly. As you can imagine, the
reports would be referenced on many occasions -

and
I'd

rather not have 150 (30hospitals*5reports) Excel
templates.

Will the transferspreadsheet allow me to make this work,
how?

I really appreciate you getting back to me on this.

Many thanks,

Basil

-----Original Message-----
Mike,

Without knowing more about your data and Excel
reporting
needs, its hard to
give a definitive answer. The easiest approach, and
hopefully the one you
can use, is to use DoCmd.TransferSpreadsheet (see
TransferSpreadsheet Method
under MS Access help). This command allows you to
export
data from tables
or queries to Excel. Since you can use query datasets,
the data you export
to Excel can be data joined from multiple tables,
calculated data, summary
data, and/or filtered data.. The Excel sheet

that
you

transfer to can be
setup as a template with the additional formulas,
charts,
etc already in
place. Macros or VBA code can be used to

automate
the

data transfer to
multiple Excel files.

If this approach sounds workable and you need more
direction, let me know.
I can also suggest other alternatives if this

does
not

sound viable. The
automation of e-mails, if needed, shouldn't be too
difficult either.

Allan
--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting
Systems
www.fieldscope.com
860.242.4184


in
message
Hello,

I have moved all my work from Excel to Access coz
Excel
couldn't handle the amounts of data and multiple
tables.
Nobody who I produce reports for can handle Access
and
want their reports in Excel (so that they can
manipulate
them within Excel) - rather than say, RTF or SNP.

The reports are a bit complicated so they don't work
at
all nicely if exported to Excel, and lose a lot of
data
(eg charts).

I need to learn how to work with Excel within
Access -
DAO
or OLE or whichever it is. I'll maybe need to feed
pre-
made templates or something. Can anyone offer tips
or
sources of help?

I need to learn it pretty darn well in the next week.
(possible have to automate emails too, but hopefully
not)

Thanks all.

Mike


.



.



.

.

 

[Allan Thompson]

Basil,

I'll try to respond to both of your last posts.

First, in response to this:
[All the people on this website do an amazing job. I often
wonder why you all do it - do you get paid by microsoft?]
I don't know about anyone else but I certainly don't get paid. I respond to
posts for a few reasons. One reason is I like to share what I have learned
and help people get to a solution with a little less pain. Newsgroup posts
by other people willing to share their knowledge have have been very helpful
to me and I feel that I should give back when I can. A second reason is
that I like solving a good puzzle. I tend to respond to the posts that I
know are somewhat challenging, not the ones that can be answered with a
one-line response. And finally, I'm not totally altruistic. As an
independent consultant, I'm always looking for opportunities for exposure.
You never know when someone will see something you wrote and contact you in
connection with a project. (It happens!)

Now for the technical stuff:

1) All of the VBA can be handled from Access. But you need to open Excel
and go to the VB Help there for info on the objects, methods, etc. available
in Excel.
2) Based on the report you outlined, the TransferSpreadsheet method probably
will not work for you. You will need to use the brute force method to walk
records in Access and copy the values to cells in Excel.
a) Open the Excel application by calling the following function

Public Function basExcelApp() As Boolean
Dim ExcelApp As Object
On Error Resume Next
basExcelApp = True
' see if Excel is loaded
Set ExcelApp = GetObject(, "Excel.application")
If Err <> 0 Then ' if not then load Excel
On Error GoTo err_bas
Set ExcelApp = CreateObject("Excel.application")
End If

exit_bas:
Exit Function

err_bas:
MsgBox Error$, vbCritical
basExcelApp = False
Resume exit_bas

End Function

b) You need to open one recordset to walk through the hospitals and one or
more recordsets (depending how you handle the different queries) to walk the
detail records in Access.

c)To create a Excel new file from the template open the workbook, use code
like the following:
'delete old copy of destination file [variables master and extfn
previously defined]

If Dir(extfn) Then
Kill extfn
End If
On Error GoTo err_gen
'copy template master to destination
FileCopy master, extfn
'Open newly created destination file
Workbooks.Open extfn, 0, False, , "password"
Set wbDest = Workbooks.Item(extWb) [wbDest is dimmed as a
Workbook]
'Unprotect destination sheet
wbDest.Sheets(1).Unprotect

d) To copy data from Access to Excel cells, you can do something like this:
'rsHosp is the Access recordset; the Cells arguments are row, then
column (13,2 = B13)
With wbDest.Sheets(1)
.Cells(12, 2) = rsHosp!ReferralStatus
.Cells(13, 4) = rsHosp!ReferralKey
..
End With

e) Because you will be walking rows in Excel, you will actually need to
use a variable for the row number in the above,
e.g. irow = irow + 1 'increment row
.cells(irow, 2) = rsHosp!ReferralStatus

That's kind of a rough sketch. Hopefully it gives you a start. There's
obviously a lot of detail to deal with in terms of creating your looping
logic and handling the 5 different queries and the spreadsheets that they
populate.

Good luck. Let me know how it's going.

Allan
--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184

Hi again!

The last post I put up has a bit more detail that you
kindly asked for, so I'd look at that first.

The reason for this second post is that I need another
shove on the Excel side of things. I've figured out
creating a recordset for the hospitals in Access... I'm
now not sure about the methodology of working with the
Excel file - i.e. how to specify which sheet/row the data
should be exported to. Do I use a combination of VBA in
the Access form followed by VBA created in Excel
workbook_open? Does the email part come into the
Access/Excel code - etc. It's not easy to find a simple
explaination for this on the net, and Northwind doesn't
help.

Thanks again Allan.

-----Original Message-----
Thanks for your kind words. I'm glad to help when it is appreciated (but
tend to get annoyed when there is not even an acknowledgement).

Re the grouping question:
1)My thought was that you include a column in the Access query that would be
your header value (dependant on status). If you do this, it seems that you
would only need one row/cell on your spreadsheet that would show the
appropriate value based on your filter. Am I missing something?
2)Can you give me a sample of what you visualize for the heading, subheading
and detail ?
3)Are there any group footers?

Allan

Conditional formatting... spot on, didn't think of that.
I guess issues such as this are things that I can sort out
when I get to work on it. I think you've explained really
well that it is worth pursuing.

Regarding your thoughts on the queries I raised:

1. (Grouping levels stuff) - I currently use the switch
function to set what the field will show and what the
label will be (the label is actually a textbox) with the
criteria being the referral status. What you've suggested
makes sense for the detail in the column. But I don't
know how I could get the Status to show as subheadings
rather than another column, and create a new header
beneath each subheading showing the calculated label
(dependant on status). If I'm right on this, I think I've
got a workable solution - tell me if it can work/what you
think:
In Excel workbook:
-Create 3 rows at the top showing the 3 different header
possibilities (dependant on status).
-Have the status in the final column and hide it. (sort
data by status and then referral key to enable hide
duplicates to work - con.formatting will also have to look
at equal referral keys in the 2 rows)
-Put a filter on the hidden status column
-Create a combo box for patient to select status
(subheading)
-action from combo the relevant header row to be visible
and the status column appropriately filtered.

- This would have to be created for every sheet.

2. The above wouldn't require the breaks... otherwise I've
done similar things before in Excel - it is getting the
status as a sub-heading that I don't know.

3. I might try looking through internet/helpfiles until I
hit a hurdle - I've never actually had a coach/training or
read an IT book.

You've helped me so much, I really appreciate all the time
you must have spent and the advice that you have given.
I'm going to get to work on it. I'd value your thoughts
on my suggestion in 1. (If what I've written makes any
sense!).

Thanks Allan, there's a beer (or 2) waiting for you in
London!
I'll probably be back on for further problems...
especially in the emailing bits!

Basil

-----Original Message-----
Ah! This is where the superiority of Access over Excel
as a reporting tool
becomes evident. But this still may be doable. To
handle the suppression
of duplicates in Excel, you need to use conditional
formatting. See
http://www.contextures.com/xlCondFormat03.html#Duplicate
for an example of
how to do this. You could apply the conditional
formatting in advance to
the appropriate columns in your spreadsheet template.

Re some of your other questions:

[ The Referral Status grouping level effectively converts
the field to
subtitles in each of the hospital reports.
some of the detail fields and Referral Status header
labels are calculated
textboxes that show different data
depending on the Referral Status (subtitle).]
You could handle this by formatting the column using the
Switch function
within your query. (See Visual Basic help if you are
not familiar with the
Switch function. Access does a good job of hiding its
availability)

[There would also need to be a blank row forced between
each grouping stage
within the sheet - the detail size is variable so it
would have to be built
into the process - is it possible to run Excel VBA on
the workbook from
within the Access code?]
Yes, it is possible to use Excel VBA from Access. I'm
not sure how you can
handle the multiple break levels, however. I have some
ideas but would need
to see your report format before I could give you
suggestions.

[I have no idea what DAO/ADO recordsets are though - but
I'll look into it
and learn - know any good sources?]
Maybe it was just me, but years ago when I was teaching
myself to work with
recordsets, I really struggled. And I was working from a
book that was
highly recommended (written by Getz, Litwin and
Reddick). If this is your
first time working with DAO, automation of Excel from
within Access and
looping constructs, you would probably benefit from
working with a
consultant/coach to get you started and to help you over
the hurdles.

Allan

message
Allan,

First of all, thanks so much - you're a star.

That sounds workable. I already have 5 seperate queries,
and I use a filter (actioned in VBA if required) to
specify the hospital. I have no idea what DAO/ADO
recordsets are though - but I'll look into it and
learn -
know any good sources?

I think it would help to explain the grouping levels:
1st - Originating Hospital
2nd - Referral Status (or referral stage depending on
report)
3rd - Referral key (since 1 patient may have >1
appointment)

A couple of worries/doubts in it as a result...

The Referral Status grouping level effectively converts
the field to subtitles in each of the hospital reports.
some of the detail fields and Referral Status header
labels are calculated textboxes that show different data
depending on the Referral Status (subtitle).

Would I be able to recreate this? There would also need
to be a blank row forced between each grouping stage
within the sheet - the detail size is variable so it
would
have to be built into the process - is it possible to
run
Excel VBA on the workbook from within the Access code?

Secondly due to the referral key grouping, I set "Hide
duplicates" to yes on many fields in the report. Can
this
copy across?

If we can sort out the above queries - it sounds great,
and I think I could manage it! Would even remove the
need
for email automation!

IF we can't sort out the queries above is it feasible to
have 5 Excel workbooks set up for each report and filter
the different Referral Statuses into seperate sheets -
wouldn't fix the hide duplicates problem though... and
would cause further work - would rather be able to solve
the queries I think!

Thanks loads Allan.

Basil

-----Original Message-----
Basil,

The key question is whether you can represent the data
from your Access
reports using queries. If so, the TransferSpreadsheet
method should work.
The key to making the process more efficient is using
VBA, in particular its
looping capability.

See if this makes sense for you.
- Create a template in Excel that has 5 sheets, one
for
each report type
- In Access, create 5 queries (or modify existing),
one
for each report.
For the WHERE clause of each query, use something
like "WHERE HospitalID =
Forms!frmSelect!cboHospitalID " (cboHospitalID being a
combo box on the form
frmSelect)
- create a VBA procedure that loops through all the
hospitals using a DAO or
ADO recordset (e.g. recordset name = rsHosp)
- within the loop, you can create an Excel file for
each
hopital by doing
the following:
1) set the value of cboHospitalID = rsHosp! HospitalID
2) reset a variable that contains the target Excel
filename e.g...\Excel
Files\[HospitalID].xls
3) use the Kill statement (see Visual Basic for
Applications help in MS
Access) to delete any prior version of the target file
4) copy the source Excel template file to the target
file
using the FileCopy
statement
5) use the TransferSpreadsheet method to transfer data
from each of your 5
queries (which will be filtered for the current
hospital)
to the appropriate
sheet in the target Excel file
6) e-mail the Excel file
7) move to the next hospital

Let me know if this sounds workable.

Allan

--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting
Systems
www.fieldscope.com
860.242.4184


message
Thanks Allan,

This sounds like the right kind of track I need to be
on.

I am aware of how you can create a pivot form which
can
be
opened up in Excel edit mode - this seems to hold the
Excel file within the database (although I could be
wrong) - it's not what I want but holding the excel
file
internally might be useful to me. I'll give you more
detail:

I work for a hospital. There are 30 hospitals that
send
patients to us for treatment (these are referrals).
When
with us, I have allocated information into different
groups -
1. patients who we have only got the referral for
2. patients who we have seen at a clinic
3. patients who we have have agreed to admit (booked
list)
4. patients who have been admitted
5. patients who we see at a follow-up clinic

Within these groups referrals can have a different
status
(there is a referral key which carries across all
activity) - eg. point 2 can be patients sent back to
the
original hospital/patients on hold/Active patients
(waiting for appointment or outcome).

I have created 5 reports in line with the above, and
introduced a grouping level to show the referral
status
(forces new page). (There is also a higher grouping
for
hospital of origin).

Thanks for bearing with me on this...

I have a form that enables you to view all reports
or a
selected report - either showing all hospitals within
them
or filtered by hospital.

Now is where I need help... they want to see the
report
in
Excel - ideally 1 workbook with 5 sheets (I'd remove
the
page break for the status grouping - so all statuses
will
be on the same sheet). But 5 workbooks would be
fine.
Now I would need to run through a process as follows:

-Select 1st hospital in table
-run all 5 reports (shoved in Excel if poss) (if have
data)
-attach them to an email
-send to email (held in the email fild of the
hospital
table)
-Select 2nd hospital...

This would be done weekly. As you can imagine, the
reports would be referenced on many occasions - and
I'd
rather not have 150 (30hospitals*5reports) Excel
templates.

Will the transferspreadsheet allow me to make this
work,
how?

I really appreciate you getting back to me on this.

Many thanks,

Basil

-----Original Message-----
Mike,

Without knowing more about your data and Excel
reporting
needs, its hard to
give a definitive answer. The easiest approach, and
hopefully the one you
can use, is to use DoCmd.TransferSpreadsheet (see
TransferSpreadsheet Method
under MS Access help). This command allows you to
export
data from tables
or queries to Excel. Since you can use query
datasets,
the data you export
to Excel can be data joined from multiple tables,
calculated data, summary
data, and/or filtered data.. The Excel sheet that
you
transfer to can be
setup as a template with the additional formulas,
charts,
etc already in
place. Macros or VBA code can be used to automate
the
data transfer to
multiple Excel files.

If this approach sounds workable and you need more
direction, let me know.
I can also suggest other alternatives if this does
not
sound viable. The
automation of e-mails, if needed, shouldn't be too
difficult either.

Allan
--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting
Systems
www.fieldscope.com
860.242.4184


"Mike T." <[email protected]>
wrote
in
message
Hello,

I have moved all my work from Excel to Access coz
Excel
couldn't handle the amounts of data and multiple
tables.
Nobody who I produce reports for can handle Access
and
want their reports in Excel (so that they can
manipulate
them within Excel) - rather than say, RTF or SNP.

The reports are a bit complicated so they don't
work
at
all nicely if exported to Excel, and lose a lot of
data
(eg charts).

I need to learn how to work with Excel within
Access -
DAO
or OLE or whichever it is. I'll maybe need to feed
pre-
made templates or something. Can anyone offer
tips
or
sources of help?

I need to learn it pretty darn well in the next
week.
(possible have to automate emails too, but
hopefully
not)

Thanks all.

Mike


.



.



.

.

 

[Mike]

Hi again Allan.

You've given me plenty of great stuff here to work with...
I'll get on the case again from Monday. I'll send you an
email (if that's ok) to let you know how it's going (and
possibly if I get totally stuck!).

Thanks so much, good luck with the consulting.

Basil

-----Original Message-----
Basil,

I'll try to respond to both of your last posts.

First, in response to this:
[All the people on this website do an amazing job. I often
wonder why you all do it - do you get paid by microsoft?]
I don't know about anyone else but I certainly don't get paid. I respond to
posts for a few reasons. One reason is I like to share what I have learned
and help people get to a solution with a little less pain. Newsgroup posts
by other people willing to share their knowledge have have been very helpful
to me and I feel that I should give back when I can. A second reason is
that I like solving a good puzzle. I tend to respond to the posts that I
know are somewhat challenging, not the ones that can be answered with a
one-line response. And finally, I'm not totally altruistic. As an
independent consultant, I'm always looking for opportunities for exposure.
You never know when someone will see something you wrote and contact you in
connection with a project. (It happens!)

Now for the technical stuff:

1) All of the VBA can be handled from Access. But you need to open Excel
and go to the VB Help there for info on the objects, methods, etc. available
in Excel.
2) Based on the report you outlined, the

TransferSpreadsheet method probably

will not work for you. You will need to use the brute force method to walk
records in Access and copy the values to cells in Excel.
a) Open the Excel application by calling the following function

Public Function basExcelApp() As Boolean
Dim ExcelApp As Object
On Error Resume Next
basExcelApp = True
' see if Excel is loaded
Set ExcelApp = GetObject(, "Excel.application")
If Err <> 0 Then ' if not then load Excel
On Error GoTo err_bas
Set ExcelApp = CreateObject("Excel.application")
End If

exit_bas:
Exit Function

err_bas:
MsgBox Error$, vbCritical
basExcelApp = False
Resume exit_bas

End Function

b) You need to open one recordset to walk through the hospitals and one or
more recordsets (depending how you handle the different queries) to walk the
detail records in Access.

c)To create a Excel new file from the template open the workbook, use code
like the following:
'delete old copy of destination file [variables master and extfn
previously defined]

If Dir(extfn) Then
Kill extfn
End If
On Error GoTo err_gen
'copy template master to destination
FileCopy master, extfn
'Open newly created destination file
Workbooks.Open extfn, 0, False, , "password"
Set wbDest = Workbooks.Item(extWb) [wbDest is dimmed as a
Workbook]
'Unprotect destination sheet
wbDest.Sheets(1).Unprotect

d) To copy data from Access to Excel cells, you can do something like this:
'rsHosp is the Access recordset; the Cells arguments are row, then
column (13,2 = B13)
With wbDest.Sheets(1)
.Cells(12, 2) = rsHosp!ReferralStatus
.Cells(13, 4) = rsHosp!ReferralKey
..
End With

e) Because you will be walking rows in Excel, you will actually need to
use a variable for the row number in the above,
e.g. irow = irow + 1 'increment row
.cells(irow, 2) = rsHosp!ReferralStatus

That's kind of a rough sketch. Hopefully it gives you a start. There's
obviously a lot of detail to deal with in terms of creating your looping
logic and handling the 5 different queries and the spreadsheets that they
populate.

Good luck. Let me know how it's going.

Allan
--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184

Hi again!

The last post I put up has a bit more detail that you
kindly asked for, so I'd look at that first.

The reason for this second post is that I need another
shove on the Excel side of things. I've figured out
creating a recordset for the hospitals in Access... I'm
now not sure about the methodology of working with the
Excel file - i.e. how to specify which sheet/row the data
should be exported to. Do I use a combination of VBA in
the Access form followed by VBA created in Excel
workbook_open? Does the email part come into the
Access/Excel code - etc. It's not easy to find a simple
explaination for this on the net, and Northwind doesn't
help.

Thanks again Allan.

-----Original Message-----
Thanks for your kind words. I'm glad to help when it

is
appreciated (but

tend to get annoyed when there is not even an acknowledgement).

Re the grouping question:
1)My thought was that you include a column in the

Access
query that would be

your header value (dependant on status). If you do

this,
it seems that you

would only need one row/cell on your spreadsheet that would show the
appropriate value based on your filter. Am I missing something?
2)Can you give me a sample of what you visualize for

the
heading, subheading

and detail ?
3)Are there any group footers?

Allan


Conditional formatting... spot on, didn't think of that.
I guess issues such as this are things that I can

sort
out

when I get to work on it. I think you've explained really
well that it is worth pursuing.

Regarding your thoughts on the queries I raised:

1. (Grouping levels stuff) - I currently use the switch
function to set what the field will show and what the
label will be (the label is actually a textbox) with the
criteria being the referral status. What you've suggested
makes sense for the detail in the column. But I don't
know how I could get the Status to show as subheadings
rather than another column, and create a new header
beneath each subheading showing the calculated label
(dependant on status). If I'm right on this, I think I've
got a workable solution - tell me if it can work/what you
think:
In Excel workbook:
-Create 3 rows at the top showing the 3 different header
possibilities (dependant on status).
-Have the status in the final column and hide it. (sort
data by status and then referral key to enable hide
duplicates to work - con.formatting will also have to look
at equal referral keys in the 2 rows)
-Put a filter on the hidden status column
-Create a combo box for patient to select status
(subheading)
-action from combo the relevant header row to be visible
and the status column appropriately filtered.

- This would have to be created for every sheet.

2. The above wouldn't require the breaks... otherwise I've
done similar things before in Excel - it is getting the
status as a sub-heading that I don't know.

3. I might try looking through internet/helpfiles

until
I

hit a hurdle - I've never actually had a

coach/training
or

read an IT book.

You've helped me so much, I really appreciate all the time
you must have spent and the advice that you have given.
I'm going to get to work on it. I'd value your thoughts
on my suggestion in 1. (If what I've written makes any
sense!).

Thanks Allan, there's a beer (or 2) waiting for you in
London!
I'll probably be back on for further problems...
especially in the emailing bits!

Basil

-----Original Message-----
Ah! This is where the superiority of Access over Excel
as a reporting tool
becomes evident. But this still may be doable. To
handle the suppression
of duplicates in Excel, you need to use conditional
formatting. See

http://www.contextures.com/xlCondFormat03.html#Duplicate

for an example of
how to do this. You could apply the conditional
formatting in advance to
the appropriate columns in your spreadsheet template.

Re some of your other questions:

[ The Referral Status grouping level effectively converts
the field to
subtitles in each of the hospital reports.
some of the detail fields and Referral Status header
labels are calculated
textboxes that show different data
depending on the Referral Status (subtitle).]
You could handle this by formatting the column using the
Switch function
within your query. (See Visual Basic help if you are
not familiar with the
Switch function. Access does a good job of hiding its
availability)

[There would also need to be a blank row forced between
each grouping stage
within the sheet - the detail size is variable so it
would have to be built
into the process - is it possible to run Excel VBA on
the workbook from
within the Access code?]
Yes, it is possible to use Excel VBA from Access. I'm
not sure how you can
handle the multiple break levels, however. I have some
ideas but would need
to see your report format before I could give you
suggestions.

[I have no idea what DAO/ADO recordsets are

though -
but

I'll look into it
and learn - know any good sources?]
Maybe it was just me, but years ago when I was teaching
myself to work with
recordsets, I really struggled. And I was working

from
a

book that was
highly recommended (written by Getz, Litwin and
Reddick). If this is your
first time working with DAO, automation of Excel from
within Access and
looping constructs, you would probably benefit from
working with a
consultant/coach to get you started and to help you over
the hurdles.

Allan

message
Allan,

First of all, thanks so much - you're a star.

That sounds workable. I already have 5 seperate queries,
and I use a filter (actioned in VBA if required) to
specify the hospital. I have no idea what DAO/ADO
recordsets are though - but I'll look into it and
learn -
know any good sources?

I think it would help to explain the grouping levels:
1st - Originating Hospital
2nd - Referral Status (or referral stage depending on
report)
3rd - Referral key (since 1 patient may have >1
appointment)

A couple of worries/doubts in it as a result...

The Referral Status grouping level effectively converts
the field to subtitles in each of the hospital reports.
some of the detail fields and Referral Status header
labels are calculated textboxes that show

different
data

depending on the Referral Status (subtitle).

Would I be able to recreate this? There would

also
need

to be a blank row forced between each grouping stage
within the sheet - the detail size is variable so it
would
have to be built into the process - is it possible to
run
Excel VBA on the workbook from within the Access code?

Secondly due to the referral key grouping, I set "Hide
duplicates" to yes on many fields in the report. Can
this
copy across?

If we can sort out the above queries - it sounds great,
and I think I could manage it! Would even remove the
need
for email automation!

IF we can't sort out the queries above is it feasible to
have 5 Excel workbooks set up for each report and filter
the different Referral Statuses into seperate sheets -
wouldn't fix the hide duplicates problem though... and
would cause further work - would rather be able to solve
the queries I think!

Thanks loads Allan.

Basil

-----Original Message-----
Basil,

The key question is whether you can represent the data
from your Access
reports using queries. If so, the TransferSpreadsheet
method should work.
The key to making the process more efficient is using
VBA, in particular its
looping capability.

See if this makes sense for you.
- Create a template in Excel that has 5 sheets, one
for
each report type
- In Access, create 5 queries (or modify existing),
one
for each report.
For the WHERE clause of each query, use something
like "WHERE HospitalID =
Forms!frmSelect!cboHospitalID " (cboHospitalID being a
combo box on the form
frmSelect)
- create a VBA procedure that loops through all the
hospitals using a DAO or
ADO recordset (e.g. recordset name = rsHosp)
- within the loop, you can create an Excel file for
each
hopital by doing
the following:
1) set the value of cboHospitalID = rsHosp! HospitalID
2) reset a variable that contains the target Excel
filename e.g...\Excel
Files\[HospitalID].xls
3) use the Kill statement (see Visual Basic for
Applications help in MS
Access) to delete any prior version of the target file
4) copy the source Excel template file to the target
file
using the FileCopy
statement
5) use the TransferSpreadsheet method to transfer data
from each of your 5
queries (which will be filtered for the current
hospital)
to the appropriate
sheet in the target Excel file
6) e-mail the Excel file
7) move to the next hospital

Let me know if this sounds workable.

Allan

--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting
Systems
www.fieldscope.com
860.242.4184


"Mike" <[email protected]>

wrote
in

message
Thanks Allan,

This sounds like the right kind of track I need to be
on.

I am aware of how you can create a pivot form which
can
be
opened up in Excel edit mode - this seems to

hold
the

Excel file within the database (although I

could
be

wrong) - it's not what I want but holding the excel
file
internally might be useful to me. I'll give

you
more

detail:

I work for a hospital. There are 30 hospitals that
send
patients to us for treatment (these are referrals).
When
with us, I have allocated information into different
groups -
1. patients who we have only got the referral for
2. patients who we have seen at a clinic
3. patients who we have have agreed to admit (booked
list)
4. patients who have been admitted
5. patients who we see at a follow-up clinic

Within these groups referrals can have a different
status
(there is a referral key which carries across all
activity) - eg. point 2 can be patients sent

back
to

the
original hospital/patients on hold/Active patients
(waiting for appointment or outcome).

I have created 5 reports in line with the

above,
and

introduced a grouping level to show the referral
status
(forces new page). (There is also a higher grouping
for
hospital of origin).

Thanks for bearing with me on this...

I have a form that enables you to view all reports
or a
selected report - either showing all hospitals within
them
or filtered by hospital.

Now is where I need help... they want to see the
report
in
Excel - ideally 1 workbook with 5 sheets (I'd remove
the
page break for the status grouping - so all statuses
will
be on the same sheet). But 5 workbooks would be
fine.
Now I would need to run through a process as follows:

-Select 1st hospital in table
-run all 5 reports (shoved in Excel if poss)

(if
have

data)
-attach them to an email
-send to email (held in the email fild of the
hospital
table)
-Select 2nd hospital...

This would be done weekly. As you can imagine, the
reports would be referenced on many occasions - and
I'd
rather not have 150 (30hospitals*5reports) Excel
templates.

Will the transferspreadsheet allow me to make this
work,
how?

I really appreciate you getting back to me on this.

Many thanks,

Basil

-----Original Message-----
Mike,

Without knowing more about your data and Excel
reporting
needs, its hard to
give a definitive answer. The easiest

approach,
and

hopefully the one you
can use, is to use DoCmd.TransferSpreadsheet (see
TransferSpreadsheet Method
under MS Access help). This command allows

you
to

export
data from tables
or queries to Excel. Since you can use query
datasets,
the data you export
to Excel can be data joined from multiple tables,
calculated data, summary
data, and/or filtered data.. The Excel sheet that
you
transfer to can be
setup as a template with the additional formulas,
charts,
etc already in
place. Macros or VBA code can be used to automate
the
data transfer to
multiple Excel files.

If this approach sounds workable and you need more
direction, let me know.
I can also suggest other alternatives if this does
not
sound viable. The
automation of e-mails, if needed, shouldn't be too
difficult either.

Allan
--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting
Systems
www.fieldscope.com
860.242.4184


"Mike T."

wrote
in
message
Hello,

I have moved all my work from Excel to

Access
coz

Excel
couldn't handle the amounts of data and multiple
tables.
Nobody who I produce reports for can handle Access
and
want their reports in Excel (so that they can
manipulate
them within Excel) - rather than say, RTF or SNP.

The reports are a bit complicated so they don't
work
at
all nicely if exported to Excel, and lose a lot of
data
(eg charts).

I need to learn how to work with Excel within
Access -
DAO
or OLE or whichever it is. I'll maybe need

to
feed

pre-
made templates or something. Can anyone offer
tips
or
sources of help?

I need to learn it pretty darn well in the next
week.
(possible have to automate emails too, but
hopefully
not)

Thanks all.

Mike


.



.



.



.

.

 

[Allan Thompson]

Basil,

E-mail is fine.

Allan

Hi again Allan.

You've given me plenty of great stuff here to work with...
I'll get on the case again from Monday. I'll send you an
email (if that's ok) to let you know how it's going (and
possibly if I get totally stuck!).

Thanks so much, good luck with the consulting.

Basil

-----Original Message-----
Basil,

I'll try to respond to both of your last posts.

First, in response to this:
[All the people on this website do an amazing job. I often
wonder why you all do it - do you get paid by microsoft?]
I don't know about anyone else but I certainly don't get paid. I respond to
posts for a few reasons. One reason is I like to share what I have learned
and help people get to a solution with a little less pain. Newsgroup posts
by other people willing to share their knowledge have have been very helpful
to me and I feel that I should give back when I can. A second reason is
that I like solving a good puzzle. I tend to respond to the posts that I
know are somewhat challenging, not the ones that can be answered with a
one-line response. And finally, I'm not totally altruistic. As an
independent consultant, I'm always looking for opportunities for exposure.
You never know when someone will see something you wrote and contact you in
connection with a project. (It happens!)

Now for the technical stuff:

1) All of the VBA can be handled from Access. But you need to open Excel
and go to the VB Help there for info on the objects, methods, etc. available
in Excel.
2) Based on the report you outlined, the

TransferSpreadsheet method probably

will not work for you. You will need to use the brute force method to walk
records in Access and copy the values to cells in Excel.
a) Open the Excel application by calling the following function

Public Function basExcelApp() As Boolean
Dim ExcelApp As Object
On Error Resume Next
basExcelApp = True
' see if Excel is loaded
Set ExcelApp = GetObject(, "Excel.application")
If Err <> 0 Then ' if not then load Excel
On Error GoTo err_bas
Set ExcelApp = CreateObject("Excel.application")
End If

exit_bas:
Exit Function

err_bas:
MsgBox Error$, vbCritical
basExcelApp = False
Resume exit_bas

End Function

b) You need to open one recordset to walk through the hospitals and one or
more recordsets (depending how you handle the different queries) to walk the
detail records in Access.

c)To create a Excel new file from the template open the workbook, use code
like the following:
'delete old copy of destination file [variables master and extfn
previously defined]

If Dir(extfn) Then
Kill extfn
End If
On Error GoTo err_gen
'copy template master to destination
FileCopy master, extfn
'Open newly created destination file
Workbooks.Open extfn, 0, False, , "password"
Set wbDest = Workbooks.Item(extWb) [wbDest is dimmed as a
Workbook]
'Unprotect destination sheet
wbDest.Sheets(1).Unprotect

d) To copy data from Access to Excel cells, you can do something like this:
'rsHosp is the Access recordset; the Cells arguments are row, then
column (13,2 = B13)
With wbDest.Sheets(1)
.Cells(12, 2) = rsHosp!ReferralStatus
.Cells(13, 4) = rsHosp!ReferralKey
..
End With

e) Because you will be walking rows in Excel, you will actually need to
use a variable for the row number in the above,
e.g. irow = irow + 1 'increment row
.cells(irow, 2) = rsHosp!ReferralStatus

That's kind of a rough sketch. Hopefully it gives you a start. There's
obviously a lot of detail to deal with in terms of creating your looping
logic and handling the 5 different queries and the spreadsheets that they
populate.

Good luck. Let me know how it's going.

Allan
--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184

Hi again!

The last post I put up has a bit more detail that you
kindly asked for, so I'd look at that first.

The reason for this second post is that I need another
shove on the Excel side of things. I've figured out
creating a recordset for the hospitals in Access... I'm
now not sure about the methodology of working with the
Excel file - i.e. how to specify which sheet/row the data
should be exported to. Do I use a combination of VBA in
the Access form followed by VBA created in Excel
workbook_open? Does the email part come into the
Access/Excel code - etc. It's not easy to find a simple
explaination for this on the net, and Northwind doesn't
help.

Thanks again Allan.

-----Original Message-----
Thanks for your kind words. I'm glad to help when it is
appreciated (but
tend to get annoyed when there is not even an
acknowledgement).

Re the grouping question:
1)My thought was that you include a column in the Access
query that would be
your header value (dependant on status). If you do this,
it seems that you
would only need one row/cell on your spreadsheet that
would show the
appropriate value based on your filter. Am I missing
something?
2)Can you give me a sample of what you visualize for the
heading, subheading
and detail ?
3)Are there any group footers?

Allan


message
Conditional formatting... spot on, didn't think of that.
I guess issues such as this are things that I can sort
out
when I get to work on it. I think you've explained
really
well that it is worth pursuing.

Regarding your thoughts on the queries I raised:

1. (Grouping levels stuff) - I currently use the switch
function to set what the field will show and what the
label will be (the label is actually a textbox) with the
criteria being the referral status. What you've
suggested
makes sense for the detail in the column. But I don't
know how I could get the Status to show as subheadings
rather than another column, and create a new header
beneath each subheading showing the calculated label
(dependant on status). If I'm right on this, I think
I've
got a workable solution - tell me if it can work/what
you
think:
In Excel workbook:
-Create 3 rows at the top showing the 3 different header
possibilities (dependant on status).
-Have the status in the final column and hide it. (sort
data by status and then referral key to enable hide
duplicates to work - con.formatting will also have to
look
at equal referral keys in the 2 rows)
-Put a filter on the hidden status column
-Create a combo box for patient to select status
(subheading)
-action from combo the relevant header row to be visible
and the status column appropriately filtered.

- This would have to be created for every sheet.

2. The above wouldn't require the breaks... otherwise
I've
done similar things before in Excel - it is getting the
status as a sub-heading that I don't know.

3. I might try looking through internet/helpfiles until
I
hit a hurdle - I've never actually had a coach/training
or
read an IT book.

You've helped me so much, I really appreciate all the
time
you must have spent and the advice that you have given.
I'm going to get to work on it. I'd value your thoughts
on my suggestion in 1. (If what I've written makes any
sense!).

Thanks Allan, there's a beer (or 2) waiting for you in
London!
I'll probably be back on for further problems...
especially in the emailing bits!

Basil

-----Original Message-----
Ah! This is where the superiority of Access over Excel
as a reporting tool
becomes evident. But this still may be doable. To
handle the suppression
of duplicates in Excel, you need to use conditional
formatting. See

http://www.contextures.com/xlCondFormat03.html#Duplicate
for an example of
how to do this. You could apply the conditional
formatting in advance to
the appropriate columns in your spreadsheet template.

Re some of your other questions:

[ The Referral Status grouping level effectively
converts
the field to
subtitles in each of the hospital reports.
some of the detail fields and Referral Status header
labels are calculated
textboxes that show different data
depending on the Referral Status (subtitle).]
You could handle this by formatting the column using
the
Switch function
within your query. (See Visual Basic help if you are
not familiar with the
Switch function. Access does a good job of hiding its
availability)

[There would also need to be a blank row forced between
each grouping stage
within the sheet - the detail size is variable so it
would have to be built
into the process - is it possible to run Excel VBA on
the workbook from
within the Access code?]
Yes, it is possible to use Excel VBA from Access. I'm
not sure how you can
handle the multiple break levels, however. I have some
ideas but would need
to see your report format before I could give you
suggestions.

[I have no idea what DAO/ADO recordsets are though -
but
I'll look into it
and learn - know any good sources?]
Maybe it was just me, but years ago when I was teaching
myself to work with
recordsets, I really struggled. And I was working from
a
book that was
highly recommended (written by Getz, Litwin and
Reddick). If this is your
first time working with DAO, automation of Excel from
within Access and
looping constructs, you would probably benefit from
working with a
consultant/coach to get you started and to help you
over
the hurdles.

Allan

message
Allan,

First of all, thanks so much - you're a star.

That sounds workable. I already have 5 seperate
queries,
and I use a filter (actioned in VBA if required) to
specify the hospital. I have no idea what DAO/ADO
recordsets are though - but I'll look into it and
learn -
know any good sources?

I think it would help to explain the grouping levels:
1st - Originating Hospital
2nd - Referral Status (or referral stage depending on
report)
3rd - Referral key (since 1 patient may have >1
appointment)

A couple of worries/doubts in it as a result...

The Referral Status grouping level effectively
converts
the field to subtitles in each of the hospital
reports.
some of the detail fields and Referral Status header
labels are calculated textboxes that show different
data
depending on the Referral Status (subtitle).

Would I be able to recreate this? There would also
need
to be a blank row forced between each grouping stage
within the sheet - the detail size is variable so it
would
have to be built into the process - is it possible to
run
Excel VBA on the workbook from within the Access
code?

Secondly due to the referral key grouping, I
set "Hide
duplicates" to yes on many fields in the report. Can
this
copy across?

If we can sort out the above queries - it sounds
great,
and I think I could manage it! Would even remove the
need
for email automation!

IF we can't sort out the queries above is it
feasible to
have 5 Excel workbooks set up for each report and
filter
the different Referral Statuses into seperate
sheets -
wouldn't fix the hide duplicates problem though...
and
would cause further work - would rather be able to
solve
the queries I think!

Thanks loads Allan.

Basil

-----Original Message-----
Basil,

The key question is whether you can represent the
data
from your Access
reports using queries. If so, the
TransferSpreadsheet
method should work.
The key to making the process more efficient is
using
VBA, in particular its
looping capability.

See if this makes sense for you.
- Create a template in Excel that has 5 sheets, one
for
each report type
- In Access, create 5 queries (or modify existing),
one
for each report.
For the WHERE clause of each query, use something
like "WHERE HospitalID =
Forms!frmSelect!cboHospitalID " (cboHospitalID
being a
combo box on the form
frmSelect)
- create a VBA procedure that loops through all the
hospitals using a DAO or
ADO recordset (e.g. recordset name = rsHosp)
- within the loop, you can create an Excel file for
each
hopital by doing
the following:
1) set the value of cboHospitalID = rsHosp!
HospitalID
2) reset a variable that contains the target Excel
filename e.g...\Excel
Files\[HospitalID].xls
3) use the Kill statement (see Visual Basic for
Applications help in MS
Access) to delete any prior version of the target
file
4) copy the source Excel template file to the target
file
using the FileCopy
statement
5) use the TransferSpreadsheet method to transfer
data
from each of your 5
queries (which will be filtered for the current
hospital)
to the appropriate
sheet in the target Excel file
6) e-mail the Excel file
7) move to the next hospital

Let me know if this sounds workable.

Allan

--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting
Systems
www.fieldscope.com
860.242.4184


in
message
Thanks Allan,

This sounds like the right kind of track I need
to be
on.

I am aware of how you can create a pivot form
which
can
be
opened up in Excel edit mode - this seems to hold
the
Excel file within the database (although I could
be
wrong) - it's not what I want but holding the
excel
file
internally might be useful to me. I'll give you
more
detail:

I work for a hospital. There are 30 hospitals
that
send
patients to us for treatment (these are
referrals).
When
with us, I have allocated information into
different
groups -
1. patients who we have only got the referral for
2. patients who we have seen at a clinic
3. patients who we have have agreed to admit
(booked
list)
4. patients who have been admitted
5. patients who we see at a follow-up clinic

Within these groups referrals can have a different
status
(there is a referral key which carries across all
activity) - eg. point 2 can be patients sent back
to
the
original hospital/patients on hold/Active patients
(waiting for appointment or outcome).

I have created 5 reports in line with the above,
and
introduced a grouping level to show the referral
status
(forces new page). (There is also a higher
grouping
for
hospital of origin).

Thanks for bearing with me on this...

I have a form that enables you to view all reports
or a
selected report - either showing all hospitals
within
them
or filtered by hospital.

Now is where I need help... they want to see the
report
in
Excel - ideally 1 workbook with 5 sheets (I'd
remove
the
page break for the status grouping - so all
statuses
will
be on the same sheet). But 5 workbooks would be
fine.
Now I would need to run through a process as
follows:

-Select 1st hospital in table
-run all 5 reports (shoved in Excel if poss) (if
have
data)
-attach them to an email
-send to email (held in the email fild of the
hospital
table)
-Select 2nd hospital...

This would be done weekly. As you can imagine,
the
reports would be referenced on many occasions -
and
I'd
rather not have 150 (30hospitals*5reports) Excel
templates.

Will the transferspreadsheet allow me to make this
work,
how?

I really appreciate you getting back to me on
this.

Many thanks,

Basil

-----Original Message-----
Mike,

Without knowing more about your data and Excel
reporting
needs, its hard to
give a definitive answer. The easiest approach,
and
hopefully the one you
can use, is to use DoCmd.TransferSpreadsheet (see
TransferSpreadsheet Method
under MS Access help). This command allows you
to
export
data from tables
or queries to Excel. Since you can use query
datasets,
the data you export
to Excel can be data joined from multiple tables,
calculated data, summary
data, and/or filtered data.. The Excel sheet
that
you
transfer to can be
setup as a template with the additional formulas,
charts,
etc already in
place. Macros or VBA code can be used to
automate
the
data transfer to
multiple Excel files.

If this approach sounds workable and you need
more
direction, let me know.
I can also suggest other alternatives if this
does
not
sound viable. The
automation of e-mails, if needed, shouldn't be
too
difficult either.

Allan
--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting
Systems
www.fieldscope.com
860.242.4184


"Mike T."

wrote
in
message
Hello,

I have moved all my work from Excel to Access
coz
Excel
couldn't handle the amounts of data and
multiple
tables.
Nobody who I produce reports for can handle
Access
and
want their reports in Excel (so that they can
manipulate
them within Excel) - rather than say, RTF or
SNP.

The reports are a bit complicated so they don't
work
at
all nicely if exported to Excel, and lose a
lot of
data
(eg charts).

I need to learn how to work with Excel within
Access -
DAO
or OLE or whichever it is. I'll maybe need to
feed
pre-
made templates or something. Can anyone offer
tips
or
sources of help?

I need to learn it pretty darn well in the next
week.
(possible have to automate emails too, but
hopefully
not)

Thanks all.

Mike


.



.



.



.

.