manish_ti
01-19-2012, 10:26 AM
I have to copy the excel data in word file. My Excel is having(18 rows , 30 columns).
I want to paste these data in word for reporting purpose, that report need proper header for each tables and users wise details and tables should be pasted one after another in word file.
Since , excel file has 30 columns so pasting all data together on word will not filt properly, so I divided in three parts(10 columns each) and was trying to paste but not able to paste table one after another, the codes are fiven below.
I am attaching the "RawData" file which has Raw data and expected results(as I want in word file) sheets.
Your Help will be highly appreciated. I believe your expertise will help here.
Thanks in advance.
Code:-
---------------------------------------------------------------------
Private Sub Copy _To_Word()
Dim NoofReports As Integer
Workbooks.Open ("C:\Sample\report") ' path for file
Dim AppWord As Word.Application
Set AppWord = CreateObject("Word.Application")
AppWord.Documents.Add
AppWord.Visible = True
lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
If (LastCol Mod 10) = 0 Or (LastCol Mod 10) >= 5 Then
NoofReports = (LastCol / 10)
Else
NoofReports = (LastCol / 10)
NoofReports = NoofReports + 1
End If
i = 1
n = 2
For i = 1 To NoofReports
Union(Range(Cells(1, 1), Cells(lastrow, 1)), Range(Cells(1, n), Cells(lastrow, n + 8))).Select
Selection.Copy
AppWord.Selection.Paste
Application.CutCopyMode = False
Set AppWord = Nothing
n = n + 9
Next i
End Sub
I want to paste these data in word for reporting purpose, that report need proper header for each tables and users wise details and tables should be pasted one after another in word file.
Since , excel file has 30 columns so pasting all data together on word will not filt properly, so I divided in three parts(10 columns each) and was trying to paste but not able to paste table one after another, the codes are fiven below.
I am attaching the "RawData" file which has Raw data and expected results(as I want in word file) sheets.
Your Help will be highly appreciated. I believe your expertise will help here.
Thanks in advance.
Code:-
---------------------------------------------------------------------
Private Sub Copy _To_Word()
Dim NoofReports As Integer
Workbooks.Open ("C:\Sample\report") ' path for file
Dim AppWord As Word.Application
Set AppWord = CreateObject("Word.Application")
AppWord.Documents.Add
AppWord.Visible = True
lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
If (LastCol Mod 10) = 0 Or (LastCol Mod 10) >= 5 Then
NoofReports = (LastCol / 10)
Else
NoofReports = (LastCol / 10)
NoofReports = NoofReports + 1
End If
i = 1
n = 2
For i = 1 To NoofReports
Union(Range(Cells(1, 1), Cells(lastrow, 1)), Range(Cells(1, n), Cells(lastrow, n + 8))).Select
Selection.Copy
AppWord.Selection.Paste
Application.CutCopyMode = False
Set AppWord = Nothing
n = n + 9
Next i
End Sub