Reading Excel in VB.NET

22 Mar 2006 6:02 PM

simchajoy2000@yahoo.com

Hi,

So I do actually know how to read excel into VB.NET using the following
approach:

With oConn2
   .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source="
& FilePath & ";Extended Properties=Excel 8.0;"
   .Open()
End With

Worksheet = "Well Data"
strSQL = "SELECT * FROM [" & Worksheet & "$]"

With oCmd2
    .Connection = oConn2
    .CommandType = CommandType.Text
    .CommandText = strSQL
End With

oDA2.SelectCommand = oCmd2
oDA2.Fill(oDS2)

This approach works great except for with the type of excel worksheet I
am dealing with now. This particular worksheet has a column which
contains numbers, dates, strings containing only text and strings
containing numbers and text. The first value it runs across is a
number and from that point on it tries to force everything in the
column to be a number, and if it contains text, then it simply returns
a DBNull value for that cell.

Is there something I can do to force the Data Adaptor to see this
column as simply text and to just leave it alone? Or is there another
approach to reading Excel that I could use?

Thanks!

Joy

22 Mar 2006 7:31 PM

Chris

simchajoy2***@yahoo.com wrote:
Show quoteHide quote

> Hi,
>
> So I do actually know how to read excel into VB.NET using the following
> approach:
>
> With oConn2
>    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source="
> & FilePath & ";Extended Properties=Excel 8.0;"
>    .Open()
> End With
>
> Worksheet = "Well Data"
> strSQL = "SELECT * FROM [" & Worksheet & "$]"
>
> With oCmd2
>     .Connection = oConn2
>     .CommandType = CommandType.Text
>     .CommandText = strSQL
> End With
>
> oDA2.SelectCommand = oCmd2
> oDA2.Fill(oDS2)
>
> This approach works great except for with the type of excel worksheet I
> am dealing with now. This particular worksheet has a column which
> contains numbers, dates, strings containing only text and strings
> containing numbers and text. The first value it runs across is a
> number and from that point on it tries to force everything in the
> column to be a number, and if it contains text, then it simply returns
> a DBNull value for that cell.
>
> Is there something I can do to force the Data Adaptor to see this
> column as simply text and to just leave it alone? Or is there another
> approach to reading Excel that I could use?
>
> Thanks!
>
> Joy
>

You can open the excel sheet directly through interop.

Example:
       Dim objExcel As New Excel.Application
         Dim objWrkBk As Excel.Workbook
         Dim objSht As Excel.Worksheet
         Dim objRng As Excel.Range
         Dim strCol, strCell As String
         Dim maxCol, maxRow As Integer
         Dim iRow, iCol As Integer
         maxRow = 2
         maxCol = 2
         objWrkBk = objExcel.Workbooks.Open("C:\test.xls")
         objSht = objWrkBk.Worksheets(1)
         objExcel.Visible = True
         For iCol = 1 To maxCol
             For iRow = 1 To maxRow
                 strCol = Chr(Asc(iRow) + 16)
                 strCell = strCol + iCol.ToString
                 objRng = objSht.Range(strCell)
                 MsgBox(objRng.Value)
             Next
         Next

You will need to make a refernce to the Excel, which is easy. Look at
the top of this article for how do that.

http://www.vbdotnetheaven.com/Code/Jul2003/2124.asp

Chris

22 Mar 2006 9:00 PM

simchajoy2000@yahoo.com

Hey Thanks. That helps a lot. I checked out the link you sent me but
I'm still a bit confused. Where do I get this TlbImp? And in the
article it says to execute this line:

TlbImp Excel9.olb Excel.dll

But where is this executed? In the command line prompt or somewhere
else?

Thanks again.

22 Mar 2006 9:13 PM

Chris

simchajoy2***@yahoo.com wrote:

> Hey Thanks. That helps a lot. I checked out the link you sent me but
> I'm still a bit confused. Where do I get this TlbImp? And in the
> article it says to execute this line:
>
> TlbImp Excel9.olb Excel.dll
>
> But where is this executed? In the command line prompt or somewhere
> else?
>
> Thanks again.
>

yes, it's an exe installed with visual studio. Use it at the command
prompt.

Chris

23 Mar 2006 4:57 AM

simchajoy2000@yahoo.com

Thanks Chris,

Sorry if this is a redundant question but, what directory do I need to
be in when I execute the command? When I tried to execute it just at
the C drive, it returns an error.

Joy

23 Mar 2006 3:26 PM

Chris

simchajoy2***@yahoo.com wrote:

> Thanks Chris,
>
> Sorry if this is a redundant question but, what directory do I need to
> be in when I execute the command? When I tried to execute it just at
> the C drive, it returns an error.
>
> Joy
>

I'd do a search for the file. There may be a "Visual Studio .Net
Command Prompt" in your program files for Visual Studio. That one will
probably have the paths setup.

Chris

22 Mar 2006 9:10 PM

tommaso.gastaldi

Hi Joy,

you can specify a conversion on that field (or even all). For instance,
if it is the first one:

SELECT
         iif(IsNull(w.F1), "", cstr(w.F1)),
         w.F2
FROM
         [Worksheet$] w

if it is the k-th field, you will clearly use FK where k is the ordinal
of the field (k=1,2,3...)

Let me know if this works for you...

-tommaso

PS.
- Can omit aliasing if you wish
- F1, F2, F3 , ... clearly are the automatic names assigned to the
column by the OLEDB driver. They are always so (in any case, if you
like generality, you could get them from the SchemaGuid)...

simchajoy2***@yahoo.com ha scritto:

Show quoteHide quote

> Hi,
>
> So I do actually know how to read excel into VB.NET using the following
> approach:
>
> With oConn2
>    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source="
> & FilePath & ";Extended Properties=Excel 8.0;"
>    .Open()
> End With
>
> Worksheet = "Well Data"
> strSQL = "SELECT * FROM [" & Worksheet & "$]"
>
> With oCmd2
>     .Connection = oConn2
>     .CommandType = CommandType.Text
>     .CommandText = strSQL
> End With
>
> oDA2.SelectCommand = oCmd2
> oDA2.Fill(oDS2)
>
> This approach works great except for with the type of excel worksheet I
> am dealing with now. This particular worksheet has a column which
> contains numbers, dates, strings containing only text and strings
> containing numbers and text. The first value it runs across is a
> number and from that point on it tries to force everything in the
> column to be a number, and if it contains text, then it simply returns
> a DBNull value for that cell.
>
> Is there something I can do to force the Data Adaptor to see this
> column as simply text and to just leave it alone? Or is there another
> approach to reading Excel that I could use?
>
> Thanks!
>
> Joy