Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel row expansion past 16,000

Posted on 1998-07-15
11
Medium Priority
?
685 Views
Last Modified: 2010-08-05
How do you expand the row limit past 16,000 in Excel 97
0
Comment
Question by:kludwig
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 

Expert Comment

by:ewilson
ID: 1612826
Time to start using Access :(
0
 
LVL 3

Expert Comment

by:shalbe
ID: 1612827
kludwig,

My Excel 97 has 65536 rows by default.

Has yours been 16000 since it was installed or did it change ?

Did you previously have Excel 5.0 installed ?

Is this on all worksheets or just some ?

How many rows in a new workbook ?

Steve
0
 
LVL 3

Expert Comment

by:gpipes
ID: 1612828
Is this the case?

 
PSS ID Number: Q178086
Article last modified on 02-05-1998
 
WINDOWS:97,7.0;
 
WINDOWS
 

======================================================================
---------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Excel 97 for Windows
 - Microsoft Excel for Windows, version 7.0, 7.0a
---------------------------------------------------------------------
 
SYMPTOMS
========
 
When you attempt to save a workbook that contains a link to a large range
of cells (approximately 16,000 cells), the following error message may
appear:
 
   Not enough memory
 
Then, the following error message appears:
 
   Unable to save external link values
 
CAUSE
=====
 
This problem occurs when you save a workbook that contains a link to a
range of cells in a different workbook, and the range is more than 16,000
cells. This problem occurs because Microsoft Excel 97 for Windows cannot
save a workbook that contains a link to an external range of more than
16,000 cells.
 
WORKAROUND
==========
 
To work around this problem, link to multiple smaller ranges instead of one
large range.
 
If you do not need to save a copy of the external link values, you can
disable the option to save a copy of the external link values by following
these steps:
 
1. Switch to the workbook that contains the link formulas.
 
2. On the Tools menu, click Options.
 
3. Click the Calculation tab in the Options dialog box.
 
4. Clear the Save External Link Values option under Workbook Options and
   click OK.
 
STATUS
======
 
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article. We are researching this problem
and will post new information here in the Microsoft Knowledge Base as it
becomes available.
 
Additional query words: XL97
======================================================================
Keywords          : xlloadsave kberrmsg
Version           : WINDOWS:97,7.0;
Platform          : WINDOWS
Issue type        : kbbug
Solution Type     : kbworkaround
=============================================================================
Copyright Microsoft Corporation 1998.


 

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:Victor Spiridonov
ID: 1612829
Are you using a workbook created in previous version of Excel?
0
 

Author Comment

by:kludwig
ID: 1612830
I have a user with a 30,000 record Access database and they are trying to export it to Excel and Excel will only print/display about half the records.  I need to know how to get the other half to display.
0
 
LVL 1

Expert Comment

by:Gene Klamerus
ID: 1612831
Either select a subset via an Access query (from Excel) and/or split the data into several sheets based on some select clause.  There's no reason to not have sheets for subsets of the data (is there)?
0
 

Author Comment

by:kludwig
ID: 1612832
But if Excel 97 has 65,536 rows by default why am I not able to utilize them all?
0
 
LVL 3

Expert Comment

by:shalbe
ID: 1612833
kludwig,

What version of Access are you using to export from ?
What version of Access was the database created in ?
Are you exporting to a file or launching the table in Excel ?
If you are launching it in Excel, try saving as a file and opening in Excel.
Also, what operating system are you running ?

Sorry about all the questions, but the more details you can provide, the more chance someone will have an answer.

Steve
0
 

Expert Comment

by:kenjones
ID: 1612834
XL: Importing Text Files Larger Than 16384 Rows

Last reviewed: July 17, 1998
Article ID: Q120596

The information in this article applies to:

•Microsoft Excel for Windows, versions 5.0, 5.0c •Microsoft Excel for the Macintosh, versions 5.0, 5.0a •Microsoft Excel for Windows NT, version 5.0 •Microsoft Excel for Windows 95, version 7.0 •Microsoft Excel 97 for Windows







SUMMARY

In versions of Microsoft Excel that are earlier than Microsoft Excel 97, text files that contain more than 16,384 rows cannot be opened in their entirety. You cannot open these files because earlier versions of Microsoft Excel are limited to 16,384 rows. If you open a file that contains more data than this, the following error message appears



   File not loaded completely




and the text file is truncated at the row 16,384. However, you can use a macro to open the file and automatically break the text into multiple worksheets.

NOTE: Microsoft Excel 97 has a limit of 65,536 rows.





MORE INFORMATION

The following sample macro prompts you for a text filename, then opens the file into memory. If the number of rows is larger than the Microsoft Excel worksheet limit of 16,384, the macro breaks the file into multiple worksheets. This macro applies only to files you saved as text files and does not apply to any other file formats. The macro will not work with database file formats.

Note that because this is a macro, using it may be significantly slower than clicking Open on the File menu.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:



   http://www.microsoft.com/supportnet/refguide/

   'All lines that begin with an apostrophe (') are remarks and are not
   'required for the macro to run.

   Sub LargeFileImport()

      'Dimension Variables
      Dim ResultStr As String
      Dim FileName As String
      Dim FileNum As Integer
      Dim Counter As Double
      'Ask User for File's Name
      FileName = InputBox("Please enter the Text File's name")
      'Check for no entry
      If FileName = "" Then End
      'Get Next Available File Handle Number
      FileNum = FreeFile()
      'Open Text File For Input
      Open FileName For Input As #FileNum
      'Turn Screen Updating Off
      Application.ScreenUpdating = False
      'Create A New WorkBook With One Worksheet In It
      Workbooks.Add template:=xlWorksheet
      'Set The Counter to 1
      Counter = 1
      'Loop Until the End Of File Is Reached
      Do While Seek(FileNum) <= LOF(FileNum)
         'Display Importing Row Number On Status Bar
          Application.StatusBar = "Importing Row " & _
             Counter & " of text file " & FileName
          'Store One Line Of Text From File To Variable
          Line Input #FileNum, ResultStr
          'Store Variable Data Into Active Cell
          If Left(ResultStr, 1) = "=" Then
             ActiveCell.Value = "'" & ResultStr
          Else
             ActiveCell.Value = ResultStr
          End If
          If ActiveCell.Row = 16384 Then
             'If On The Last Row Then Add A New Sheet
             ActiveWorkbook.Sheets.Add
          Else
             'If Not The Last Row Then Go One Cell Down
             ActiveCell.Offset(1, 0).Select
          End If
          'Increment the Counter By 1
          Counter = Counter + 1
      'Start Again At Top Of 'Do While' Statement
      Loop
      'Close The Open Text File
      Close
      'Remove Message From Status Bar
      Application.StatusBar = False

   End Sub




NOTE: The macro does not parse the data into columns. After using the macro, you may also need to use the Text To Columns command on the Data menu to parse the data as needed.

When you run this macro on a Macintosh, and you are attempting to open a file that is on the desktop, the file name must be preceded by the following



   <Hard disk>:Desktop Folder:




where <Hard disk> is the name of your hard disk. Note that there is a space between the words Desktop and Folder.








<Picture: Did this information help answer your question?> <Picture: Yes>    <Picture: No>
 <Picture: It didn't apply>   <Picture>



------------------------------------------------------------------------


Additional query words: 97 8.00 7.00 5.00 5.00a 5.00c import ascii xl97 xl7
xl5
Keywords : xlvbahowto kbcode
Version : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a
Platform : MACINTOSH WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 17, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.
0
 

Author Comment

by:kludwig
ID: 1612835
Thanks for the tech note but I am using Excel 97 and it will only allow 16,000 lines
0
 
LVL 8

Accepted Solution

by:
Answers2000 earned 200 total points
ID: 1612836
In Access export the file into CSV format.
In Excel 97 load this file.

I successfully can read a CSV with 22,000 rows into Excel 97
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question