xl97: Create Word file from Excel range? 2

[VBAjedi]

Hi all!

Can someone show me an approach to create a Word file from an Excel file? The code will be run from an Excel97 workbook, needs to be forward compatible with later versions of Office, and needs to do the following:

1)Open a new Word document.
2)Write each cell's contents in Range("A1:A20&quot as a new line in the Word document, applying the text formatting from that cell to that line.
3)Save the document as a name specified by the user via an inputbox (I can work out the mechanics of getting the filename and path from the user, I just need the syntax for the Save As in the Word object model).

Thanks for any help you can give me!


VBAjedi

 

[Loomah]

Hi Jedi
I'd never done this before so it isn't the full answer but it's a start. The bit I couldn't get the hang of quickly was taking the format over, but it's past my bedtime now!!

One other thing I should point out (though it probably doesn't matter too much) is that this was done in Office 2000, not 97. I have problems with creating the reference to Word8. Other than that I only have xl from the 97 suite still on my system.

Regarding references, don't forget to make a reference to the word object library if you haven't already done so. I did!!!

Sub CopyXL_to_Word()
Dim c As Range
Dim wdObj As Word.Application
Dim wbME As Workbook

'Create Objects
Set wdObj = CreateObject("word.application")
Set wbME = ThisWorkbook

With wdObj
    .Visible = True
    wdObj.Documents.Add
        'navigate worksheet cells
        For Each c In wbME.Worksheets("sheet1").Range("A1:A20")
            With .Selection
                'add cell value to word
                .TypeText c.Value
                .TypeParagraph
            End With
        Next
    'save the document
    .ActiveDocument.SaveAs ("name")
End With
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[TonyJollans]

Hi Jedi,

If you copy your range intact to Word, it will create a table which includes all the formatting (including cell formatting). If you then convert the table to text, the cell formatting will be lost but the text formatting will remain.

I have tested this (not exhaustively) and it seems to work fine in Excel and Word 2000. In 97 it fails with a text colour change mid way through a cell but that was all I could see wrong. I can't imagine any forward compatibility issues.

Dim objWord As Word.Application
Dim rngExcel As Range

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Add

Set rngExcel = [A1:A20]
rngExcel.Copy
objWord.Selection.Paste
Application.CutCopyMode = False

objWord.ActiveDocument.Tables(1).Rows.ConvertToText Separator:=wdSeparateByParagraphs

' Filename = InputBox etc. here

objWord.ActiveDocument.SaveAs Filename

' Or "C:\Folder\" & Filename, etc.

objWord.Quit

Set rngExcel = Nothing
Set objWord = Nothing

Enjoy,
Tony

 

[VBAjedi]

Loomah and Tony,

Both suggestions were helpful - have a star! I will probably use Tony's code for the transfer since it transfers the formats too (nicely done!), but I like the exposure Loomah gave me to working with individual lines in my Word document, and will almost definitely use that in this project as well.

Thanks again, both! You're both a big part of what makes this forum such a great resource. . .


VBAjedi