Prev: need socket ocx recommendation
Next: Is there a way to convert a .TIF file in VB6
From: Dee Earley on 3 Mar 2010 05:07
On 03/03/2010 09:44, Dave O. wrote:
> Interesting, the intellisense for the "Cells(x,y)" property implies that
> there is nothing to go after the coordinates (ie if I place a . there, I get
> no suggestions). It seems that yes, "Value" does work there but "Text"
> gives: "Error 1004: Unable to set the Text property of Range class".
> Anyway I've got it working now by setting the NumberFormat for the column.

It is most likely untyped (or a variant) so it can;t provide any
intellisense

--
Dee Earley (dee.earley(a)icode.co.uk)
i-Catcher Development Team

iCode Systems
From: Karl E. Peterson on 3 Mar 2010 13:42
Dave O. wrote:
> "Karl E. Peterson" <karl(a)exmvps.org> wrote in message
> news:%23rhoq1kuKHA.3428(a)TK2MSFTNGP06.phx.gbl...
>> Dave O. wrote:
>>> Hi
>>>
>>> I'm creating a new Excel file (referenced: Microsoft Excel 10.0 Object
>>> Library) and I have a column of 13 digit numbers which Excel being helpful
>>> as usual is showing in scientific notation, I've tried prefixing the
>>> column content with an apostrophe which works for CSV files being read
>>> into Excel but apparently not in XLS files.
>>>
>>> Code Extracts:
>>>
>>> Dim XL As Excel.Workbook
>>> Dim XLA As Excel.Application
>>> Dim XS As Excel.Worksheet
>>>
>>> Set XLA = New Excel.Application
>>> Set XL = XLA.Workbooks.Open(XLSName)
>>> Set XS = XL.Worksheets(1)
>>>
>>> tLn = Split(XLSContent, vbCrLf)
>>> tCl = Split(tLn(i), "�")
>>> For n = 0 To UBound(tCl)
>>> XS.Cells(i + 1, n + 1) = tCl(n)
>>> Next
>>> Next
>>>
>>> You get the idea I'm sure - What I need to know is how can I use the
>>> library to force column 2 to text to stop Excel from being "helpful". Any
>>> other tips like setting column width and simple formatting (bold text)
>>> would be nice but not as important.
>>
>> It looks to me like you're being bitten by the default property of the
>> object you're assigning to. Which, more than likely, is Value. How about
>> being explicit, and assigning directly to the Text property?
>>
>> -- .NET: It's About Trust!
>> http://vfred.mvps.org
>>
>
> Interesting, the intellisense for the "Cells(x,y)" property implies that
> there is nothing to go after the coordinates (ie if I place a . there, I get
> no suggestions). It seems that yes, "Value" does work there but "Text" gives:
> "Error 1004: Unable to set the Text property of Range class".
> Anyway I've got it working now by setting the NumberFormat for the column.

You probably have to reduce the range down to a single cell, or
something. I know I fought this one before, and I had to some sort of
dance like that.

Ahhhh, something like this?

.Cells(x, y).Offset(0, 0).Text

The Offset property returns a specific cell within a range, as I
recall. Might want to F1 it. :-)

--
..NET: It's About Trust!
http://vfred.mvps.org


From: Dave O. on 4 Mar 2010 04:53

"Karl E. Peterson" <karl(a)exmvps.org> wrote in message
news:e3n0WFwuKHA.4492(a)TK2MSFTNGP05.phx.gbl...
> You probably have to reduce the range down to a single cell, or something.
> I know I fought this one before, and I had to some sort of dance like
> that.
>
> Ahhhh, something like this?
>
> .Cells(x, y).Offset(0, 0).Text
>
> The Offset property returns a specific cell within a range, as I recall.
> Might want to F1 it. :-)

Yeah tried that a lot but F1 on any Excel object returns "Unable to display
help". I tried the online help but the navigation there is not so much
non-intuitive as anti-intuitive.

I tested inserting an offset there and it returns the same error for .Text
as before - "Error 1004: Unable to set the Text property of Range class".

Thanks Again
Dave O.


From: Dave O. on 5 Mar 2010 06:38

"Karl E. Peterson" <karl(a)exmvps.org> wrote in message
news:e26CE78uKHA.796(a)TK2MSFTNGP05.phx.gbl...

> You may need to re-run the office install. I think the VBA help is an
> optional component that isn't included by default. That's the wordy way
> to say, "Works here!" ;-)

Really can't be bothered as I should be getting a new PC soon, I'll install
everything on that when it turns up and hopefully it'll work then.

> This does seem to work, though:
>
> Set rng = ActiveSheet.Range("A1")
> rng.Offset(0, 0).Value = 1198.3
> rng.Offset(1, 0).Value = "'" & CStr(1198.3)

Thanks but I have it working quite adequately by setting the column format.

Thanks again for your interest.
Dave O.


First  |  Prev  | 
Pages: 1 2 3 4 5
Prev: need socket ocx recommendation
Next: Is there a way to convert a .TIF file in VB6