Output from Excel to Word -- HELP!

[artikal]

Hi! I am trying to output from Excel to Word using the following code. The data needs to retain the font.color, .bold, .italic,.underline and .strikethrough properties of the ActiveCell.Value.
Tried 3 different ways:
1. Open "c:\TESTFILE.doc" For Output As #1
Range("A1").Select
ActiveCell.Offset(somenum, 0).Select
Print #1, ActiveCell.Value;
Close #1
This wrote the data to the file but would not format as required.

2. Dim channelNumber As String 'Application Handle
Dim FullPath As String
FullPath = "C:\MyTestFile.Doc"
channelNumber = Application.DDEInitiate( _
app:="WinWord", topic:=FullPath)
Range("A1").Select
ActiveCell.Offset(somenum, 0).Select
ActiveCell.Copy
SendKeys "^v", True
Application.DDETerminate channelNumber

It opens the file but nothing gets written to it. Also, not sure if this will retain the formatting/styles/font in Word.

3. Dim wdApp As Object
Dim wdDoc As Object
Set wdApp = CreateObject("Word.application")
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open _
(Filename:="C:\myTestFile.doc")
Range("A1").Select
ActiveCell.Offset(somenum, 0).Select
ActiveCell.Copy
SendKeys "^v", True
wdDoc.Close savechanges:=False
Set wdDoc = Nothing
wdApp.Quit
Set wdApp = Nothing
Again nothing happens.
In all 3 cases, I'm not getting any errors.
Can anybody help?! Thanks in advance!

 

[TonyJollans]

1. You cannot write a file in Word format that way - just text, which is why you lose the formatting.

2. Even if the paste works (but I suspect it doesn't) you are doing nothing to save the document. Withut some experimenting I don't know whether the technique could work.

3. Normal instantiation of a Word object is the way to do it - but you are throwing away any changes you make with SaveChanges:=False! Again I suspect the paste doesn't work and there aren't any changes; Sendkeys is notoriously unreliable and there is no need to use it here.

Try replacing ...

SendKeys "^v", True
wdDoc.Close savechanges:=False

with ...

[blue]wdApp.Selection.Paste
wdDoc.Close savechanges:=True[/blue]

I'm not sure this will do exactly what you want but it should point you in the right direction.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[cresbydotcom]

think along the lines of Sendkeys. If Word has focus and the data highlighted, you can instruct Word to do anything that can be invoked by keystrokes. If data is mixed as well as their properties it will need a lot of inspection for the properties of interest. (eg Cells(x,y).InteriorColor etc). To find what is needed I usually "watch" (say) "cells(x,y)" in single step and look for expanded properties that seem to be answering the need.

Not elegant and probably tedius but I collect Eurosport schedules from the internet that way, save as text files, look for patterned data and extract the schedule, dump it into Excel and highlight lines with MotoGP etc in them. It is slow and relies on picking up all the right clues and even then Teletext Transmissions (UK analogue satellite) are different. But it works for up to 14 days............