+ Reply to Thread
Results 1 to 8 of 8

Maintaining formatting - Excel => Word

  1. #1
    Forum Contributor
    Join Date
    04-27-2006
    Location
    Cayman Islands
    Posts
    375

    Maintaining formatting - Excel => Word

    I am using an excel spreadsheet to enter details into a word document, but even though a cell is formatted in currency format when I use the following code....

    appWD.Selection.MoveDown Unit:=wdLine, Count:=4
    appWD.Selection.MoveLeft Unit:=wdCharacter, Count:=2
    appWD.Selection.TypeText Text:=strReference

    The text changes from 33.50 in the Excel spreadsheet to 33.5 in the Word document, any tips?

  2. #2
    aidan.heritage@virgin.net
    Guest

    Re: Maintaining formatting - Excel => Word

    Excel DISPLAYS values as formatted, but stores them numerically - so if
    you need to send it to the document as a certain format, send it IN
    that format

    appWD.Selection.TypeText Text:=3Dformat(strReference ,"=A30.00")

    will do what you want


  3. #3
    Forum Contributor
    Join Date
    04-27-2006
    Location
    Cayman Islands
    Posts
    375
    I tried that and it came up with a compile syntax error, highlighting the 3D part of 3D format, and where does the "=A30.00" bit come from? Sorry don't use VB much...

    After reading the other post you helped me on I have changed my code...

    strLOADate = Cells(2, 1)
    strReference = Cells(2, 3)

    appWD.ActiveDocument.Bookmarks("LOADate").Range = strLOADate
    appWD.ActiveDocument.Bookmarks("Reference").Range = strReference


    This (with other bits in between obviously) now loads my Date Cell into the Date Bookmark in the Word document, could you just give me an example of how to do this in the format that the Excel cell is formatted in i.e. producing 12 May 2006 rather than 12/5/06?

    Thanks I appreciate it!
    Last edited by ChrisMattock; 05-10-2006 at 08:11 AM.

  4. #4
    aidan.heritage@virgin.net
    Guest

    Re: Maintaining formatting - Excel => Word

    Might help to do this by email (aidan.heritage@virgin.net being my
    email) but the A30 that you saw was supposed to be a UK pounds sign and
    0.00 - basically, put in the format that you want to have in the quotes


  5. #5
    Ed
    Guest

    Re: Maintaining formatting - Excel => Word

    > Might help to do this by email
    It would be great if you could at least update this thread, if you aren't
    going to continue resolving things here. That way the rest of us could
    learn, too.

    Ed

    <aidan.heritage@virgin.net> wrote in message
    news:1147265482.981810.260750@j33g2000cwa.googlegroups.com...
    > Might help to do this by email (aidan.heritage@virgin.net being my
    > email) but the A30 that you saw was supposed to be a UK pounds sign and
    > 0.00 - basically, put in the format that you want to have in the quotes
    >




  6. #6
    aidan.heritage@virgin.net
    Guest

    Re: Maintaining formatting - Excel => Word

    My apologies - my concern was that the formatting wasn't going through
    correctly via the group! We've had an exchange of emails, but the ONLY
    development is the ability to format a date with ordinal text - not
    built into either Word or Excel, but a select case statement along
    these lines would handle it

    Select Case Day(Now())
    Case 1, 21, 31
    MsgBox Day(Now()) & "st " & Format(Now(), "mmmm yyyy")
    Case 2, 22
    MsgBox Day(Now()) & "nd " & Format(Now(), "mmmm yyyy")
    Case 3
    MsgBox Day(Now()) & "rd " & Format(Now(), "mmmm yyyy")
    Case Else
    MsgBox Day(Now()) & "th " & Format(Now(), "mmmm yyyy")
    End Select

    (obviously in the real example, we don't need a message box, and we
    only actually need to store the ordinal (string) part as a variable.


  7. #7
    Ed
    Guest

    Re: Maintaining formatting - Excel => Word

    Yah - I can see the need for the Select Case to get ordinals for date. As
    far as preserving the formatting shown in Excel when moved to Word, the
    problem there seems to be the use of strings. Strings don't support
    formatting of any kind, as far as I know. But using copy and paste, even
    using Paste Special >> Unformatted Text in Word, I get formatted date and
    currency values from Excel into Word just fine. (I'm using Word and Excel
    XP.)

    Ed

    <aidan.heritage@virgin.net> wrote in message
    news:1147270577.091489.280740@y43g2000cwc.googlegroups.com...
    > My apologies - my concern was that the formatting wasn't going through
    > correctly via the group! We've had an exchange of emails, but the ONLY
    > development is the ability to format a date with ordinal text - not
    > built into either Word or Excel, but a select case statement along
    > these lines would handle it
    >
    > Select Case Day(Now())
    > Case 1, 21, 31
    > MsgBox Day(Now()) & "st " & Format(Now(), "mmmm yyyy")
    > Case 2, 22
    > MsgBox Day(Now()) & "nd " & Format(Now(), "mmmm yyyy")
    > Case 3
    > MsgBox Day(Now()) & "rd " & Format(Now(), "mmmm yyyy")
    > Case Else
    > MsgBox Day(Now()) & "th " & Format(Now(), "mmmm yyyy")
    > End Select
    >
    > (obviously in the real example, we don't need a message box, and we
    > only actually need to store the ordinal (string) part as a variable.
    >




  8. #8
    aidan.heritage@virgin.net
    Guest

    Re: Maintaining formatting - Excel => Word

    Copy and paste will work as the clipboard takes what it sees (kind
    of!), and you can even use copy and paste in the macro, but I would not
    recommend it. I agree that it's the use of strings, but there is no
    need to use strings

    AppWd.bookmarks("Fred").range=range("A1").value

    works just as well as storing the value of the cell in a variable first

    BUT you don't actually need to use the value

    AppWd.bookmarks("Fred").range=range("A1").text

    would give you the formatted text


+ 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