asked on

Reading Excel Column's data

I have a column in Excel called "DATE" which starts at B:6 and holds a month's worth of dates in there.

1) Is there a way to loop through Column B starting at B:6 and down, then checking each date?
2) Once I find today's date like using something like Date.Now() how would I write data starting at that specific cell?

Thanks!

Chris

ASKER CERTIFIED SOLUTION

DotNetLover_Baan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Learned

That'll do it Anirban.

Bob

Trancedified

ASKER

-Baan,

Thanks for the quick post, but I got an error:

"An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll"

It is pointing to:
cmd.ExecuteNonQuery()

Based on that web page's code, I got this:

Imports System.Data
Imports System.Data.OleDb

Public Class Form1
Inherits System.Windows.Forms.Form

Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\CChang\My Documents\blah.xls;" & _
"Extended Properties=""Excel 8.0;HDR=NO"""

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934#11

Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
conn1.Open()
Dim cmd As New System.Data.OleDb.OleDbCommand
cmd.Connection = conn1
cmd.CommandText = "INSERT INTO [sheet1$C6] values ('CCC')"
cmd.ExecuteNonQuery() '<----- Here
conn1.Close()

End Sub

Any ideas?

Chris

End Class

Bob Learned

Is sheet1$C6 the name of the worksheet?

Bob

DotNetLover_Baan

Put Try-Catch to get the exact error..

Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
conn1.Open()
Dim cmd As New System.Data.OleDb.OleDbCommand
cmd.Connection = conn1
cmd.CommandText = "INSERT INTO [sheet1$C6] values ('CCC')"
Try
cmd.ExecuteNonQuery() '<----- Here
Catch Ex As Exception
MsgBox(Ex.ToString)
Finally
conn1.Close()
End Try

-Baan

Trancedified

ASKER

I reworked it alittle:

Try
Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
conn1.Open()
Dim cmd As New System.Data.OleDb.OleDbCommand
cmd.Connection = conn1

cmd.CommandText = "INSERT INTO [Sheet1$] (A1) values ('111')"
cmd.ExecuteNonQuery()

conn1.Close()

Catch ex As Exception
MsgBox(ex.Message)
End Try

The error message is: "The INSERT INTO statement contains the following unknown field name: 'A1'. Make sure y ou have typed the name correctly, and try the operation again.

I thought A1 would be Column A, row 1??
Chris

SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Trancedified

ASKER

Bob,

Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\CChang\My Documents\blah.xls;" & _
"Extended Properties=""Excel 8.0;HDR=NO;"""

I tried flipping YES or NO but I get the same error. blah.xls is just a blank excel file.

Chris

Bob Learned

What are the field names for the data table?

Bob

Trancedified

ASKER

Bob,

There are no field names like "Date" or something. I'm hoping to just write to specific columns like A, B, C, D etc. but starting from a specific row.

Chris

Bob Learned

No, you would have to use the same column names that the OleDb provider would use, which is gets from either the header row, or uses F1, F2, F3, F4... when HDR = NO (as is in your connection string).

Bob

Trancedified

ASKER

Bob, Baan,

I found the solution, instead of getting from a datasource, I just checked for a blank (a gray bar in a row in Excel) within a specific column, and if there's a blank BELOW that bar, add the data I need. It's alittle better than checking for Date.Now()... Thanks for your comments!

Chris