Solved

Reading Excel Column's data

Posted on 2004-09-30
12
983 Views
Last Modified: 2012-06-22
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
Comment
Question by:Trancedified
  • 5
  • 5
  • 2
12 Comments
 
LVL 18

Accepted Solution

by:
DotNetLover_Baan earned 250 total points
ID: 12194609
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12200275
That'll do it Anirban.

Bob
0
 
LVL 1

Author Comment

by:Trancedified
ID: 12200569
-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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12200610
Is sheet1$C6 the name of the worksheet?

Bob
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12201728
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
 
LVL 1

Author Comment

by:Trancedified
ID: 12203650
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 250 total points
ID: 12203679
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
 
LVL 1

Author Comment

by:Trancedified
ID: 12204291
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12216401
What are the field names for the data table?

Bob
0
 
LVL 1

Author Comment

by:Trancedified
ID: 12217514
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12217844
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
 
LVL 1

Author Comment

by:Trancedified
ID: 12264889
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now