Solved: Paste from Excel to Word Without Cells

[Daxton A.]

Now I have never ever programmed in word so I'm a newbie. This is what I want, it's not that important but want to copy the cells out of excel and paste them in Word as just the text. B/c when a cell/cells is copied & pasted into word, it gets pasted w/a set a borders around it. I'm sure there's a way to just take the borders off, you can tell me that 2 lol, but I just wanna learn Word VBA. The reason I wanna make a macro to do this is b/c the Wb has about 30 sheets on it and there's about 20 cells on each sheet. I mean like I said it's not that important but I just want to learn to use the Word VBA b/c I've never used it. Just about the only thing I've used in Vba is excel. Anyway like I said IT'S NOT THAT IMPORTANT! LOL

Thank you,
Daxton

[Scottie P]

Hi Daxton.

I guess that there are a couple of things that need to be considered in your question; hope I didn't miss something.

Are you opening Word and having it go out to Excel for data or are you opening Excel first and having it send data to Word?
(Also, if we are starting from Excel we will probably end up moving this thread over to the Excel VBA forum)
*****
How should the data be formatted when it is placed into a Word document?
You say it is supposed to have the worksheet data (without the cells around it). As an example, if I were going to do this for only a few cells, I would copy my Excel data, open Word and Under the Edit Menu select Paste Special... > Unformatted Text. This way I would have only the data that was in the cells. I think the Word VBA for that would be (or similar to):
[VBA]
Selection.PasteSpecial DataType:=wdPasteText
[/VBA]
But this will give you the layout as it is in Excel; so if you wanted the data to appear as sentence/paragraph form then we'd need to consider that.

Post back when you can

Scott

[parry]

Hi Daxton, I dont know the Word object model particularly well myself but heres an example on how to open an xl book and copy values from column A in the first sheet into a predetermined bookmark in the word document. I just used the macro recorder to find the word methods but I believe Word uses range as well so you could have accessed the bookmark via the Word Range object.

[VBA]Sub CopyFromXl()
Dim Xl As Excel.Application, Wb As Excel.Workbook, i As Integer
Dim XlOpen As Boolean
'//This code requires a reference to the Excel object library to use some Xl methods
'//such as End(xlup) and Dim objects Xl and Wb (otherwise just declare as Object)

On Error Resume Next
Set Xl = GetObject(, "Excel.Application") 'Select Xl app if open
If Err.Number <> 0 Then 'If Excels not already open then open an instance
Set Xl = CreateObject("Excel.Application")
Xl.Visible = True 'If you want Excel to be visible. Its invisible by default.
Else
XlOpen = True 'an indicator so we know whether to close Xl app or not when finished
End If
On Error GoTo 0

'Open workbook
On Error Resume Next
Set Wb = Xl.Workbooks.Open("C:\Text.xls")
If Wb Is Nothing Then
MsgBox "Unable to open file 'C:\Text.xls'!"
On Error GoTo 0
GoTo CleanUp
End If
On Error GoTo 0
With Wb.Sheets(1)
'see if theres anything to copy
If .Cells(1, 1) = vbNullString Then
MsgBox "There is no text to copy!"
GoTo CleanUp
End If

'Go to bookmark called 'bkmk1' where you want to insert values
Selection.GoTo What:=wdGoToBookmark, Name:="Bkmk1"

'Copy column A values to Word document
For i = 1 To .Range("A65536").End(xlup).Row
If Not IsError(.Cells(i, 1)) Then
Selection.TypeText Text:=.Cells(i, 1).Text 'insert cells formatted value
Selection.TypeParagraph 'carriage return to next line
End If
Next i
End With
CleanUp:
If XlOpen = False Then Xl.Quit 'close xl if we started it otherwise leave open
Set Xl = Nothing
Set Wb = Nothing
End Sub [/VBA]

[lucas]

Parry,
This code works well if I want to import just col A from excel as long as you set the reference to the Excel object library and set the bookmark. I added 1 line to your code to get it to copy both Col A and Col B but I can't figure out how to put a seperator such as a space between the data from A & B. Any ideas?

[VBA]
For i = 1 To .Range("A65536").End(xlup).Row
If Not IsError(.Cells(i, 1)) Then
Selection.TypeText Text:=.Cells(i, 1).Text 'insert cells formatted value
Selection.TypeText Text:=.Cells(i, 2).Text 'insert cells formatted value
Selection.TypeParagraph 'carriage return to next line
[/VBA]

[parry]

Hi Lucas, untested but should be just...

[VBA] For i = 1 To .Range("A65536").End(xlup).Row
If Not IsError(.Cells(i, 1)) Then
Selection.TypeText Text:=.Cells(i, 1).Text & " " 'insert cells formatted value
Selection.TypeText Text:=.Cells(i, 2).Text 'insert cells formatted value
Selection.TypeParagraph 'carriage return to next line [/VBA]

or...
[VBA]For i = 1 To .Range("A65536").End(xlup).Row
If Not IsError(.Cells(i, 1)) Then
Selection.TypeText Text:=.Cells(i, 1).Text 'insert cells formatted value
Selection.TypeText Text:=" "
Selection.TypeText Text:=.Cells(i, 2).Text 'insert cells formatted value
Selection.TypeParagraph 'carriage return to next line[/VBA]

[lucas]

Thanks Parry,

This one works:
[VBA]
Selection.TypeText Text:=.Cells(i, 1).Text & " " 'insert cells formatted value
Selection.TypeText Text:=.Cells(i, 2).Text 'insert cells formatted value

[/VBA]
I'm still reading the books and help files but sometimes an example will show me a lot. Thanks again.

[parry]

Great . You could also shorten the code to one line like this...

[VBA]Selection.TypeText Text:=.Cells(i, 1).Text & " " & .Cells(i, 2).Text [/VBA]

[Daxton A.]

Thanks for all of your help.

[Anne Troy]

Is this solved, then, Dax?
Also, you should make sure you read about VBA training in the newsletter that was sent last night, Dax.

[Daxton A.]

It's solved Dreamboat...Thanks again for all the help.