Microsoft KB Archive/119806

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

Article ID: 119806

Article Last Modified on 8/16/2005

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Excel 95 Standard Edition

-



This article was previously published under Q119806





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



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:


 * 1) In Microsoft Excel, open the workbook to which you exported the data, and then choose Save As from the File menu.
 * 2) 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".
 * 3) Close the workbook.
 * 4) Open the .XLW file you created in step 2.
 * 5) From the File menu, choose Save As.
 * 6) In the Save File As Type box, select Microsoft Excel Workbook, and then choose OK.
 * 7) 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.



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.</li>  Create the following new macro, and then save it as TestMacro:

<pre class="fixed_text">      Macro Name   Macro Actions TestMacro   TransferSpreadsheet

TestMacro Actions TransferSpreadsheet Transfer Type: Export Spreadsheet Type: Microsoft Excel 5.0 Table Name: Customers File Name: :\ \BOOK1.XLS </li> Create a blank, new form.</li> Add a command button to the form. Set the command button's OnClick property to the TestMacro macro.</li> Save the form, and then view it in Form view.</li> Choose the command button twice.</li> Switch to Microsoft Excel.</li> Open the BOOK1.XLS workbook.</li> Try to delete the new worksheets. You will receive the error message stated earlier in this article.</li></ol>

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.

<div class="references_section">