+ Reply to Thread
Results 1 to 5 of 5

Formatting all cells or sheet

  1. #1
    Steve Schroeder
    Guest

    Formatting all cells or sheet

    Although I'm not familair with the Excel object libarary, I am familiar with
    VBA. How can I format the whole sheet to use Text as the cell formatting, as
    opposed to General, the default?

    Me.Cells.Format...?

    Thanks,

    Steve



  2. #2
    Bob Phillips
    Guest

    Re: Formatting all cells or sheet


    Activesheet.Cells.Numberformat = "@"

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Steve Schroeder" <[email protected]> wrote in message
    news:[email protected]...
    > Although I'm not familair with the Excel object libarary, I am familiar

    with
    > VBA. How can I format the whole sheet to use Text as the cell formatting,

    as
    > opposed to General, the default?
    >
    > Me.Cells.Format...?
    >
    > Thanks,
    >
    > Steve
    >
    >




  3. #3
    Toppers
    Guest

    RE: Formatting all cells or sheet

    Activesheet.cells.numberformat="@"

    "Steve Schroeder" wrote:

    > Although I'm not familair with the Excel object libarary, I am familiar with
    > VBA. How can I format the whole sheet to use Text as the cell formatting, as
    > opposed to General, the default?
    >
    > Me.Cells.Format...?
    >
    > Thanks,
    >
    > Steve
    >
    >
    >


  4. #4
    Steve Schroeder
    Guest

    Re: Formatting all cells or sheet

    Thanks both of you for your help, that did appear to work. As is usually the
    case, the situation is a bit more complicated.

    Becase the column I am formatting contains a string value of:

    12929.00000001 (case number, text, not an 8 decimal place number)

    Even formatting it to text still leaves it 12929, at least visually. If you
    pretend to edit the cell, then move to another, it then appears as wished,
    the full string. I should also mention that this data is coming in from an
    ASP.Net web page. I actually tried to add the formatting in the VBScript
    code, but only got an error, incidently not the line I add,
    i.e.Activesheet.Cells.Numberformat = "@"

    The error is: Invalid Procedure Call or argument, and it happens on:
    HTMLProjectItems("Sheet1").Text = sHTML. Here is the VBScript code I'm
    using. Any thoughts or ideas would be greatly appreciated. Thanks!

    Sub cmdExcel_onclick()
    Dim sHTML
    sHTML = window.document.forms(0).children(4).outerhtml
    Dim oXL, oBook
    Set oXL = CreateObject("Excel.Application")
    Set oBook = oXL.Workbooks.Add
    oXL.Sheets("Sheet1").Cells.NumberFormat = "@"
    oBook.HTMLProject.HTMLProjectItems("Sheet1").Text = sHTML
    oBook.HTMLProject.RefreshDocument
    oXL.Visible = true
    oXL.UserControl = true
    End Sub



    "Steve Schroeder" <[email protected]> wrote in message
    news:[email protected]...
    > Although I'm not familair with the Excel object libarary, I am familiar

    with
    > VBA. How can I format the whole sheet to use Text as the cell formatting,

    as
    > opposed to General, the default?
    >
    > Me.Cells.Format...?
    >
    > Thanks,
    >
    > Steve
    >
    >




  5. #5
    Steve Schroeder
    Guest

    Re: Formatting all cells or sheet


    Ok, well here's what I did, although it isn't a perfect solution:

    oXL.Sheets("Sheet1").Columns(1).Cells.NumberFormat = "#####.########"
    oXL.Sheets("Sheet1").Columns(1).Cells.HorizontalAlignment = -4131



    "Steve Schroeder" <[email protected]> wrote in message
    news:[email protected]...
    > Thanks both of you for your help, that did appear to work. As is usually

    the
    > case, the situation is a bit more complicated.
    >
    > Becase the column I am formatting contains a string value of:
    >
    > 12929.00000001 (case number, text, not an 8 decimal place number)
    >
    > Even formatting it to text still leaves it 12929, at least visually. If

    you
    > pretend to edit the cell, then move to another, it then appears as wished,
    > the full string. I should also mention that this data is coming in from an
    > ASP.Net web page. I actually tried to add the formatting in the VBScript
    > code, but only got an error, incidently not the line I add,
    > i.e.Activesheet.Cells.Numberformat = "@"
    >
    > The error is: Invalid Procedure Call or argument, and it happens on:
    > HTMLProjectItems("Sheet1").Text = sHTML. Here is the VBScript code I'm
    > using. Any thoughts or ideas would be greatly appreciated. Thanks!
    >
    > Sub cmdExcel_onclick()
    > Dim sHTML
    > sHTML = window.document.forms(0).children(4).outerhtml
    > Dim oXL, oBook
    > Set oXL = CreateObject("Excel.Application")
    > Set oBook = oXL.Workbooks.Add
    > oXL.Sheets("Sheet1").Cells.NumberFormat = "@"
    > oBook.HTMLProject.HTMLProjectItems("Sheet1").Text = sHTML
    > oBook.HTMLProject.RefreshDocument
    > oXL.Visible = true
    > oXL.UserControl = true
    > End Sub
    >
    >
    >
    > "Steve Schroeder" <[email protected]> wrote in message
    > news:[email protected]...
    > > Although I'm not familair with the Excel object libarary, I am familiar

    > with
    > > VBA. How can I format the whole sheet to use Text as the cell

    formatting,
    > as
    > > opposed to General, the default?
    > >
    > > Me.Cells.Format...?
    > >
    > > Thanks,
    > >
    > > Steve
    > >
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1