Maintaining formatting - Excel => Word

[ChrisMattock]

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?

 

[aidan.heritage]

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

 

[ChrisMattock]

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

 

[aidan.heritage]

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

 

[Ed]

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]

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.

 

[Ed]

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]

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