Exporting Excel data to Word (VBA)

[SnowBlind]

Hi!

I'm working on a macro that copies data from Excel and then pastes it to Word, after which it saves the document.

Here's the code:

Sub PasteToWord()

Range("A5:A6").Select
Dim WDApp As Word.Application

' Is something selected?

If Not TypeName(Selection) = "Range" Then
MsgBox "Select a Range!", vbExclamation, "No Range Selected"

Else

Set WDApp = CreateObject "Word.Application.9")
WDApp.Visible = True

Selection.copy

WDApp.Documents.Add
WDApp.Selection.Paste
WDApp.ActiveDocument.SaveAs FileName:="C:tempImported.doc"
WDApp.ActiveDocument.Close

Set WDDoc = Nothing
Set WDApp = Nothing
End If
End Sub



Now, I've managed to make it work, but I've got 2 questions:

1. How can I get the macro NOT to copy the cell outlines to the Word document?
2. How do define the empty space left between the words that are pasted to the document?
This message was edited by SnowBlind on 2002-07-11 05:31

 

[nancyo]

I have been trying to do this for a while also, and have formatting problems. I tried "recording" a macro in Word to reformat, but didn't get that to work. I am interested in responses to this as well...

 

[NateO]

Howdy Snowblind & NancyO

I use the following code with the pastespecial to leave the gridlines behind.

<pre>
Sub WordUp()
Dim WdObj As Object, fname As String
fname = Sheets(2).[a1].Value
Set WdObj = CreateObject("Word.Application")
WdObj.Visible = False
Selection.Copy 'Your Copy Range
WdObj.documents.Add
WdObj.Selection.PasteSpecial Link:=False, _
DataType:=wdPasteText, Placement:= _
wdInLine, DisplayAsIcon:=False
Application.CutCopyMode = False
If fname <> "" Then 'make sure fname is not blank
With WdObj
.ChangeFileOpenDirectory "c:temp" 'save Dir
.ActiveDocument.SaveAs Filename:=fname & ".doc"
End With
Else:
MsgBox ("File not saved, naming range was botched, guess again.")
End If
With WdObj
.ActiveDocument.Close
.Quit
End With
Set WdObj = Nothing
End Sub</pre>

I dim as an object, perhaps a little slower, but avoids the library reference.

Not sure I exactly follow question 2, but this should be formatted very much like your spreadsheet. Also, don't edit in Word, will return it to a gridline format.

 

[daniel50096230]

I had code as below:

Dim WdObj As Object, fname As String
fname = "Word"
Set WdObj = CreateObject("Word.Application")
WdObj.Visible = False
Range("A1:I30").Select
Selection.Copy 'Your Copy Range
WdObj.Documents.Add
WdObj.Selection.PasteSpecial Link:=False, _
    DataType:=wdPasteText, Placement:= _
    wdInLine, DisplayAsIcon:=False
Application.CutCopyMode = False
If fname <> "" Then 'make sure fname is not blank
With WdObj
   .ChangeFileOpenDirectory "c:\temp" 'save Dir
    .ActiveDocument.SaveAs Filename:=fname & ".doc"
End With
Else:
MsgBox ("File not saved, naming range was botched, guess again.")
End If
With WdObj
    .ActiveDocument.Close
    .Quit
End With
Set WdObj = Nothing

But it occur error at .ChangeFileOpenDirectory "c:\temp" 'save Dir
..May I know what is the problem?????Anyone can tell me?

 

[daniel50096230]

How can I do so that when I insert new excel data into this word document,it will be insert into the new page so it will not overlapping the existing page.