excel to word

D

dk_novice

New Member
Joined
Dec 9, 2008
Messages
5
HI all,

Sorry to post this, but i cant seem to figure out whats wrong...
Im trying to copy data from a excel, each line should create a seperate word file. I have created about 35 book marks in the .dot file.
I looked at the code Colin posted:

Sub CopytoWord()
Dim WDApp As Word.Application
Dim WDDoc As Word.Document

On Error GoTo ErrorHandler

Set WDApp = New Word.Application

With WDApp
Set WDDoc = .Documents.Add(Template:="C:\Documents and Settings\minime\Desktop\081208\Detailedtest.dot")
.Visible = True
End With

With WDDoc.Bookmarks
'Amount
.Item("amount").Range.InsertAfter ActiveCell.Value
'Share Amount
.Item("shareamount").Range.InsertAfter (ActiveCell.Value / 1000)
End With
Click to expand...

here is what i think i should do
1. add the macro to the excel sheet.
2. add the With WDDoc.Bookmarks part for each bookmark
3. run it

Now my question is?
How do i edit the WDDoc.Bookmarks part, should it be cells to bookmark name ? if so how do i handle different rows ?

I hope you can help me out here
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Hi,

That code you've picked up uses early binding so you will also need to add a reference to the Word Object library. In the visual basic editor, make sure that the correct project is selected, then go to Tools | References and scroll down the list and tick the Microsoft Word Object Library.

Where you have added bookmarks to your Word template, each bookmark has a name. Let's say four of the bookmark names are One, Two, Three and Four.

Do you actually want to copy and paste into word, or do you just need to transfer the cell values? If you just need to transfer the cell values, you can use the InsertAfter method as outlined on the code sample you found. The core of your code (without errorhandling) would look like this - you just need to change the blue text to suit:

Rich (BB code): 
Sub CopytoWord()
    Dim WDApp As Word.Application
    Dim WDDoc As Word.Document
 
    Set WDApp = New Word.Application
 
    With WDApp
         Set WDDoc = .Documents.Add(Template:="C:\Documents and Settings\minime\Desktop\081208\Detailedtest.dot")
        .Visible = True
    End With
 
    With WDDoc.Bookmarks
        .Item("One").Range.InsertAfter Worksheets("Sheet1").Range("A1").Value
        .Item("Two").Range.InsertAfter Worksheets("Sheet1").Range("B6").Value
        .Item("Three").Range.InsertAfter Worksheets("Sheet1").Range("E15").Value
        .Item("Four").Range.InsertAfter Worksheets("Sheet1").Range("F23").Value
    End With 
 
    Set WDDoc = Nothing
    Set WDApp = Nothing
 
End Sub

If you want to insert a group of cells' values after a bookmark, then the way you do it will depend on how you want it to look in the Word document. If you give us the details we can help you with that.

Also, I'm not sure what you mean when you say 'each line should create a seperate word file' but, if this procedure needs to produce many documents all in a single run, then you will probably need to incorporate a loop which will need to be carefully placed. Again, more details and we should be able to help.

HTH
Colin
 
Upvote 0
Thanks alot Colin that worked great.

Here is what i have done:
With WDDoc.Bookmarks
.Item("Grouping").Range.InsertAfter Worksheets("Worksheet").Range("D4").Value
.Item("Title").Range.InsertAfter Worksheets("Worksheet").Range("B4").Value
.Item("PainNo").Range.InsertAfter Worksheets("Worksheet").Range("A4").Value
.Item("CaSiS").Range.InsertAfter Worksheets("Worksheet").Range("J4").Value
.Item("CtObE").Range.InsertAfter Worksheets("Worksheet").Range("K4").Value
.Item("Delta").Range.InsertAfter Worksheets("Worksheet").Range("L4").Value
.Item("P1").Range.InsertAfter Worksheets("Worksheet").Range("M4").Value
.Item("Effect").Range.InsertAfter Worksheets("Worksheet").Range("N4").Value
.Item("Why").Range.InsertAfter Worksheets("Worksheet").Range("O4").Value
.Item("How").Range.InsertAfter Worksheets("Worksheet").Range("P4").Value
.Item("P2").Range.InsertAfter Worksheets("Worksheet").Range("Q4").Value
.Item("Simplify").Range.InsertAfter Worksheets("Worksheet").Range("R4").Value
.Item("Standardize").Range.InsertAfter Worksheets("Worksheet").Range("S4").Value
.Item("Owner").Range.InsertAfter Worksheets("Worksheet").Range("T4").Value
.Item("Asis").Range.InsertAfter Worksheets("Worksheet").Range("U4").Value
.Item("CTools").Range.InsertAfter Worksheets("Worksheet").Range("V4").Value
.Item("CRes").Range.InsertAfter Worksheets("Worksheet").Range("W4").Value
.Item("Tobe").Range.InsertAfter Worksheets("Worksheet").Range("X4").Value
.Item("NRes").Range.InsertAfter Worksheets("Worksheet").Range("Y4").Value
.Item("NTools").Range.InsertAfter Worksheets("Worksheet").Range("Z4").Value
.Item("NTrain").Range.InsertAfter Worksheets("Worksheet").Range("AA").Value
.Item("P3").Range.InsertAfter Worksheets("Worksheet").Range("AB").Value
.Item("Est").Range.InsertAfter Worksheets("Worksheet").Range("AC").Value
.Item("NextSteps").Range.InsertAfter Worksheets("Worksheet").Range("AD").Value
.Item("P4").Range.InsertAfter Worksheets("Worksheet").Range("AG").Value
.Item("Benefits").Range.InsertAfter Worksheets("Worksheet").Range("AH").Value
.Item("Milestones").Range.InsertAfter Worksheets("Worksheet").Range("AI").Value
.Item("KPI").Range.InsertAfter Worksheets("Worksheet").Range("AJ").Value
.Item("CSF").Range.InsertAfter Worksheets("Worksheet").Range("AK").Value
.Item("P5").Range.InsertAfter Worksheets("Worksheet").Range("AL").Value
.Item("PTotal").Range.InsertAfter Worksheets("Worksheet").Range("AM").Value
End With
Click to expand...

As you see its all row "4" here, and i want it repeated 25 times.
and i dont mind if the output is in one single file or 25 files, they just have to use the same .dot template.

any more great ideas ?

Thanks again

Jameel
 
Upvote 0
I tried some more, its hard when Im used to work in other languages

i want somthing simple like:
for m in `4 to 25`
do
Item("Grouping").Range.InsertAfter Worksheets("Worksheet").Range("D$m").Value
done
Click to expand...

I found the loop functionm but i dont know how to change what I marked as $m in the above
 
Upvote 0
Well, the loop would be like this:
Code: 
For m = 4 to 25
    .Item("Grouping").Range.InsertAfter Worksheets("Worksheet").Range("D" & m).Value
Next m

And this would be nested inside the With WDDoc.Bookmarks... End With Block in the code.

But are you sure this is the output you want in your template?

HTH,
Colin
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Create empty table (with headings) in Word document after some text
Replies
5
Views
157
dwsteyl
D
N
  • Solved
Userform populated w/ word information
Replies
1
Views
352
NdNoviceHlp
NdNoviceHlp
N
  • Question
Delete bookmarks using userform (code is not working as expected)
Replies
6
Views
438
NdNoviceHlp
NdNoviceHlp
C
  • Question
MAIL MERGE EXCEL + WORD + VBA DIRECTORY PROBLEM
Replies
1
Views
606
Cleitonis
C
P
  • Question
Runtime Error 462 when Running Code to Export Excel Values into Word Bookmarks
Replies
6
Views
957
pastelprincess
P

Forum statistics

Threads
1,215,102
Messages
6,123,097
Members
449,096
Latest member
provoking

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top