thinker_2005
07-03-2006, 08:57 PM
Hi everyone,
I was wondering if it was possible to copy a range of data from excel to word with proper pagebreaks.
Essentially i want the code to copy maybe 2 or 3 tables from excel, copy this into word while having each table copy to its own page within word. Another thing i would like to have is perhaps create a word template file with company logos(i would do this myself, not with a macro) , and have it open the template, and copy the data to this template and save the file. I have been at this for a few days now and i cant seem to get the data to copy with pagebreaks. Right now i have a dirty method which copies and forces me to save a different document for each table, but this could be painful if i were to increase this to say 4-5 tables or more. If anyone could help I would really appreciate it. Thanks .
Private Sub Copy_Keep_Formatting(ByVal t As Integer, ByVal p As Integer)
Dim wApp As Object
Dim wDoc As Object
Dim rnValue As Range
Dim lastrow As Long
lastrow = Sheets(1).Cells(65536, t + 1).End(xlUp).Row
With Sheets(1)
Set rnValue = .Range(Cells(4, t), Cells(lastrow + 1, p))
End With
Set wApp = CreateObject("Word.Application")
Set wDoc = wApp.Documents.Add
With wDoc
rnValue.Copy
.Content.Paste
Application.CutCopyMode = False
'.SaveAs Filename:=ThisWorkbook.Path & "\file.doc"
.Close
End With
wApp.Quit
Set wDoc = Nothing
Set wApp = Nothing
End Sub
I was wondering if it was possible to copy a range of data from excel to word with proper pagebreaks.
Essentially i want the code to copy maybe 2 or 3 tables from excel, copy this into word while having each table copy to its own page within word. Another thing i would like to have is perhaps create a word template file with company logos(i would do this myself, not with a macro) , and have it open the template, and copy the data to this template and save the file. I have been at this for a few days now and i cant seem to get the data to copy with pagebreaks. Right now i have a dirty method which copies and forces me to save a different document for each table, but this could be painful if i were to increase this to say 4-5 tables or more. If anyone could help I would really appreciate it. Thanks .
Private Sub Copy_Keep_Formatting(ByVal t As Integer, ByVal p As Integer)
Dim wApp As Object
Dim wDoc As Object
Dim rnValue As Range
Dim lastrow As Long
lastrow = Sheets(1).Cells(65536, t + 1).End(xlUp).Row
With Sheets(1)
Set rnValue = .Range(Cells(4, t), Cells(lastrow + 1, p))
End With
Set wApp = CreateObject("Word.Application")
Set wDoc = wApp.Documents.Add
With wDoc
rnValue.Copy
.Content.Paste
Application.CutCopyMode = False
'.SaveAs Filename:=ThisWorkbook.Path & "\file.doc"
.Close
End With
wApp.Quit
Set wDoc = Nothing
Set wApp = Nothing
End Sub