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?


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
View this thread: http://www.excelforum.com/showthread...hreadid=540559

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:=format(strReference ,"£0.00")

will do what you want

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...

here is what I have (or part of it)... where strReference is a £0.00
sum

Sub main()
strDate = Cells(2, 1)
strReference = Cells(2, 3)
strFAO = Cells(2, 4)
iquote = Cells(2, 2)
fname$ = InputBox("Save Letter of Acceptance as :")
Cells(3, 3) = "Letter " & fname$ & " issued"

Dim appWD As Word.Application
Set appWD = CreateObject("word.application.8")
appWD.Visible = True
appWD.Documents.Open FileName:="H:\DesktopXP\LOA
Stuff\02\LOA2.doc"
appWD.Selection.TypeText Text:=strDate
appWD.Selection.MoveRight Unit:=wdCharacter, Count:=13
appWD.Selection.TypeText Text:=strReference
appWD.Selection.MoveRight Unit:=wdCharacter, Count:=23
appWD.Selection.TypeText Text:=strFAO
appWD.ActiveDocument.SaveAs FileName:="H:\DesktopXP\LOA Stuff\02\"
& fname$, FileFormat:=wdFormatDocument

appWD.ActiveDocument.Close
appWD.Quit

End Sub


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
View this thread: http://www.excelforum.com/showthread...hreadid=540559

Might help to do this by email 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

wrote in message
oups.com...
Might help to do this by email 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.