Excel.Interop Not Saving Column Width Changes

[Guest]

Hi,

I need to open an Excel file using Interop, resize a column and then save
the file, insuring that the worksheet retains the newly resized column width.
As I step through the code all is working fine. But when I reopen the file
the new column width has not been saved. If I open the file manually, resize
the column, then save, the file retains the new column width. Any
suggestions? I should also add that I tried reading the column width after
the AutoFit was called and then explicitly set the column width to that
value, then I saved the file This also did not work.

Here is the code I used. I have done this in Winforms.

Thanks for your help.
Mark


using Excel=Microsoft.Office.Interop.Excel;

namespace ExcelWindowsTest
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application excel;
Excel.Workbook book;
Excel.Worksheet sheet;
Excel.Range range;

try
{
excel = new Excel.Application();
excel.Visible = true;

book = (Excel.Workbook)(excel.Workbooks.Add(@"C:\Documents
and Settings\Mark\My Documents\Visual Studio
2005\Projects\ExcelWindowsTest\ExcelWindowsTest\PortDeliveryWorkBook.xls"));
excel.DisplayAlerts = false;
sheet = (Excel.Worksheet)book.ActiveSheet;
range = (Excel.Range)sheet.Columns["A", Type.Missing];
range.AutoFit();

book.Save();
excel.Quit();
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage,
theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage,
theException.Source);

MessageBox.Show(errorMessage, "Error");
}
}

 

[Doug Glancy]

Not sure if it will help but try:

range.EntireColumn.AutoFit

Also I don't know if it matters that you are using an Excel reserved name
"Range" as a variable name, but I'd try changing it to "Rng" or something
(also maybe for "Excel" and "Sheet").

hth,

Doug

 

[Guest]

I tried renaming the variables and using range.EntireColumn.AutoFit.
Unfortunately, this did not work.

Perhaps Excel.Interop just can't do this. I do not know. If anyone has
another suggestion it would be greatly appreciated.

Thanks for your time.

Not sure if it will help but try:

range.EntireColumn.AutoFit

Also I don't know if it matters that you are using an Excel reserved name
"Range" as a variable name, but I'd try changing it to "Rng" or something
(also maybe for "Excel" and "Sheet").

hth,

Doug

Hi,

I need to open an Excel file using Interop, resize a column and then save
the file, insuring that the worksheet retains the newly resized column
width.
As I step through the code all is working fine. But when I reopen the
file
the new column width has not been saved. If I open the file manually,
resize
the column, then save, the file retains the new column width. Any
suggestions? I should also add that I tried reading the column width
after
the AutoFit was called and then explicitly set the column width to that
value, then I saved the file This also did not work.

Here is the code I used. I have done this in Winforms.

Thanks for your help.
Mark


using Excel=Microsoft.Office.Interop.Excel;

namespace ExcelWindowsTest
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application excel;
Excel.Workbook book;
Excel.Worksheet sheet;
Excel.Range range;

try
{
excel = new Excel.Application();
excel.Visible = true;

book = (Excel.Workbook)(excel.Workbooks.Add(@"C:\Documents
and Settings\Mark\My Documents\Visual Studio
2005\Projects\ExcelWindowsTest\ExcelWindowsTest\PortDeliveryWorkBook.xls"));
excel.DisplayAlerts = false;
sheet = (Excel.Worksheet)book.ActiveSheet;
range = (Excel.Range)sheet.Columns["A", Type.Missing];
range.AutoFit();

book.Save();
excel.Quit();
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage,
theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage,
theException.Source);

MessageBox.Show(errorMessage, "Error");
}
}