Reading Excel in VB.NET

[simchajoy2000]

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

 

[Chris]

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

 

[simchajoy2000]

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]

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

(e-mail address removed) ha scritto:

 

[Chris]

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

 

[simchajoy2000]

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]

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