Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 995
  • Last Modified:

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
0
Trancedified
Asked:
Trancedified
  • 5
  • 5
  • 2
2 Solutions
 
DotNetLover_BaanCommented:
0
 
Bob LearnedCommented:
That'll do it Anirban.

Bob
0
 
TrancedifiedAuthor Commented:
-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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Bob LearnedCommented:
Is sheet1$C6 the name of the worksheet?

Bob
0
 
DotNetLover_BaanCommented:
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
0
 
TrancedifiedAuthor Commented:
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
0
 
Bob LearnedCommented:
What is the connection string like?  Are you marking the first row as headers?  If you don't then your columns are going to be F1, F2, F3...

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MySpreadsheet.XLS;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

HDR=Yes

Bob
0
 
TrancedifiedAuthor Commented:
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
0
 
Bob LearnedCommented:
What are the field names for the data table?

Bob
0
 
TrancedifiedAuthor Commented:
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
0
 
Bob LearnedCommented:
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
0
 
TrancedifiedAuthor Commented:
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
0
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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