Component marking database has hit the 64k line limit of pre-2010 excel.
When I try to work on the spreadsheet using post-2010 excel, the program still chokes at the same line count while boasting 10^6 lines in it's spec and 'help' files.
The error is a warning that the program cannot dump non-vacant lines to permit insertion of the new lines.
I'm dealing with .xls file extensions. That IS the file format as far as I'm aware.
It is expected to be handled by anyone with a spreadsheet program. Basically text files, sequenced alpha-numerically (or numeri- alphabetically by excel in practice)to be sorted, sequenced, searched or manipulated under the end user's preferences, for whatever purpose they want.
I'm running into rogue vendors who issue part marking numbers of
3chars +, generating >16k variations for a single part function with fixed incremental factory adjustments. I'll bet they havent actually shipped 100 of these variations.
I'm tempted to give them their own private page, that nobody will ever look at. Otherwise it's just noise.
I think that is the old format. Isn't .xlsx the new format?
Microsoft is the one who screwed the pooch years ago when they switched formats. The old versions of the programs can't read the new file formats. That was when I started using LibreOffice to open the .xlsx files and convert them to .xls format.
After a bit I thought, why not try using LibreOffice and it worked just as well as Excel other than a few compatibility issues with embedded drawings. Eventually they fixed that.
I do still miss Visio though. That was a great tool that MS bought.
--
Rick C.
+ Get 5,000 miles of free Supercharging
You will have to resave it in the native format of post 2007 Excel format .xlsx or .xlsm if it contains macros. Then and only then the line limit may be lifted if you are very lucky. Legacy spreadsheets and Word documents sometimes develop interesting quirks when moved over.
The one I am most familiar with is exponential growth in size each time the hybrid new document is saved as orphanned metadata accumulates. You may get warnings when you save it in the new format - pay attention to them to be sure none of them affect features you are using.
Once you make it greater than 2^16 lines it will only work on those spreadsheets that support that functionality.
I've avoided the .xlsx file format and regularly convert it back to .xls before working on it. Initially this was because of the apparent file size bloat and lack of backwards compatibility. Just another MS licensing grab contributing to an already moribund monopoly, stomping on the work of previous developers. I was appalled that the new format was organized using hypertext, which seemed likely to introduce a whole new can of worms.
I'll see how libre office goes. If all the reader needs to do, to get the same simple functionality of larger .xls formats, is to dump their MS dependencies, then it may be worth the trouble.
This marking code information already has a section devoted to incorrect, spurious or just plain fraudulent information, weeded out of the available data. The current mfr that generates 16K lines for just one type of factory trimmed part, per physical package type, is probably just going to end up there. It's just noise.
In .xlsx format, in excel 2010, I get the warning that 'the copy and paste areas are not the same size and shape', . . . . when I'm using the 'insert' directive for 5000 lines being moved from the draft sheet to the actual list sheet.
Size and shape have nothing to do with insertions, unless
Rather than copy and paste, I use the data insert / import command, and use the text-input wizard to setup a filter, then save that setting for re-use.
I havent found a "expert blog" that reveals all the Excel problems I've had .
In practice, Excel has limitations, despite its claimed specs. I rely heavi ly on Excel in my projects. My file sizes are quite large, 1 to 10MB. Excel becomes slow, and downright unstable, as the workbook grows large. But mo re indicative of the "stress" is the working memory (which is 10-100x more than the file size. Excel can be very RAM hungry). Have Task Manager runni ng as you open a large file, lo and behold.
I found Excel 2010 to be more faster and stable than 2013 and 2016 versions . (I have not tried newer installed versions, or the cloud-based versions) .
Cells with very long text strings (paragraphs) seem to be biggest challenge to Excel, and really slow it down. Try to keep the amount of text low in cells, and low in general.
Yes, I know Excel wasn't meant to be a "true" database. But it is so flexib le and easy to learn, requires less programming, & so much more accessible to most users. It tends to get integrated into office workflow so much tha t anything else becomes "non standards".
Save files in xlsx or xlsm format. The xlsb files *are* smaller, but I've h ad Excel freeze up on loading larger ones. The xlsb wasn't corrupted, but E xcel 2016 seemed to not know how to open them.
There used to be a reasonably active usenet group for Excel in the comp.microsoft hierarcy. I haven't used it for a while but I did when the ill fated 2007 was dumped on the world with many many bugs.
Strangely I haven't seen much of that but my spreadsheets tend to contain very large arrays of numbers and numerical formulae.
I have seen internal race problems with the charting where if you are doing it from VBA it is possible for Excel on a multiprocesor to attempt to plot data before the axis scaling is well defined. Ends up in a blue screen or locks up completely.
I have not seen too much instability apart from the original 2007. Prior to that 2002 and 2003 were both good vintages.
Embedded images have caused the most grief in the things I have been asked to look at that had gone horribly wrong.
For small databases that involve some computation I'd say Excel was ideal. I use it for our village hall booze stock control with about 50 line items (database would be overkill).
I'd still consider Excel for a few thousand items, above that then a database program is a more sensible approach.
Not experienced that but I can believe it happens. The most common migration problem with large legacy documents Word or Excel that get used on machines with different versions of Excel is a buildup of orphanned metadata that manifests itself as an exponential increase in the filesize each time around the loop. Microsoft used to actually admit to this if you probe the MSKB with exactly the right question:
formatting link
It fails 404 these days. I don't believe it is fixed.
The same query at m.public.excel.programming gets no response, though the news group shows regular activity. There's no guarantee that usenet messages migrate to the source of the posts seen there, or are just trickle-down from an MS message board.
I had a weird thing happen in Excel the other day.
I created a chart in a spreadsheet and copy and pasted just the chart into an email.
Then I kept the email open but did not send it yet.
Then I changed some numbers in the spreadsheet as a what if test and as expected the data on the chart in the spreadsheet changed to reflect the new data. So far so good.
But to my amazement, the chart in the email that I had previously copied and pasted ALSO CHANGED. This is NOT what I wanted or expected.
Apparently the chart in the email is no longer a static object but rather is linked back to the source spreadsheet somehow.
Who would think someone would WANT a feature like that.
Made me wonder that if they could, would they link it back even after I sent the email.
ElectronDepot website is not affiliated with any of the manufacturers or service providers discussed here.
All logos and trade names are the property of their respective owners.