Reading Cell data from Excel

  • Thread starter Thread starter DaveS
  • Start date Start date
D

DaveS

Hi!

Just a quick question...

I need to import user data from an Excel spreadsheet. In VB6, I used

strData = xlWksht.Range("$A$1) & ""

to import a cell with string data in it. That worked just fine.

When I do the same thing in .NET, I get an error since the right hand
side of the expression is not a string. Any ideas of the .NET version
of that line of code?

TIA,
DaveS
 
Fergus,

I tried that and get the following error:

An unhandled exception of type 'System.InvalidCastException' occurred in
microsoft.visualbasic.dll

Additional information: Cast from type 'Range' to type 'String' not
valid.

TIA,

DaveS
 
Hello,

DaveS said:
I need to import user data from an Excel spreadsheet.
In VB6, I used

strData = xlWksht.Range("$A$1) & ""
Click to expand...

Didn't you use 'strData = xlWksht.Range("$A$1") & ""'?
to import a cell with string data in it. That worked just fine.

When I do the same thing in .NET, I get an error since
the right hand side of the expression is not a string. Any
ideas of the .NET version of that line of code?
Click to expand...

You must convert it to a string, have a look at 'CStr'.
 
Hi Dave,

|| Additional information: Cast from type 'Range' to
|| type 'String' not valid

Now I'm with you. A very useful piece of additional information, that. :-)

Range is an object containing the cell coords or some such nonsense. VB6
presumably knew how to extract the text. I guess Range has a default property
which does this.

Alas, .NET knows nothing about the default properties of COM objects (eg
Excel), so you have to explicitly get the text.

But it's still very easy (when you know how):
strData = xlWksht.Range("$A$1").Text

Regards,
Fergus

ps. [You can use ().Formula if you're interested in that at all.]
 
DaveS said:
Fergus,

I tried that and get the following error:

An unhandled exception of type 'System.InvalidCastException' occurred
in microsoft.visualbasic.dll

Additional information: Cast from type 'Range' to type 'String'
not valid.
Click to expand...

The message is correct. The object returned by

xlWksht.Range ("$A$1")

is a Range object, not a string. In this case, VB6 looked for a default
property and assigned the value of the default property, but not the Range
object itself. It failed if there was no default property. Now, when using
Option Strict, you have to specify the property on your own and apply type
casting. It's a little more work, but you'll get the error message earlier,
i.e. even before the application runs.

So, you have to write

strData = Directcast (xlWksht.Range ("$A$1")._Default, String)

.... but only if it returns a string.
 
Just a note to let you know that I finally figured out a way to read a
cell/range from Excel...

strData = xlWksht.Range("$A$1")._Default & ""

I still need to append a zero-length string just in case the cell
returns Nothing. In other words,

strData = xlWksht.Range("$A$1")._Default.ToString will produce an error
in this case.

If anyone knows of a more elegant way of reading in Excel data, please
post it here!

TIA,

DaveS
 
Hi Armin,

Lol. No Herfried spotted that Dave had mistyped his example and left the
closing quote off his reference: ("$A$1).

Regards,
Fergus
 
Fergus Cooney said:
Lol. No Herfried spotted that Dave had mistyped his example and
left the
closing quote off his reference: ("$A$1).
Click to expand...

weeep weeep weepp ... can you hear them scream - my glasses?

Should've started windiff ...

;-))
 
Hello,

Armin Zingler said:
Do I have to clean my glasses or are both statements equal?
Click to expand...

Clean your glasses:

strData = xlWksht.Range("$A$1) & ""
strData = xlWksht.Range("$A$1") & ""

;-)
 
Hello,

Armin Zingler said:
weeep weeep weepp ... can you hear them scream - my glasses?

Should've started windiff ...
Click to expand...

Windows includes a tool for magnifying parts of the screen...

SCNR
 
Tut mir leid! I was in a huge rush this morning and needed to get this
question posted onto the newsgroup before a meeting. I guess that quote
just slipped right on by me...

DaveS
 
Back
Top