Retrieving data from Excel and store it into string variable

[marsulein]

Hi All,

I create an excel object in VB and store the data from the spreadshee
into a local variable called strTemp. However, error occurs when th
data field is larger than 255 characters. Excel automatically truncat
the data to 255 chars and gives out error:

"Arguments are of the wrong type, are out of acceptable range, or ar
in conflict with one another"

Is there anyway to fix this?

Thanx in advance.

Regards,
Ma

 

[NickHK]

marsulein,
How do you DIMension "strTemp" ?
And how are trying to set its value ?

NickHK

 

[Jamie Collins]

I create an excel object in VB and store the data from the spreadsheet
into a local variable called strTemp. However, error occurs when the
data field is larger than 255 characters. Excel automatically truncate
the data to 255 chars and gives out error:

"Arguments are of the wrong type, are out of acceptable range, or are
in conflict with one another"

Is there anyway to fix this?

It sounds like the data type of the Excel column is being determined
as TEXT, which is limited to 255 characters. The data type you require
is MEMO.

You can test the data type in you code using:

Select Case objExcel.Fields("Name").Type
Case adLongVarWChar
MsgBox "MEMO"
Case adVarWChar
MsgBox "TEXT"
End Select

How do you determine the data type for an Excel column? Well, you
can't, it is done for you using the data present in the column at the
time the query is executed and may be influenced by settings in the
connection string and the registry. For details, see:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Jamie.

--