By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,268 Members | 1,293 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,268 IT Pros & Developers. It's quick & easy.

Reading Cell data from Excel

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
Hi Dave,

Try
strData = CStr (xlWksht.Range ("$A$1"))

Regards,
Fergus
Nov 20 '05 #2

P: n/a
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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 20 '05 #3

P: n/a
Hello,

"DaveS" <st********@hotmail.com> schrieb:
I need to import user data from an Excel spreadsheet.
In VB6, I used

strData = xlWksht.Range("$A$1) & ""
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?


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

--
Herfried K. Wagner
MVP VB Classic, VB.NET
http://www.mvps.org/dotnet
Nov 20 '05 #4

P: n/a
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.]
Nov 20 '05 #5

P: n/a
"DaveS" <d?*********@hotmail.com> schrieb
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.


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

Nov 20 '05 #6

P: n/a
"Herfried K. Wagner [MVP]" <hi*******@m.activevb.de> schrieb
"DaveS" <st********@hotmail.com> schrieb:
I need to import user data from an Excel spreadsheet.
In VB6, I used

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


Didn't you use 'strData = xlWksht.Range("$A$1") & ""'?


Do I have to clean my glasses or are both statements equal?
--
Armin

Nov 20 '05 #7

P: n/a
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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 20 '05 #8

P: n/a
Hi Armin,

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

Regards,
Fergus

Nov 20 '05 #9

P: n/a
"Fergus Cooney" <fi******@tesco.net> schrieb
Lol. No Herfried spotted that Dave had mistyped his example and
left the
closing quote off his reference: ("$A$1).


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

Should've started windiff ...

;-))

--
Armin

Nov 20 '05 #10

P: n/a
Hello,

"Armin Zingler" <az*******@freenet.de> schrieb:
strData = xlWksht.Range("$A$1) & ""


Didn't you use 'strData = xlWksht.Range("$A$1") & ""'?


Do I have to clean my glasses or are both statements equal?


Clean your glasses:

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

;-)

--
Herfried K. Wagner
MVP VB Classic, VB.NET
http://www.mvps.org/dotnet
Nov 20 '05 #11

P: n/a
Hello,

"Armin Zingler" <az*******@freenet.de> schrieb:
Lol. No Herfried spotted that Dave had mistyped his
example and left the closing quote off his reference: ("$A$1).


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

Should've started windiff ...


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

SCNR

--
Herfried K. Wagner
MVP VB Classic, VB.NET
http://www.mvps.org/dotnet
Nov 20 '05 #12

P: n/a
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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 20 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.