Export datasheet with subdatasheet to Excel

[KG]

A charitable organization that I am involved with wishes to switch to
Giftworks (with which I am completely unfamiliar) to keep track of donations
and to generate "thank you" letters. So far, their data base, going back
several years, has been on MS Access and consists of (a) a large Donor's list
with personal information and (b) a subdatasheet listing individual donations
by check date.

I am told that Giftworks cannot import directly from Access and that it is
necessary to first make an intermediate data dump on Excel. I have never
attempted to export to Excel an Access data sheet and its linked
subdatasheet. Is that even possible? Should I address this question to the
Excel community Groups?

Many thanks for yor help

 

[John Nurick]

Hi KG,

The subdatasheet complicates things. You need to find out just what
Giftworks needs on the Excel sheet(s) it imports. I know knothing about
Giftworks, but at a guess it will want either

(a) two sheets, one with the list of donors and the other with the list
of donations (with some sort of DonorID to key the one to the other).

(b) a single sheet, with one row per donation, each row containing
details of the donor as well as of the donation.

For (a), you need to create two separate queries, each containing the
fields that Giftworks wants, formatted the way it wants. One will be
based on your current Donors table, the other on the Donations table
that underlies your subdatasheets. Then use File|Export to export each
of them to Excel.

For (b), you need to create one query that joins the two tables, and
then export that.

 

[KG]

Thank you, I'll try to find out (a) why Giftworks cannot import directly from
Access and (b) how the Access database needs to exported to it.

 

[Norbert]

Hi,
also needed to export Access datasheet with linked subdatasheet into MS Excel.
I searched on Internet a lot and didn't find a solution.
After this I made an standard Access add-in that does this.
MS Excel export created with this add-in is a virtual replica of MS Access datasheet & subdatasheet.
No information is lost and it even looks similar to MS Access datasheet & subdatasheet.

You will find my add-in if you Google a bit for "SubdatasheetsAndSubformsAccessToExcelExporter.mda" - this is the name of the add-in.

I believe it will help you

Norbert

 

[Norbert]

A2EE.mda

Hi,
a new version of above mentioned Access add-in has been released.
It's name is 'A2EE.mda' (which stands for Access To Excel Exporter) and it exports following MS Access structures to MS Excel:
- Table with subdatasheet
- Query with subdatasheet
- Form in datasheet view with subdatasheet
- Form with subform

The result of export is that data from both parent (datasheet or forms) and child (subdatasheet or subform) get exported to MS Excel while keeping the drill-down view.

You can export:
- either all parental records together with all related child's records
- or only current parental record (the one with cursor) with all its related child's records

Subdatasheets and subforms exported to Excel can thus be shared with non-Access users.

As web links can't put on this forum, I can only recommend to search for 'A2EE.mda' in your Internet search engine which will provide you with download link and other details.

Best regards
Norbert

 

[norbi]

KG wrote on 01/28/2007 23:56 ET

A charitable organization that I am involved with wishes to switch t
Giftworks (with which I am completely unfamiliar) to keep track of donation
and to generate "thank you" letters. So far, their data base, goin
bac
several years, has been on MS Access and consists of (a) a large Donor's lis
with personal information and (b) a subdatasheet listing individual donation
by check date

I am told that Giftworks cannot import directly from Access and that it i
necessary to first make an intermediate data dump on Excel. I have neve
attempted to export to Excel an Access data sheet and its linke
subdatasheet. Is that even possible? Should I address this question to th
Excel community Groups

Many thanks for yor hel

Hi
it is possible to export Access datasheet and its linked subdatasheet to M
Excel sheet
You can do this with the access add-in 'A2EE.mda' that can be downloaded on
www.limbersti.cz/A2EE
B
Norbert