Solved: Paste from Excel to Word

[Rejje]

Hi!

I'm trying to make make a macro that will work its way down a some 20 cells in a column which contains ranges of areas in the workbook. These areas I want it to copy, then insert into a word document, copy next area and so on.

I don't really need a loop as in a few places the macro need to stop and make other stuff as well as paste some clips centered, some left.

I have tried with some success. It creates a temporary folder, opens the right document and saves as in the end.

I have never worked with multiple applications and vba before and I am completely stuck in dimming, setting and making the right app run get active for copying and pasting.

What I need is to see the structure for copyExcel/pasteWord/copyExcel/pasteWord/...

Below is what I composed so far (fault are inescapable for me as you see):

Sub CreateWordDoc2()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim xlDoc As Excel.Workbook
MkDir Range("V_20200").Value
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open(Range("V_20400").Value)
Set xlDoc = Active.Workbook

xlDoc.Range(Range("V_20500").Select).Copy
wrdDoc.Selection.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafile, Placement:=wdInLine, DisplayAsIcon:=False


If Dir(Range("V_21700").Value) <> "" Then

Kill Range("V_21700").Value

End If

wrdDoc.SaveAs (Range("V_21700").Value)
wrdDoc.Close

wrdApp.Quit
Set wrdDoc = Nothing
Set wrdApp = Nothing

End Sub

[austenr]

A search engine is your best friend.

http://www.mrexcel.com/forum/showthread.php?t=340714

[Bob Phillips]

Public Enum WordConstants
wdInLine = 0
wdPasteText = 2
wdPasteEnhancedMetafile = 9
End Enum

Sub CreateWordDoc2()
Dim wrdApp As Object 'Word.Application
Dim wrdDoc As Object 'Word.Document
Dim xlDoc As Workbook
MkDir Range("V_20200").Value
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open(Range("V_20400").Value)
Set xlDoc = ActiveWorkbook

xlDoc.ActiveSheet.Range("V_20500").Copy
wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:=wdInLine, DisplayAsIcon:=False

If Dir(Range("V_21700").Value) <> "" Then

Kill Range("V_21700").Value
End If

wrdDoc.SaveAs Range("V_21700").Value
wrdDoc.Close

wrdApp.Quit
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub

[Rejje]

Great!

It work except it only pastes the text "TXT_01" into the Word document.

Range("V_20500").Value = TXT_01
TXT_01 is the area in a different worksheet I would like for the macro to select - then paste into Word

I have tried

xlDoc.ActiveSheet.Range(Range(Range("V_20500").Value).Select).Copy

but it does not work either.

What it's supposed to look like?

[Bob Phillips]

Public Enum WordConstants
wdInLine = 0
wdPasteText = 2
wdPasteEnhancedMetafile = 9
End Enum

Sub CreateWordDoc2()
Dim wrdApp As Object 'Word.Application
Dim wrdDoc As Object 'Word.Document
Dim xlDoc As Workbook
MkDir Range("V_20200").Value
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open(Range("V_20400").Value)
Set xlDoc = ActiveWorkbook

With xlDoc.ActiveSheet

.Range(.Range("V_20500").Value).Copy
End With
wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:=wdInLine, DisplayAsIcon:=False

If Dir(Range("V_21700").Value) <> "" Then

Kill Range("V_21700").Value
End If

wrdDoc.SaveAs Range("V_21700").Value
wrdDoc.Close

wrdApp.Quit
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub

[Rejje]

Thanks! However I get:

With xlDoc.ActiveSheet

.Range(.Range("V_20500").Value).Copy ' Error 1004/Program- or object error

End With


Could it be that the area which I want to be copied is in another worksheet? Do I really need xlDoc.ActiveSheet?

[Bob Phillips]

Do you have a named range that is this value .Range("V_20500").Value?

[Rejje]

Yes! Exact text in the cell named V_20500 is TXT_401.

TXT_401 is a named area (2Cx1R) in another worksheet.

V_20500 is a variable pointing to different areas and which is depending on choises one makes in the workbbook (made with Excel code).

That is why I need for this sub to read in cell V_20500 what area to copy.

[Rejje]

By the way: What does Public Enum do?

[Bob Phillips]

It is an enumerated list, which I use to define the Word constants, as I do late-binding, so the variables are not (implicitly) available to me.