Nope. I just do this for the glory. 
Hopefully it will get fixed but at least there’s a work around.
Can’t it be because the file is saved but not closed? In that case, LO shows me a dialogue about it. I still wonder why LO doesn’t allow importing or reviewing a saved but opened Excel file. All other formats do not have this restriction.
About the File / Insert method for saving Excel formatting as promised in the import dialog.
Works:
- font type and size;
- text alignment;
- cell merging;
- predefined number format;
- predefined background color;
- reference formulas (including SUM(), IF(), SUBTOTAL(), and others).
Does not work:
- cell border thickness and coloring (even “non-existent” borders are displayed) - very annoying;
- predefined row heights and column widths - adjustable manually in LO;
- hiding columns, rows - should be avoided;
- partially changed cell content formats - can be adjusted manually in LO;
- special formats (Accounting, Custom, etc.) or conditional formating, which change formats based on cell contents - unpleasant, but can be adjusted manually in LO;
- filter on fly (of course).
Some illustrations of what has just been said (top - the original look, bottom - the look of the imported file):
When making manual changes in LO, keep in mind that they will disappear at the next table update.
If this is taken into account, then it is very good to use Excel file import in LO.
@ingeniousPV, kamambers might be on something. I had been trying to insert your spreadsheet while it was open in Calc. With it not open I can insert it just fine. Good catch @kamambers.
1 Like
While that is great information to know, unfortunately, LO 2020 is doing the same thing despite the file being closed or not --LO just “thinks” for a second and fails to produce a prompt. That is very odd that you can insert it but I can’t. It seems you’re using the same operating system as me, which makes it even odder.
It was a little slower than I usually see but then I’ve never inserted such a large spreadsheet file.
This is the way it looked with the first sheet.
Eureka! I figured it out. I have a protected sheet in the workbook. I removed the sheet from the workbook and it works fine for me now.
Interesting. I hadn’t looked for a protected sheet. Which sheet?
I wonder how Microsoft Office works with the OS behind the scene. @DaveR seems to use LibreOffice, and it might just not respect sheet protection. Did you, Dave insert the file directly after receiving it or only after opening and saving it in LibreOffice?
I opened it first to look at it but didn’t save it from Calc before trying to insert it. It wasn’t until after I tried inserting it while it wasn’t open in Calc that I had success.
I spoke too soon. When I deleted the giant protected worksheet (it’s the tab called “Module Data”), it was able to be inserted into an LO file. I then unprotected it instead of deleting it… and LO didn’t like it. I tried to convert all the data values to “General”. LO still didn’t like it.
It’s a very large tab on the spreadsheet. About 40 columns and 26,000 rows. I use it mainly as a giant database upon which the rest of the workbook draws from.
I’m experimenting with protected worksheets. I failed to generate the error. Even the password-protected worksheet imported perfectly into the LO.
The file, part of which I showed in my previous post, has 73 columns and 3792 rows per worksheet. Also a large amount of data, but this did not prevent the import of any part of this worksheet.
Now I created a worksheet with 1107264 active cells, which is more than the worksheet you mentioned. It took 1 minute for LO to show the import dialogue. But showed! It has now been 30 minutes since LO did not respond because I asked to import the large worksheet with Excel formatting completely. I believe that at some point the import would have ended because there was no indication that another application on my computer would not respond. But my patience ended.
I repeated this experiment, only this time I selected a small amount of data from the whole extensive worksheet. Data was imported in a flash.
My opinion is that the cause of the import problem you are experiencing is not the protection of the worksheet, but the large amount of data. I assume that there is some undocumented limit (only SU Team knows) beyond which the program will not be able to handle the amount of data on any computer. However, I am more inclined to think that it depends on the resources of each computer (the amount of RAM allocated to the program, the amount of hard disk space for creating temporary files, etc.). Therefore, we fail to generate the error with the same results.
By the way, I do not recommend storing large amounts of structured data as Excel spreadsheets. Access (or some other database management system) tables are much more versatile for this purpose.
1 Like
Thank you for taking your time to conduct these experiments.
The workbook I have has about 26 tabs. It’s only that one tab that has a lot of data. I don’t need to import data from that worksheet tab. I would be importing data from other tabs that have worksheets which are very small. I was hoping I could insert the workbook and then select one those small worksheets.
The laptop I’m using is quite a powerhouse (in my opinion): Intel Core i7 processor @ 2.7GHz, 16 GB of RAM.
You piqued my interest w/ the mention of Access. I’ve never used it. Can Excel reference data from Access?
Yes, by linking the Access database table as the data source. I don’t do that, so I won’t be able to explain all the options in nuances. In fact, I mainly work with Access databases, but I only export selected and ordered data to Excel as the end result. Sometimes the other way around, but rarely.
For example, here the first two columns contain data imported from an Access database table, and the third I created myself using the CONCATENATE () function. These are country international phone codes.
How does that work? When I copy and paste, the numbers file is an image in Layout. It is no longer a spreadsheet.
When I try to insert the .xslx file, nothing happens…
I posted that over two years ago, and now when I try copy and paste I only get an image too. Trying to figure out how I did it last time.
Surely something must happen when you try using File>Insert to insert the .XLSX file. Can you share both the spreadsheet and LO files?
thanks, Dave, I will when I get a chance post deadline