ACC2: Exporting Data to MS Excel 5.0 or 95 Twice Causes Error

ID: Q119806

The information in this article applies to:

Microsoft Access 2.0
Microsoft Excel for Windows, versions 5.0, 7.0

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

Problem 1

If you export data from Microsoft Access to a Microsoft Excel versions 5.0 or 7.0 workbook twice, either manually or using the TransferSpreadsheet macro action, and then try to delete extra worksheets from the workbook, you will receive the error message:

Not enough memory

Problem 2

Similarly, if you export data from Microsoft Access to a Microsoft Excel workbook twice, either manually or using the TransferSpreadsheet macro action, and then try to insert a worksheet, you receive the error message:

Cannot do that command on nonadjacent selections

RESOLUTION

Problem 1

Do not export Microsoft Access data directly to a Microsoft Excel workbook. Instead, use one of the following methods:

Export the data, either manually or using the TransferSpreadsheet macro action, to a Microsoft Excel version 4.0 workbook.

Export the data, either manually or using the TransferSpreadsheet macro action, to a temporary Microsoft Excel 5.0 or 7.0 workbook, and then use a Microsoft Excel macro to transfer the data from one workbook to another.

If you accidentally export data to a Microsoft Excel workbook, follow these steps to correct the problem:

In Microsoft Excel, open the workbook to which you exported the data, and then choose Save As from the File menu.

In the Save File As Type box, select Microsoft Excel 4.0 Workbook, and then choose OK. Note that the file's extension changes to ".XLW".

Close the workbook.

Open the .XLW file you created in step 2.

From the File menu, choose Save As.

In the Save File As Type box, select Microsoft Excel Workbook, and then choose OK.

When you are prompted, choose Yes to replace the existing file.

Problem 2

To solve the problem with inserting worksheets, move the worksheets in the exported workbook to a new workbook within Microsoft Excel.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access version 7.0.

MORE INFORMATION

Steps to Reproduce Problem

Create a new Microsoft Excel version 5.0 workbook. Type 1 in cell A1, and type 2 in cell B1.

Delete worksheets 5-16 by selecting their tabs and then choosing Delete Sheet from the Edit menu.

Save the workbook as BOOK1.XLS. Make sure to save the file in Microsoft Excel Workbook format (the default format).

Close the workbook and then minimize Microsoft Excel.

Start Microsoft Access version 2.0 and open the sample database NWIND.MDB.

Create the following new macro, and then save it as TestMacro:


       Macro Name   Macro Actions
       --------------------------------
       TestMacro    TransferSpreadsheet

       TestMacro Actions
       ----------------------------------------
       TransferSpreadsheet
          Transfer Type: Export
          Spreadsheet Type: Microsoft Excel 5.0
          Table Name: Customers
          File Name: <drive>:\<path>\BOOK1.XLS

Create a blank, new form.

Add a command button to the form. Set the command button's OnClick property to the TestMacro macro.

Save the form, and then view it in Form view.

Choose the command button twice.

Switch to Microsoft Excel.

Open the BOOK1.XLS workbook.

Try to delete the new worksheets. You will receive the error message stated earlier in this article.

NOTE: This problem occurs with the second export to the same Microsoft Excel 5.0 workbook. If you export the data only once, the problem does not occur. The problem will also occur if you export the data, delete the new worksheet, export the data again, and then try to delete the new worksheet.

REFERENCES

For more information about the TransferSpreadsheet macro action, search for "TransferSpreadsheet," and then "TransferSpreadsheet Action" using the Microsoft Access Help menu.

Microsoft Access "User's Guide," version 2.0, Chapter 26, "Using Macros to Print Reports or Transfer Data," page 700

Additional query words: interoperability


Keywords          : kberrmsg kbinterop IsmExl5 
Version           : 2.0
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: April 6, 1999