Rejje
12-07-2010, 02:16 PM
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
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