Microsoft Excel - excel template very large after saving

Asked By dvey
27-Sep-11 10:26 AM
Hi all,

i have an excel template (xlsm.) 
that is running with a setup
and has vba code and VSTO(c#) code too.
the problem is that after saving the file,-
without adding any more date,
the file that was 1.53 mb becomes 6.89 mb.
does anyone knows what can i do???
thanks.
  Suchit shah replied to dvey
27-Sep-11 10:46 AM
Do you know the last row and column in the spreadsheet that should have data in (the active part of the spreadsheet.

If so, Hold the Ctrl key down on your keyboard and press the End key once on your keyboard and then let the Ctrl key go. This should take you to the last active cell in the spreadsheet.

What is the cell reference that the cell pointer has gone to ?

If the cell pointer goes to where you expected, then no problem.

However, lets say you expected the last cell to be V200 but the cell pointer goes to IV2000, then that may be part of the problem.

What I would do in this situation, is select all the columns from W to IV and delete the columns and then select row 201 to 65536 (if that is the last row in your spreadsheet) and delete all those rows.

Having done that Save As and give the file a new name and save it and close the file.

Then open the file with the new name and do a Ctrl + End to see if the cell pointer goes to V200.
If it does, then you have reduced the size of the file.

It may well be that it is not the complete solution, but at that point, you will not have eronious data in parts of the spreadsheet you didn't expect.

Anyway have a go at that, and see if it makes any difference and come back to the thread and let us know. Then we can go further.
  dvey replied to Suchit shah
27-Sep-11 10:54 AM
thanks for your quick replay.
i dont think this is the problem
because also sheets that have some 'extra' cells,
the extra cells are not more then a few hundreds
and not all sheets has extra cells.
any other idea??

thank you
  Radhika roy replied to dvey
27-Sep-11 11:03 AM

In order to do this you will need to use the Open XML SDK 2.0. Below is a snippet of code that worked for me when I tried it:

byte[] byteArray = File.ReadAllBytes("C:\\temp\\sa123.xltx");
using (MemoryStream stream = new MemoryStream())
{
    stream.Write(byteArray, 0, (int)byteArray.Length);
    using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
    {
       // Change from template type to workbook type
       spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
    }
    File.WriteAllBytes("C:\\temp\\sa123.xlsx", stream.ToArray()); 
}

What this code does is it takes your template file and opens it into a SpreadsheetDocument object. The type of this object is Template, but since you want it as a Workbook you call theChangeDocumentType method to change it from a Template to a Workbook. This will work since the underlying XML is the same between a .xltx and a .xlsx file and it was just the type that was causing you an issue.


How to troubleshoot errors when you save Excel files


  dvey replied to Suchit shah
27-Sep-11 11:07 AM
i tried deleting the rows and the columns-
but when pressing ctrl+end it still goes to the un expected cell.
??
  NICK NICK replied to dvey
27-Sep-11 11:55 AM
Try this:

  1. Open the Excel Template
  2. Then save the Excel Template in html format
  3. Then save the html document into xls but as another name
  Suchit shah replied to dvey
27-Sep-11 12:24 PM
Your file size can grow as you modify the VB portion of the workbook. Especially, if you're doing alot of adding / deleting of forms.

My suggestion is this. Make a new workbook and copy in the modules. That should get you back down to a file size you believe is correct
  dvey replied to Suchit shah
28-Sep-11 01:33 AM
thanks for all replies.
the point is that i have developed an excel template
that has also VBA code and also VSTO code behind.
i release a version, and then the users copy the xlsm file into there computer,
and use it,
so when i release the file- the size is 1.53 mb.
this is the size also after users are copying the file on their computer.
the problem starts when they open their file and save it-with out doing any changes,
it becomes almost 7 mb,
so the change i have to do must be in the source file.
what can cause this weird  problem???
thank you.


Create New Account
help
the dataReader to read it SqlDataReader reader; byte[] a = (byte[])reader.GetValue(0); http: / / msdn.microsoft.com / en-us / library / a1904w6t.aspx hi, Just read binary data and then convert it buffer / / Response.ClearContent(); Response.ClearHeaders(); Response.ContentType = "application / msword" ; / / word type / / Response.ContentType = "application / ms-excel"; / / excel type / / Response.ContentType = "application / octet-stream"; / / pdf type Response.BinaryWrite(buffer); Response.Flush(); Response.Close this Information is helpful! Hi Bikash, The easiest way is to use the convenient File.WriteAllBytes method which writes a byte array directly to the file name specified. var fileName = Path.ChangeExtension(Path.GetTempFileName(), "txt"); File.WriteAllBytes(fileName, b); Process.Start(fileName); Note that in this example I have assumed that you
CHANGE CASE COMMAND IN MICROSOFT EXCEL Excel Microsoft Word contains the command of CHANGE CASE which is very useful, but Microsoft Excel does not contain this command due to which its too much difficult as rewrite the whole content of a cell. Cant this command be available in Microsoft Excel too, I think its not hard for Microsoft Corporation. Or if this command is available
Microsoft Excel Microsoft Excel (full name Microsoft Office Excel ) is a spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X . It features calculation, graphing tools, pivot tables and a macro
Microsoft Excel Microsoft Excel (full name Microsoft Office Excel ) is a spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X . It features calculation, graphing tools, pivot tables and a macro