SubdatasheetsAndSubformsAccessToExcelExporter

Table of contents

• Tool's overview

• Download

• Features

• Tool's advantages

• Tool's limitations

• Installation

• Using the add-in

• License code

• Contact information, questions and feedback
   
   

Tool's overview

Download

You may download the tool here (the size of ZIP file is 48 KB).

Purpose / Features

The tool is an MS Access add-in that can export following MS Access structures from MS Access to MS Excel:

- Table containing subdatasheet

- Query containing subdatasheet

- Form in datasheet view containing subdatasheet

- Form containing subform

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

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

You can see an example down on this page in the chapter Using the add-in.

Tool's advantages

• There is no other tool of this kind

The 1st version of the tool I originally created for my own purposes after I had searched the whole Internet and realized that there was no tool that could do what I needed.

Only after some time I realized that the tool can be useful also for other people.

• There are configurable parameters

There are following 4 parameters that enable the user to configure how the export will be carried out and look like:

• Tool works on most MS Office versions

Tool works with all MS Office versions from 2000 to 2010.

Tool doesn't work in MS Access 97 or earlier versions.

Tool's limitations

• It is 20-day Trial

See more details in License code chapter.

• English is the only tool's language

Tool's menus and dialogues are only in English.

On the other hand, there are not many menus and dialogues.

• To install the tool administrator's login is required

I really tried to do without admin login but realized that it is not possible.

It is a common feature of all MS Access add-ins that in order to install them the user needs by logged in as the administrator.

The reason behind is that MS Access add-ins get registered to the part of Windows registry that requires admin's permission.

Once the tool is installed it can be run by users with limited account.

• Tool's functions are limited when it is used in a compiled Access database

While in Access databases that are not compiled (databases with .mdb, .accdb suffixes) the tool can export to MS Excel both following MS Access structures:

- datasheet & subdatasheet
- form & subform

in compiled databases (they have. mde, .accde suffixes) the tool can export only:

- MS Access form & subform

and can't export Access datasheet & subdatasheet

• Both MS Access and MS Excel need be installed on the computer in order to run the tool

They both need be installed but they needn't be of the same version as far as each of them is version 2000 or above.

So for example the tool will work if MS Excel 2000 and MS Access 2010 are installed together on the same computer, or vice versa.

• Exporting thousands of rows may sometimes take long

The number of rows that can be exported from Access to Excel is limited only by number of rows that are available in your current MS Excel sheet.

Please keep in mind that:

- Excel sheets in MS Excel of versions 2000 - 2003 have 65 636 rows

- Excel sheets in MS Excel of versions 2007 and above have 1 048 576 rows

Sometimes the export process may take long because rows are being exported one after another.

If there are tens of thousands of rows to be exported, on slower computers it then may take minutes or even tens of minutes before the export finishes.

The export time is directly proportional to number of rows to be exported, but is also affected by computer's performance, number of fields to be exported and other factors.

• Only one child (sub-level) can be exported together with the parent

It means that if there are two or more children (subdatasheets or subforms) nested one into another (parent has a child and the child has another child, etc.), only the parent and the topmost child (subdatasheet or subform) will be exported while all children on lower levels will be ignored.

• The clipboard functions are not available during the export process

If you export thousands of rows, it may, especially on low performance computers, take minutes to finish.

During this process all clipboard functions (Ctrl+C, Ctrl+X, Ctrl+V and also Print Screen key) in all Windows applications (Excel, Word, Notepad, etc.) aren't available.

Installation

Installation of this add-in goes same like of any other standard MS Access add-in.

It only slightly differs across various MS Access versions.

Requirements:

• Only user with administration permissions can install the add-in.

Installation process:

1. Download, unzip and save the add-in to anywhere on your computer.
   Do not change add-in's name!
   The name must be exactly 'SubdatasheetsAndSubformsAccessToExcelExporter.mda', else it won't work.
    
    
   

2. Launch MS Access 'Add-In Manager':

In MS Access 2000 (and similarly in versions 2002, 2003) it can be found as follows:

Access Toolbar → Tools → Add-Ins → Add-In Manager

In MS Access 2007 (and similarly in version 2010) it can be found as follows:

Access Toolbar → Database Tools → Add-ins → Add-In Manager

3. The 'Add-In manager' looks the same across all MS Access versions:

4. Click on 'Add New...' button and provide with the path to the add-in (to 'SubdatasheetsAndSubformsAccessToExcelExporter.mda' file).
    
    
   

5. After providing with the path confirm with 'Open' button.

When the add-in is installed successfully you should see following:

Using the add-in

1. Open either of following MS Access structures that you want to export to MS Excel:
   - Datasheet with subdatasheet

   - Form with subform

    
    
   

2. Launch the add-in:

In MS Access 2000 (and similarly in versions 2002, 2003) launch it as follows:

Access Toolbar → Tools → Add-Ins → Export to Excel

In MS Access 2007 (and similarly in version 2010) launch it as follows:

Access Toolbar → Database Tools → Add-Ins → Export to Excel

Then with few clicks of the mouse you can convert MS Access datasheet with subdatasheet like the one on the example screenshot:

Download Example MS Access input.mdb related to the screenshot above

to MS Excel: 

Download Example MS Excel output.xls related to the screenshot above

Access source on one hand and Excel export on the other hand not only contain the same data but the even look similar.

But don't get mistaken, it is really MS Access on the 1st print screen and MS Excel on the 2nd.

• The plus (+) symbols on the left side of MS Access represent the option to view the subdatasheet's rows.
  

• While the plus (+) symbols on the left side of MS Excel sheet represent so called groups.
  (For example in MS Excel 2007 you can manually create group by going to 'Data' ribbon and then clicking on "Group" command button.)

The result is that all information from MS Access datasheet & subdatasheet structure gets exported to grouped layout in MS Excel.

MS Excel export is kind of virtual replica of MS Access datasheet & subdatasheet.

No information is lost and it even looks similar as in MS Access.

You get the analogical result when exporting Form with subform.
 
 

License code

Trial period

The tool is 20day Trial.

In order to use it without time constrains the 'License code' needs be purchased and entered into the tool.

License code price

The License code price is $9 or an equivalent in any currency supported by PayPal.

Should you need more than one license, you may contact me first and I will provide with discount.

License code is related to MAC address

The License code that you obtain will be related to the MAC address of your computer's network card, which has following interesting consequences:

• License code will work on your computer only as long as you keep your current network card in the computer.
  Or, in other words, if you remove your network card from your computer, License code (and thus the whole tool) will stop working.

• On the other hand, if you put related network card to a different computer, you can use the related License code on the other computer.
  (Of course, this is usually not possible because on most computers network cards are integrated with computer's main board.)

Required information

When purchasing License code, always remember to provide with following information:

1. Your MAC address (mandatory)
   The tool itself will provide you with MAC address after it is installed as trial.
   Remember, one License code is always related to one MAC address.
    
   

2. E-mail address (optional)
   The License code will always be sent to the e-mail related to payer's PayPal account.
   If you provide with additional e-mail, the License code will also be posted to this additional e-mail.
    
    

License code purchase

Input your MAC address (mandatory)

Input additional e-mail address (optional)

How long it takes before you obtain License code

You will usually obtain the License code within few hours since the payment is realized, the maximum is 24 hours.

License code is valid across all tool's versions

All versions of the tool that have been released so far and also those that will be released in the future will use the same License code logics, i.e. License code now purchased will be valid against all tool's versions to come in the future.

Questions about License code

Should you want to clarify payment process first, you may first ask questions at my contact e-mail below.

Contact information, questions and feedback

On the e-mail address Norbert.Limbersky@seznam.cz: I will:

• answer all questions related to the tool, its functions and licenses

• welcome all feedback and ideas related to the tool