Question Understand Code That Uses Microsoft.ACE

[kstoneman10]

I've been giving the following code to search an Excel file for specific data. Currently, this code looks for a date within the worksheet.

My questions are:
1. Why is the "$" needed for sheet name?
2. Why is "F" used in the SQL string?
3. Right now it finds the date in row 3 of the worksheet. How can I search for data in another row?

Imports Microsoft.Office.Interop
Imports System.Data.OleDb

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ExcelCon As OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\test2.xlsx;Extended Properties=""Excel 12.0;HDR=No"";")
        Dim str1 As String
        Dim rs1 As OleDbDataReader
        Dim col As Integer
        For i = 4 To 200 Step 6
            ExcelCon.Open()
            str1 = "SELECT * " & _
                  "FROM [2011_Rollup_2Hx$] " & _
                  "Where F" & i & " ='9-Jul-11'  "

            MsgBox(str1)
            Dim sql1 As OleDbCommand = New OleDbCommand(str1, ExcelCon)
            rs1 = sql1.ExecuteReader
            rs1.Read()
            If rs1.HasRows = True Then
                col = i
                i = 200
            End If
            MsgBox("found")
            ExcelCon.Close()
        Next


    End Sub
End Class

Any assistance is greatly appreciated.

Kevin

 

[jmcilhinney]

1. That is what identifies a sheet. It's just something that Microsoft decided was the way it was to be done.

2. Because Excel is not a real database, it doesn't have real columns. The columns in a real database have names to identify them. When using Excel, the names F1, F2, F3, etc, are used for the columns.

3. That code is wrong. It's calling Read first and then testing HasRows. If HasRows is False then there's nothing to read, so it should be testing HasRows first and then calling Read if HasRows is True. That said, there's no point even testing HasRows unless you want to do something specific when it's False. If you want to read all the data then you simply use a Do or While loop and keep going until Read returns False. After each time Read returns True, you get the field values from the data reader itself by index. There are lots and lots of examples around of reading data using a data reader.

 

[kstoneman10]

Thanks jmcilhinney for that information. What if I have non-standardized workbook where I have data in some columns and rows as well as blank rows. How can I determine which row the data I'm looking is located? The location is at "Z2608". With coding provided I can find column "Z" but don't know how to determine row 2608.

Any help understanding this is greatly appreciated.

Kevin

 

[jmcilhinney]

ADO.NET is for interacting with databases. Excel is not a database so there are limitations to working with it using ADO.NET. That said, this is part of where the $ symbol comes in. You can select just a range of cells with your query something like this:

SELECT * FROM [Sheet1$B2:Z100]

The $ is actually the separator between the sheet name and the cell range, which I probably should have mentioned last time but I didn;t really think about it because I've never actually used a range that way myself.

 

[kstoneman10]

Thanks again jmcilhinney for that information.

I have a few nore questions about using Dim ExcelCon As OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0

1. I notice that Excel isn't visible. Does that nean the file doesn't need to be opened or is it running in the background but not visible?
2. Can you select a specific range within the workbook and insert/paste informationto it?
3. Can you format cells?

I have a co-worker determined to use this method for creating/updating reports so I want to be able to support them.

Any help understanding this is greatly appreciated.

Kevin

 

[jmcilhinney]

1. Excel is not involved at all. You're using ADO.NET. It's just ADO.NET and the ACE OLE DB provider involved.
2. I don't know. I guess that you could try specifying the sheet and range in an INSERT or UPDATE statement in the same way.
3. No. As I said, ADO.NET is for working with databases, or other data sources as though they were databases. OLE DB has no support for formatting because it's not a database operation. For that you would need to use Excel Automation.

 

[kstoneman10]

Using the coding in my initial post, what would be your recommendation on how to handle zero records returned?

Kevin

 

[jmcilhinney]

Using the coding in my initial post, what would be your recommendation on how to handle zero records returned?

Kevin

I've already answered that. I suggest that you read the information already provided.

 

[kstoneman10]

Thanks for reminding me. I have made the recommended change. Now I get the error message, "No vave given for one or more required parameters". Here is my new code:

Imports Microsoft.Office.Interop
Imports System.Data.OleDb

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ExcelCon As OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\test2.xlsx;Extended Properties=""Excel 12.0;HDR=No"";")
        Dim str1 As String
        Dim rs1 As OleDbDataReader
        Dim col As Integer
        For i = 1 To 60
            ExcelCon.Open()
            str1 = "SELECT * " & _
                  "FROM [2011_Rollup_2Hx$A1:B29] " & _
                  "Where F" & i & " ='Kevin'  "

            Dim sql1 As OleDbCommand = New OleDbCommand(str1, ExcelCon)
            [B]rs1 = sql1.ExecuteReader[/B] 
             If rs1.HasRows = True Then
                rs1.Read()
                col = i
                MsgBox("found at " & i)
                i = 200
            End If
            ExcelCon.Close()
        Next
        MsgBox(" not found")

    End Sub
End Class

Any suggestions?

Kevin

 

[jmcilhinney]

First things first, don't open and close the connection 60 times. You're using the same connection every time so why not just open it once and close it once? Make sure you close the data readers after use.

As for the question, I'm not quite sure what the issue is. Does it work without the range and the WHERE clause? With the range and without the WHERE clause? With the WHERE clause and without the range? This is the sort of thing you do to narrow down where the actual issue is.

 

[kstoneman10]

Below is my updated coding. I works great when it finds the answer I want; however, it still produces an error when it can't find the value I'm looking for. Now I get a different error message "Data type mismatch in criteria expression" in the same location as before (rs1 = sql1.ExecuteReader). Here is the string syntax at the error, "SELECT * FROM [2011_Rollup_2Hx$] Where F32 ='Cody' "

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ExcelCon As OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\test2.xlsx;Extended Properties=""Excel 12.0;HDR=No"";")
Dim str1 As String
Dim rs1 As OleDbDataReader
Dim col As Integer
'Dim i As Integer
ExcelCon.Open()
For i = 1 To 60
str1 = "SELECT * " & _
"FROM [2011_Rollup_2Hx$] " & _
"Where F" & i & " ='Cody' "

Dim sql1 As OleDbCommand = New OleDbCommand(str1, ExcelCon)
rs1 = sql1.ExecuteReader
If rs1.HasRows = True Then
rs1.Read()
col = i
MsgBox("found at " & i)
i = 200
End If
Next
MsgBox(" not found")
ExcelCon.Close()

End Sub
End Class

any suggestions?

Kevin

 

[jmcilhinney]

A data type mismatch means that you are comparing a column value to another value that is a different data type. You're comparing the contents of column F32 to a string so apparently column F32 doesn't contain text values.