dcsimg
Results 1 to 12 of 12

Thread: [RESOLVED] VB.NET EXCEL - Find + Copy

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Posts
    25

    Resolved [RESOLVED] VB.NET EXCEL - Find + Copy

    Hi,

    Here is what I am attempting to accomplish.

    Basically, I have an excel file (not opened) which has a lot of data.
    For example:
    Column A Column B
    001 A
    002 B


    So I have a textbox that the user will enter a number (Like in the first column (A))...

    Basically I need the script to find (in the excel file)the specified number in the textbox and if possible to copy the whole row and put them into strings, or arrays so I can then use them for something else...

    And if it's even possible, to not take all the columns but just a few. Like A, D, and F for example.

    I appreciate the help in advance!

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,523

    Re: VB.NET EXCEL - Find + Copy

    The following example works against a sheet where the first row has field names
    Code:
    Private Sub DemoWhere(ByVal FirstName As String)
    
        Dim dt As New DataTable
        Dim FileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SampleData.xlsx")
    
        Using cn As New OleDb.OleDbConnection With
            {
                .ConnectionString = ConnectionHelper.ConnectionString(FileName, "YES")
            }
            
            Using cmd As New OleDb.OleDbCommand With
                {
                    .CommandText = "SELECT FirstName, LastName FROM [Sheet2$] WHERE FirstName=@FirstName",
                    .Connection = cn
                }
    
                cmd.Parameters.AddWithValue("@FirstName", FirstName)
                Try
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    If dt.Rows.Count > 0 Then
                        Dim Row As DataRow = dt.Rows(0)
                        Console.WriteLine(String.Join(",", Row.ItemArray))
                    Else
                        Console.WriteLine("Not located")
                    End If
    
                Catch ex As Exception
                    '
                    ' You decide how to handle failure
                    '
                    Console.WriteLine(ex.Message)
                End Try
            End Using
        End Using
    
    End Sub
    The following is used above to create the connection string
    Code:
    Public Module ConnectionHelper
        Public Function ConnectionString(ByVal FileName As String) As String
            Dim Builder As New OleDb.OleDbConnectionStringBuilder
            If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                Builder.Add("Extended Properties", "Excel 8.0;IMEX=2;HDR=No;")
            Else
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                Builder.Add("Extended Properties", "Excel 12.0;IMEX=2;HDR=No;")
            End If
    
            Builder.DataSource = FileName
    
            Return Builder.ConnectionString
        End Function
        Public Function ConnectionString(ByVal FileName As String, ByVal Header As String) As String
            Dim Builder As New OleDb.OleDbConnectionStringBuilder
            If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                Builder.Add("Extended Properties", String.Format("Excel 8.0;IMEX=1;HDR={0};", Header))
            Else
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                Builder.Add("Extended Properties", String.Format("Excel 12.0;IMEX=1;HDR={0};", Header))
            End If
    
            Builder.DataSource = FileName
    
            Return Builder.ConnectionString
        End Function
    End Module
    The code above gets the highlighted row
    Name:  53.png
Views: 137
Size:  5.0 KB

    Using the same data, different sheet where there are no column headers
    Code:
    Private Sub DemoWhere(ByVal FirstName As String)
    
        Dim dt As New DataTable
        Dim FileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SampleData.xlsx")
    
        Using cn As New OleDb.OleDbConnection With
            {
                .ConnectionString = ConnectionHelper.ConnectionString(FileName)
            }
    
            Using cmd As New OleDb.OleDbCommand With
                {
                    .CommandText = "SELECT F1 As FirstName, F2 As LastName FROM [Sheet3$] WHERE F1=@FirstName",
                    .Connection = cn
                }
    
                cmd.Parameters.AddWithValue("@FirstName", FirstName)
                Try
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    If dt.Rows.Count > 0 Then
                        Dim Row As DataRow = dt.Rows(0)
                        Console.WriteLine(String.Join(",", Row.ItemArray))
                    Else
                        Console.WriteLine("Not located")
                    End If
    
                Catch ex As Exception
                    '
                    ' You decide how to handle failure
                    '
                    Console.WriteLine(ex.Message)
                End Try
            End Using
        End Using
    
    End Sub
    Anyway you look at it you can access fields via Row.Field(Of string)(0) or Row.Field(Of string)("FieldName) and if the data is not a string convert it via .ToString or CStr

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Posts
    25

    Re: VB.NET EXCEL - Find + Copy

    That's even better! I'll give it a try. Thank you very much!

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Posts
    25

    Re: VB.NET EXCEL - Find + Copy

    Hi,

    Could you, if you don't mind explain your code a little so I can understand a bit more the process?

    For example, how could I choose the headers on row 2 since in my spreadsheet, the headers are actually on the second row.

    In your code, in order to search for someone, will keep using your example, I would need to call
    Code:
    DemoWhere("StringtoSearch")
    So example
    Code:
    DemoWhere("Bob")
    would return the 3rd line?

    Is that how it works or am I completely missing the point?

    And from my understand, it takes all the info and put them into an array? How am I using it? Let say I want a label with the column # 3 result?

    Thanks for the help, I appreciate it, kinda new with this.

  5. #5
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,523

    Re: VB.NET EXCEL - Find + Copy

    The image goes with code block 1 where the first row has column names. The image below goes with the second example where the first row is data

    Name:  32.png
Views: 122
Size:  4.5 KB

    Both return a DataRow, from the DataTable

    Code:
    Try
        cn.Open()
        dt.Load(cmd.ExecuteReader)
        If dt.Rows.Count > 0 Then
            Dim Row As DataRow = dt.Rows(0) ' This is our data returned
            Console.WriteLine(String.Join(",", Row.ItemArray))
        Else
            Console.WriteLine("Not located")
        End If
    
    Catch ex As Exception
        '
        ' You decide how to handle failure
        '
        Console.WriteLine(ex.Message)
    End Try
    The Console.WriteLine is purely for allowing you to see the results from the IDE Output window.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Posts
    25

    Re: VB.NET EXCEL - Find + Copy

    Alright sweet, makes all sense.

    If I want to go by column headers but they are not on the first row but on the second one, will your first code work?

  7. #7
    Hyperactive Member
    Join Date
    Mar 2012
    Posts
    311

    Re: VB.NET EXCEL - Find + Copy

    Kevin's code is essentially what I'd suggest too... You might want to make it into a Function, however, that returns the array for processing instead of doing all of the processing of the array inside of this Sub. As for your question about having the headers on the 2nd row, you'll just have to change the range / "table" of the query to specify that:
    Code:
    .CommandText = "SELECT FirstName, LastName FROM [Sheet2$A2:F] WHERE FirstName=@FirstName"

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Posts
    25

    Re: VB.NET EXCEL - Find + Copy

    Hi,

    so I am trying with the script (the header one) and without success. It doesn't give me an error.

    From what I see

    When it tries
    Code:
    cn.Open()
    It goes right to

    Code:
    Catch ex As Exception
    I looked into it and I found this, I am not sure if the info is relevant or not but:

    Name:  error.png
Views: 125
Size:  7.8 KB

    So at that point it goes to Catch ex As Exception

    Not sure where to look now.

  9. #9
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,523

    Re: VB.NET EXCEL - Find + Copy

    Try setting IMEX to 0 or 2 in the code I supplied for creating the connection for the XLSX section which is currently set to 1, see if that helps.

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Posts
    25

    Re: VB.NET EXCEL - Find + Copy

    Hi,
    You're original code worked. I just had to install the 2007 access something (can't recall the name right now) and it works really good.

    There is only one thing, the workbook I am using is completely messed up for some reasons (been years)... tho if I copy the sheet to another workbook, it's going to work without issues.

    I had trouble copying the sheet. what I searched, I tried a few possibility they gave us but there was always an error.

    Could you help me out with the code to copy the first sheet on an excel workbook to copy to a new workbook?

    I tried with Interlop but not sure where to head to.

    I get the create the excel app and the workbooks but the copy / paste part does not seem to work for me.
    Last edited by TheOrion666; Feb 27th, 2015 at 08:24 AM.

  11. #11
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,523

    Re: VB.NET EXCEL - Find + Copy

    Hello,

    Never copied a sheet to another workbook before with straight Excel code, only with Aspose Excel library. Normally I would tinker with this but my brain is still foggy for major surgery last month and have difficulties staying on task.

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Posts
    25

    Re: VB.NET EXCEL - Find + Copy

    Hi,

    I will look into Aspose Excel library.

    No problem, I hope your surgery went good. I hope all is doing well and wish you the best recover possible.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width