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?

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

Might help to do this by email ([email protected] 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

> 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

<[email protected]> wrote in message
news:[email protected]...
> Might help to do this by email ([email protected] 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
>

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.

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

<[email protected]> wrote in message
news:[email protected]...
> 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.
>

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