Read formatted value of cell

I have a spreadsheet and some of the columns are formatted. For example, in
one column is a phone number. The value typed in is 8001234567 and when you
are simply viewing the cell it shows (800) 123 - 4567 (the cell is formatted
as Special: Phone Number). When I read (via vbscript) I get the
8001234567.

Set excel = CreateObject("Excel.Application")

excel.workbooks.Open "spreadsheet.xls"
excel.WorkSheets(1).Activate

phone = excel.ActiveSheet.Cells(1,1).Value

I would prefer to get the formatted value in the spreadsheet (i.e. (800)
123 - 4567).

I've searched and so far not turned up much. Any pointers would be greatly
appreciated.

BBB

phone = excel.ActiveSheet.Cells(1,1).Text

is one way.

booner wrote:
>
> I have a spreadsheet and some of the columns are formatted. For example, in
> one column is a phone number. The value typed in is 8001234567 and when you
> are simply viewing the cell it shows (800) 123 - 4567 (the cell is formatted
> as Special: Phone Number). When I read (via vbscript) I get the
> 8001234567.
>
> Set excel = CreateObject("Excel.Application")
>
> excel.workbooks.Open "spreadsheet.xls"
> excel.WorkSheets(1).Activate
>
> phone = excel.ActiveSheet.Cells(1,1).Value
>
> I would prefer to get the formatted value in the spreadsheet (i.e. (800)
> 123 - 4567).
>
> I've searched and so far not turned up much. Any pointers would be greatly
> appreciated.
>
> BBB

--

Dave Peterson

BBB

I think the Text property will return what you want.

excel.ActiveSheet.Cells(1,1).Text


--
**** Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

booner wrote:
> I have a spreadsheet and some of the columns are formatted. For
> example, in one column is a phone number. The value typed in is
> 8001234567 and when you are simply viewing the cell it shows (800)
> 123 - 4567 (the cell is formatted as Special: Phone Number). When I
> read (via vbscript) I get the 8001234567.
>
> Set excel = CreateObject("Excel.Application")
>
> excel.workbooks.Open "spreadsheet.xls"
> excel.WorkSheets(1).Activate
>
> phone = excel.ActiveSheet.Cells(1,1).Value
>
> I would prefer to get the formatted value in the spreadsheet (i.e.
> (800) 123 - 4567).
>
> I've searched and so far not turned up much. Any pointers would be
> greatly appreciated.
>
> BBB

That was exactly it. I should have thought of that from the VB days.

Thanks!

"**** Kusleika" <dkusleika@gmail.com> wrote in message
news:OlBV9FOWGHA.4620@TK2MSFTNGP04.phx.gbl...
> BBB
>
> I think the Text property will return what you want.
>
> excel.ActiveSheet.Cells(1,1).Text
>
>
> --
> **** Kusleika
> MS MVP - Excel
> www.dailydoseofexcel.com
>
> booner wrote:
> > I have a spreadsheet and some of the columns are formatted. For
> > example, in one column is a phone number. The value typed in is
> > 8001234567 and when you are simply viewing the cell it shows (800)
> > 123 - 4567 (the cell is formatted as Special: Phone Number). When I
> > read (via vbscript) I get the 8001234567.
> >
> > Set excel = CreateObject("Excel.Application")
> >
> > excel.workbooks.Open "spreadsheet.xls"
> > excel.WorkSheets(1).Activate
> >
> > phone = excel.ActiveSheet.Cells(1,1).Value
> >
> > I would prefer to get the formatted value in the spreadsheet (i.e.
> > (800) 123 - 4567).
> >
> > I've searched and so far not turned up much. Any pointers would be
> > greatly appreciated.
> >
> > BBB
>
>