locked
How to save a excel file in VB.net and not overwriting it RRS feed

  • Question

  • User365103270 posted

    Hi all, i need to know how to save a file by not overwriting it, i want to append data to it if the file exist. May i know how it can be done? My code is as followed:

    I do not want to overwrite new data to it and i want to append new data to the old data.      

    Can anyone assist me? Thank you very much.

    Dim oExcel As Microsoft.Office.Interop.Excel.Application

    Dim oBook, oBook1 As Microsoft.Office.Interop.Excel.Workbook

    Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet

    'Start a new workbook in Excel.

            oExcel = New Microsoft.Office.Interop.Excel.Application

    oBook = oExcel.Workbooks.Add

    oBook1 = oExcel.Workbooks.Add

    'Add data to cells of the first worksheet in the new workbook.

            oSheet = CType(oBook.Worksheets(1),

            Microsoft.Office.Interop.Excel.Worksheet)

            oSheet.Name ="Daily Attendance"

            oSheet.Range("A1").Value = "Department"

            oSheet.Range("B1").Value = "EmployeeID"

            oSheet.Range("C1").Value = "Name"

            oSheet.Range("D1").Value = "SystemDate"

            oSheet.Range("E1").Value = "SystemTime"

            oSheet.Range("F1").Value = "Status"

            oSheet.Range("A1:B1:C1:D1:E1:F1:G1").Font.Bold = True

            oBook.SaveAs("H:\Users\Tester\Desktop\HelloWorld" & "23" & ".xlsx")

            oSheet =Nothing

            oBook =Nothing

            oExcel.Quit()

            oExcel =Nothing

    Sunday, May 25, 2014 11:27 PM

Answers

  • User1140095199 posted

    Hi,

    additional details added:

    i want to append data to it when the file is available and create a new excel file if the file does not exist...

    may i know how to do that?

    Have you considered using OLEDB?

    Just need to use simple insert , update and delete statements. Refer to the example below:

        protected void InsetBtn_Click(object sender, EventArgs e)
        {
            string sexcelconnectionstring = "";
            string path = Server.MapPath("~/Employee.xlsx");
    
            //Use this if .xls
            // sexcelconnectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            //Use this if .xlsx
            sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes\"";
    
            using (OleDbConnection conn = new OleDbConnection(sexcelconnectionstring))
            {
                conn.Open();
                string query = "insert into [Sheet1$] values(9999,'John','Sales','NO')";
                OleDbCommand ocmd = new OleDbCommand(query, conn);
                ocmd.ExecuteNonQuery();
            }
    
        }

    For update use a UPDATE Query:

                string query = "update [Sheet1$] set MC='Yes' where EmployeeID=8888";
    

    Select Query:

    string query = "SELECT * FROM [Sheet1$]";

    Hope it helps!

    Best Regards!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 27, 2014 1:27 AM

All replies

  • User365103270 posted

    additional details added:

    i want to append data to it when the file is available and create a new excel file if the file does not exist...

    may i know how to do that?

    Thanks!

    Monday, May 26, 2014 1:27 AM
  • User1140095199 posted

    Hi,

    additional details added:

    i want to append data to it when the file is available and create a new excel file if the file does not exist...

    may i know how to do that?

    Have you considered using OLEDB?

    Just need to use simple insert , update and delete statements. Refer to the example below:

        protected void InsetBtn_Click(object sender, EventArgs e)
        {
            string sexcelconnectionstring = "";
            string path = Server.MapPath("~/Employee.xlsx");
    
            //Use this if .xls
            // sexcelconnectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            //Use this if .xlsx
            sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes\"";
    
            using (OleDbConnection conn = new OleDbConnection(sexcelconnectionstring))
            {
                conn.Open();
                string query = "insert into [Sheet1$] values(9999,'John','Sales','NO')";
                OleDbCommand ocmd = new OleDbCommand(query, conn);
                ocmd.ExecuteNonQuery();
            }
    
        }

    For update use a UPDATE Query:

                string query = "update [Sheet1$] set MC='Yes' where EmployeeID=8888";
    

    Select Query:

    string query = "SELECT * FROM [Sheet1$]";

    Hope it helps!

    Best Regards!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 27, 2014 1:27 AM
  • User365103270 posted

    thanks it helps!

    Tuesday, May 27, 2014 9:29 AM