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. :)