Coping data from XL to Word Doc

[kklaus]

Below is my code to copy data from a worksheet to a Word Doc. The problem I'm having is that it seems to be copied as a picture or text box. Instead of it pulling in cells A1:F301 as stated in the code, only cells A1:F70 are showing in the .doc file.

My data takes up about 6 pages in Word so all the data is cut off after the 1st page.

Is there another way to copy it in to the .doc file so that it's not a text box or a better way to pull in rows through 301?

Also, can I set the margins for the .doc file in the code?

Sub Export_Word_Attach_Outlook() 
Dim source As Range 
Dim wdApp As Object 
Dim wdDoc As Object 
Dim strdate As String 
Dim OutApp As Object 
Dim OutMail As Object 
Dim strbody As String 

'Create a hidden instance of Word 
Set wdApp = CreateObject("Word.Application") 
'Add a document. 
Set wdDoc = wdApp.Documents.Add 
  
With ActiveSheet 
Set source = .Range("A1:F301").SpecialCells(xlCellTypeVisible) 
source.Copy 
End With 

'Here's where I named the .doc file from a cell on the sheet 
strname = ThisWorkbook.Sheets("Quote Letter").Range("F10").Value & " Quote Letter" 

With wdDoc 
'Paste the source-range into the active document 
.Range.PasteSpecial Link:=False, DataType:=3, _ 
Placement:=0, DisplayAsIcon:=False 

'Save & Close the document 
.SaveAs ThisWorkbook.Path & "\" & strname & ".doc" 
.Close 
End With 

'Close the hidden instance of Word 
wdApp.Quit 

Application.CutCopyMode = False 

'Here's where I'm emailing the .doc
'file as an attachment:
strbody = "Enter Text Here" 

Set OutApp = CreateObject("Outlook.Application") 
Set OutMail = OutApp.CreateItem(0) 
With OutMail 
.to = ThisWorkbook.Sheets("Quote Letter").Range("B16").Value 
.Subject = "Quote for " & ThisWorkbook.Sheets("Quote Letter").Range("F10").Value 
.Body = strbody 
.Attachments.Add ThisWorkbook.Path & "\" & strname & ".doc" 
.Display 
End With 

'Release objects from memory. 
Set wdDoc = Nothing 
Set wdApp = Nothing 
Set OutMail = Nothing 
Set OutApp = Nothing 

'Delete the Word-document. 
Kill ThisWorkbook.Path & "\" & strname & ".doc" 


End Sub

Thanks!
Kathy

 

[TonyJollans]

Hi Kathy,

It's copied as a picture because that's what you ask it to do!!

[blue][tt] PasteSpecial ... Datatype:=3 , ...[/tt][/blue]

If you just do a normal Paste, it will be pasted as a Table. How do you want it to appear?

For your marguns, take a look at ..
[blue][tt] wdDoc.PageSetup.LeftMargin[/tt][/blue]

.. or [blue][tt].TopMargin[/tt][/blue], etc.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[SkipVought]

Hi,

DataType property

    With wdDoc
    'Paste the source-range into the active document
    .Range.PasteSpecial Link:=False, [b]DataType:=wdPasteText[/b], _
    Placement:=0, DisplayAsIcon:=False
    
    'Save & Close the document
    .SaveAs ThisWorkbook.Path & "\" & strname & ".doc"
    .Close
    End With

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[kklaus]

Thanks! I knew it was something stupid I was doing in the code. Below is my updated code.

It still seems to be coming over as a text box, so I guess I'm still doing something wrong in the Paste portion. Also, instead of it being a multi-page document, it's only printing the first page.

With wdDoc
wdDoc.PageSetup.LeftMargin = 0.5
wdDoc.PageSetup.RightMargin = 0.5
wdDoc.PageSetup.TopMargin = 0.5
wdDoc.PageSetup.BottomMargin = 0.5
'Paste the source-range into the active document
.Range.PasteSpecial Link:=False, DataType:=wdPasteText, _
Placement:=0, DisplayAsIcon:=False

'Save & Close the document
.SaveAs ThisWorkbook.Path & "\" & strname & ".doc"
.Close
End With

I would attach the workbook so you could see what it's doing exactly, but it doesn't look like I can do that.

Thanks,
Kathy

 

[SkipVought]

It still seems to be coming over as a text box"

Please explain what you mean. When I execute this code, I get 301 lines of values separated by a [Tab].


Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[kklaus]

When the email is created, I click on the .doc file attachment. When it launches in Word, it's really small and shrinks to 1 page instead of spanning multiple pages. When I click on the text to make a change, a box appears around the entire text like it's a picture or text box.

When I changed my datatype to =2 instead of 3, this seem to bring it over as text/data instead of an image, which was good. The only problem I had there was that it didn't copy our company logo image over from the worksheet to the word doc and the font was so large it spanned to 12 pages in word.

I'm using Office 2003 if that matters.

Any suggestions?

Thanks!
Kathy

 

[TonyJollans]

Hi Kathy,

Do you have a reference to Word? You don't seem to be using anything else from it. Try explicitly putting 2 instead of wdPasteText.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[SkipVought]

Then you need to make 2 copy 'n' paste: one for the text and the other for the image. The DataType is different.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[kklaus]

Thanks for the suggestion. I wrote 2 copy/paste pieces, but the 2nd one overlayed my 1st one. Do you know how I can specify to paste the data after the image?

Thanks!!
Kathy

 

[SkipVought]

Make the image an InLineShape rather than a Shape.



Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[SkipVought]

take a look at these properties

With MyShape
   .RelativeHorizontalPosition = _
        wdRelativeHorizontalPositionCharacter
   .RelativeVerticalPosition = _
        wdRelativeVerticalPositionParagraph
End With

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}