macro button for copying excel data into multiple word docs

[mbbx5va2]

Hi

I was wondering if somebody could point me in the right direction with this.

Suppose you had an excel worksheet with numerical data in cells A1:A3. So say A1=2, A2=3, A3=4 And you wanted to loop through each row in that column of data using a macro button. As it loops through, it pastes the values in each of the three rows into a separate word document. So after clicking on the macro button I should have three word documents open up each with one of the three values above pasted.

If I can get similar code then I can have a go at adapting it.

Thanks

[werafa]

Not sure about the code, but....

you will need to break this down into:
1: master loop
2: create and open new word doc (name, save location etc) (set the word doc as an excel oject as part of this - will make the calls to it much easier)
3: read and write the data. you could use copy and paste, or alternatively, you could read and pass the cell values.

The macro recorder may not work well as you will have to switch applications - and the recorder doesn't always follow the switch

try
http://www.ozgrid.com/forum/showthread.php?t=50936
for a start on making the new file

[mbbx5va2]

Thanks for the tips. I will have a go at writing up the code and get back to you if there are any major issues.

Cheers

[snb]

sub M_snb()
  with createobject("Word.application")
    .visible=true

    for each cl in sheets("sheet1").rows(1).specialcells(2)
       .documents.add.Paragraphs(1).Range = cl.Value
    next
  end with
End Sub

[mbbx5va2]

Hi thanks for the help guys. I have managed to create the macro button to produce the multiple word documents. I will have other questions for a different spreadsheet I am going to work on too. I will submit a new thread for this.

Cheers

[snb]

Did you use #4 ?

[mbbx5va2]

Did you use #4 ?

Yes I did - thanks.
These are two parts of the code that worked for me. I added extra columns on the spreadsheet so had to define a few more variables for output on the MS word documents.

Dim WordApp As Object
    Dim Data As Range
    Dim Records As Integer, i As Integer
    Dim Region As String, Surname As String
    Dim Remediation As String, Inte As String, Ta As String, TotalRem As String, Pay As String
    Dim SaveAsName As String

Then eventually...

'Cycle through all records in Sheet2
    Records = Application.CountA(Sheets("Sheet2").Range("A:A"))
    For i = 1 To Records
'       Update status bar progress message
        Application.StatusBar = "Processing Record " & i

'       Assign current data to variables
        Region = Data.Cells(i, 1).Value
        Surname = Data.Cells(i, 2).Value
        Remediation = Data.Cells(i, 3).Value
        Inte = Data.Cells(i, 4).Value
        Ta = Data.Cells(i, 5).Value
        TotalRem = Data.Cells(i, 6).Value

        Pay = Data.Cells(i, 7).Value

[snb]

did you know ?

sub M_snb()
  sn = sheets("Sheet2").Cells(1).CurrentRegion

  with createobject("Word.Application")
    For j = 1 To UBound(sn)
      .documents.add.paragraphs(1).range = Join(Application.Index(sn, j))
    Next
  end with
End Sub

[mbbx5va2]

did you know ?

sub M_snb()
  sn = sheets("Sheet2").Cells(1).CurrentRegion

  with createobject("Word.Application")
    For j = 1 To UBound(sn)
      .documents.add.paragraphs(1).range = Join(Application.Index(sn, j))
    Next
  end with
End Sub

Hi

No I didn't. Thank you.