Forum Discussion
Hyperlinks - how much do they change the size of a file
Hi Haytham,
It looks strange for me. If unzip your Before file its structure as in attaches before.txt file. Biggest file here which describes the sheet is sheet1.xml. If open it, all what defines the content with hyperlinks is at very bottom of the file
<hyperlinks><hyperlink ref="A1" r:id="rId1" xr:uid="{B7B34BD7-8C51-483C-B6EE-3A89EFD0E69A}"/>
<hyperlink ref="A2:A10000" r:id="rId2" display="https://techcommunity.microsoft.com/" xr:uid="{8583AAC1-002D-49A8-B3C5-A3CEE3F36A01}"/></hyperlinks>
Value of each row is the reference on resource defined in tiny resource file (between <v> and </v>) and looks like
<row r="10000" spans="1:1" x14ac:dyDescent="0.35"><c r="A10000" s="1" t="s"><v>0</v></c></row>
File After is exactly the same except small block with hyperlinks at the bottom of the file.
To compare, I generated another file with 3 different hyperlinks repeat in 10 cells each. Entire block which describes them is
<hyperlinks><hyperlink ref="A1" r:id="rId1" xr:uid="{65A09A39-A823-4B29-831D-35135F8B94C8}"/>
<hyperlink ref="A2:A10" r:id="rId2" display="http://techcommunity.microsoft.com" xr:uid="{445541D1-C4DC-4651-A162-47AE45477BCF}"/>
<hyperlink ref="A11" r:id="rId3" xr:uid="{ED775096-F6A1-4801-9196-FE06349F96F8}"/>
<hyperlink ref="A12:A20" r:id="rId4" display="www.microsoft.com" xr:uid="{E07D5B65-8D06-4F4F-B908-19ADD0C32BEA}"/>
<hyperlink ref="A21" r:id="rId5" xr:uid="{D978AABC-E1CD-4864-811E-4A8DC1F21E56}"/><hyperlink ref="A22:A30" r:id="rId6" display="https://vivaldi.com/" xr:uid="{A75F23EE-CA26-477D-839C-A9B8857EE103}"/></hyperlinks>
This block is 3 times bigger since we have 3 different hyperlinks instead of only one.
Oops, txt files are not allowed to attach. Zipped.
Hi Sergei,
Now I know where the file sheet1.xml came from!
I'm not an XML language expert and how it is used in Excel files, but after reading this http://professor-excel.com/xml-zip-excel-file-structure/, I admit that the code I mentioned before doesn't represent the actual structure of the Excel file.
What described in this article is actually the structure of the Excel file and what happened behind the scenes!
But however, the subject is not as you think!
What I did in the Before file was holding the fill handle in the first cell and then dragging it until I reached the cell A10000.
This is why the definition is uniform across the range.
But this definition may differ depending on how you create the links.
If you enter the links in the cells (cell after cell) without using the fill handle, the definition will appear as follows:
NOTE: You will find this code spreads horizontally in the single line. (For clarification, I've moved each hyperlink in a separate line)
You will notice that the link is the same in the workbook, but after you unzip it you will find it defined 15 times, and it's not visible here, it appears to be encrypted on every line, and has a unique ID!
This is because I entered the links manually without using the fill handle!
But the interesting fact is that each link in the range A1:A15 is an instance of the parent link which is located in a separate file called sharedStrings.xml and the link is counted 15 times in this file.
What do you think of this?
Some people may think that this subject has taken up more discussion than it deserves!
In fact, it is fun to dive into the Excel to this extent and learn all these stuff!
Thank you
Regards