5 replies to this topic

[Xystus777]

Hello,

Right now my program will open up an existing Excel Document. There's something I need help with however. After it adds information to this document, I want it to save it. However, it is always asking me if I want to overwrite it. How can I programmatically make my program overwrite it everytime? I don't want to have to click "Yes" each time. Thanks everyone. Any questions, just ask!

[Xystus777]

Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWB;
        Excel.Worksheet xlWS;
        Excel.Range xlRng;

string workbookPath = "C:\\Documents and Settings\\adavis\\My Documents\\Area51\\T-Numbers Data";

private void addTNumberBtn_Click(object sender, EventArgs e)
        {
            xlWB = (Excel.Workbook)(xlApp.Workbooks.Add(workbookPath));
            xlWS = (Excel.Worksheet)xlWB.ActiveSheet;

            xlWS.Cells[rowIndex + 1, 1] = addTNumberTextBox.Text;
            rowIndex++;

            //center the text in Excel
            excelWS.get_Range("A1", "Z26").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            excelWS.get_Range("A1", "Z26").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;

            //AutoFit
            xlRng = xlWS.get_Range("A1", "Z1");
            xlRng.EntireColumn.AutoFit();

            //Save the file to where you specified
            xlWB.SaveAs(workbookPath, Excel.XlFileFormat.xlAddIn, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            xlApp.Visible = true;
        }

[Xystus777]

Oh, and that's using Microsoft Excel Interoperability. Also, at the top, I added:

using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;

[Xystus777]

Got it. I did some more research and I figured it out. I had first add the line:

xlApp.DisplayAlerts = false;

Then I had to change my SaveAs section to:

//Save the file to where you specified
            xlWB.SaveAs(workbookPath, Excel.XlFileFormat.xlAddIn, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            xlApp.Visible = true;

I just had to change one of the Type.Missing to "Excel.XlSaveConflictResolution.xlLocalSessionChanges"

[Xystus777]

Thanks for your help Jordan.