Reading Excel in VB.NET

[simchajoy2000@yahoo.com]

Hi,

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

With oConn2
.ConnectionStri ng = "Provider=Micro soft.Jet.OLEDB. 4.0;Data source="
& FilePath & ";Extended Properties=Exce l 8.0;"
.Open()
End With

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

With oCmd2
.Connection = oConn2
.CommandType = CommandType.Tex t
.CommandText = strSQL
End With

oDA2.SelectComm and = 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

[Chris]

Re: Reading Excel in VB.NET

simchajoy2000@y ahoo.com wrote:[color=blue]
> Hi,
>
> So I do actually know how to read excel into VB.NET using the following
> approach:
>
> With oConn2
> .ConnectionStri ng = "Provider=Micro soft.Jet.OLEDB. 4.0;Data source="
> & FilePath & ";Extended Properties=Exce l 8.0;"
> .Open()
> End With
>
> Worksheet = "Well Data"
> strSQL = "SELECT * FROM [" & Worksheet & "$]"
>
> With oCmd2
> .Connection = oConn2
> .CommandType = CommandType.Tex t
> .CommandText = strSQL
> End With
>
> oDA2.SelectComm and = 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
>[/color]

You can open the excel sheet directly through interop.

Example:
Dim objExcel As New Excel.Applicati on
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.Workbo oks.Open("C:\te st.xls")
objSht = objWrkBk.Worksh eets(1)
objExcel.Visibl e = True
For iCol = 1 To maxCol
For iRow = 1 To maxRow
strCol = Chr(Asc(iRow) + 16)
strCell = strCol + iCol.ToString
objRng = objSht.Range(st rCell)
MsgBox(objRng.V alue)
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

[simchajoy2000@yahoo.com]

Re: Reading Excel in VB.NET

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.

[tommaso.gastaldi@uniroma1.it]

Re: Reading Excel in VB.NET

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)...

simchajoy2000@y ahoo.com ha scritto:
[color=blue]
> Hi,
>
> So I do actually know how to read excel into VB.NET using the following
> approach:
>
> With oConn2
> .ConnectionStri ng = "Provider=Micro soft.Jet.OLEDB. 4.0;Data source="
> & FilePath & ";Extended Properties=Exce l 8.0;"
> .Open()
> End With
>
> Worksheet = "Well Data"
> strSQL = "SELECT * FROM [" & Worksheet & "$]"
>
> With oCmd2
> .Connection = oConn2
> .CommandType = CommandType.Tex t
> .CommandText = strSQL
> End With
>
> oDA2.SelectComm and = 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[/color]

[Chris]

Re: Reading Excel in VB.NET

simchajoy2000@y ahoo.com wrote:[color=blue]
> 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.
>[/color]

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

Chris

[simchajoy2000@yahoo.com]

Re: Reading Excel in VB.NET

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

[Chris]

Re: Reading Excel in VB.NET

simchajoy2000@y ahoo.com wrote:[color=blue]
> 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
>[/color]

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