Solved

Excel row expansion past 16,000

Posted on 1998-07-15
11
571 Views
Last Modified: 2010-08-05
How do you expand the row limit past 16,000 in Excel 97
0
Comment
Question by:kludwig
11 Comments
 

Expert Comment

by:ewilson
Comment Utility
Time to start using Access :(
0
 
LVL 3

Expert Comment

by:shalbe
Comment Utility
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
Comment Utility
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
 
LVL 7

Expert Comment

by:spiridonov
Comment Utility
Are you using a workbook created in previous version of Excel?
0
 

Author Comment

by:kludwig
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 1

Expert Comment

by:klamerus
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 100 total points
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now