Cycling through Excel using VB 2003 to find duplicates
-
I'm using VB 2003 to extract data from an excel file but I'm having trouble looping through one column to find duplicates and counting how may times a cell value is duplicated :confused:. If any one can please feel free to post. :-D
Swish
You do not need to loop through the column. A more efficient way is to connect to your excel file with OledbConnection and issue a SQL group by statement.
petersgyoung
-
You do not need to loop through the column. A more efficient way is to connect to your excel file with OledbConnection and issue a SQL group by statement.
petersgyoung
I'm connecting to the excel file but having trouble with the SQL group by statement. I'm getting an error when the dataset is being filled by the OLEAdapter; An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in microsoft.visualbasic.dll Below is a sample of the code: Dim ex As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & "data source=" & xlfile & ";" & "Extended Properties=Excel 8.0;" Dim conn1 As New OleDbConnection(ex) Dim dtset1 = New DataSet Dim col1 As String = "sc_typname" '<-----Column to group by Dim MyCommand1 As New OleDbDataAdapter("select * from [flnme$] Group By col1", conn1) MyCommand1.Fill(dtset1) DataGrid1.DataSource = dtset1.Tables(0) conn1.Close() Am I missing any information or how can resolve this error? Swish
-
I'm connecting to the excel file but having trouble with the SQL group by statement. I'm getting an error when the dataset is being filled by the OLEAdapter; An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in microsoft.visualbasic.dll Below is a sample of the code: Dim ex As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & "data source=" & xlfile & ";" & "Extended Properties=Excel 8.0;" Dim conn1 As New OleDbConnection(ex) Dim dtset1 = New DataSet Dim col1 As String = "sc_typname" '<-----Column to group by Dim MyCommand1 As New OleDbDataAdapter("select * from [flnme$] Group By col1", conn1) MyCommand1.Fill(dtset1) DataGrid1.DataSource = dtset1.Tables(0) conn1.Close() Am I missing any information or how can resolve this error? Swish
Please try this: Dim MyCommand1 As New OleDBDataAdapter("select " & col1 & ", count(*) As [Count] from [flnme$] group by " & col1, conn1)
petersgyoung
-
Please try this: Dim MyCommand1 As New OleDBDataAdapter("select " & col1 & ", count(*) As [Count] from [flnme$] group by " & col1, conn1)
petersgyoung
I tried working the sql statement you posted and I still receive the Unhandled Exception Error. I've even tried tweaking it by taking the "group by" out of the statement. But I'm going to continue to tweek the code maybe VB 2003 doesn't like something about the statement.
Swish
-
I tried working the sql statement you posted and I still receive the Unhandled Exception Error. I've even tried tweaking it by taking the "group by" out of the statement. But I'm going to continue to tweek the code maybe VB 2003 doesn't like something about the statement.
Swish
"Group by" should work. Very likely, your statement cannot find something in your Excel, e.g. Table Name, Column Name, File Location, etc. The easiest way for me to help you is to get a copy of your Excel file. My email address is petersgyoung@hotmail.com.
petersgyoung