• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 591
  • Last Modified:

export to excel

I am importing from excel using the code below.  If a row fails my validation, I want to export those rows in question to an excel file with the same name adding _fail.  So myfile.xls will export to myfile_fail.xls.

I have seen comments on how to export to excel, but I wanted to just update this file and save it under a different name.  If they decide to make the header a certain font, or color, I would like my new file to be the exact same format with the successful rows removed.  I do not want to care about how the file is set up.

Here is the code for importing that I am using.

Function ReadDataFromExcel(ByVal excelfilename As String) As DataSet
        Dim ds As New DataSet
        Dim da As OleDbDataAdapter
        Dim conn As OleDbConnection

            conn = New OleDbConnection( _
                  "provider=Microsoft.Jet.OLEDB.4.0; " & _
                  "data source=" & excelfilename & "; " & _
                  "Extended Properties=Excel 8.0;")

            da = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", conn)



        Catch ex As Exception
            If conn.State = ConnectionState.Open Then
            End If
        End Try
    End Function
  • 3
  • 2
1 Solution
I would not use OleDbConnection for this.

You should be using a COM reference.  That way, you have total control over Excel.  The reference is called "Microsoft Excel XX Object Library".  Where XX is the version that you have installed on your machine.

See this article.  It should get you going:

jackjohnson44Author Commented:
Thanks, but I really don't want to have to specify columns because I don't know what they will be.  I pretty much want to edit the excel file I am working with and save it under another name.  Is this possible?
I would still use the COM object.

Anything you can do in Excel can be done via the COM object including getting the file name and saving to another file.

Also, "SELECT * FROM [Sheet1$]" would be the same as iterating over the UsedRange object.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

jackjohnson44Author Commented:
The link you posted does not show how to open a file, edit it and save it.  How would I open a file, delete rows 3 and 5 and save it under a different name?  I do not want to have to worry about font, spacing and headers.  I want an exact copy of my current file with a few lines missing.  Thanks
It's really easy.  Here's some code to get you going.  This example opens one file, removes row 3 and saves under a new filename.

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

       private void SimpleTestButton_Click(object sender, EventArgs e)
            String strOldFilePath;
            String strNewFilePath;
            Excel.Application oXL;
            Excel.Workbook oWb;
            Excel.Worksheet oWs;
            object missing = Type.Missing;

            strOldFilePath = "C:\\Temp\\test1.xls";
            strNewFilePath = "C:\\Temp\\test2.xls";

            oXL = new Excel.Application();
            oXL.Visible = true;
            oWb = oXL.Workbooks.Open(strOldFilePath,
                missing, missing, missing, missing, missing, missing, missing,
                missing, missing, missing, missing, missing, missing, missing);

            oWs = (Excel.Worksheet)oWb.Worksheets[1];

            Excel.Range row = oWs.get_Range("A3", missing);

                Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing,
                false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                missing, missing, missing, missing, missing);

Also, here's the link to the entire Excel API reference:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now