• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Format the word content copied from excel

K

kaushik03

Member
Hi all,


I have a data set that is getting stored in a sheet(of the same workbook) from user's input(through userform).

The data which is stored in the sheet(sheet1) is in the following format(rows are added one after another under the following headings as the user gives their input through userform):


Employee Name EmployeeID Name of Idea Idea Synopsis Process_Impact


The job is:

I am copying the data from sheet1(under a loop), pasting it into the "template" sheet (of same workbook),generating a word doc, copying the template data and paste the same into the word doc as excel table.For each row of data I am creating a separate word doc.


The problem I am facing is:

Whenever the excel data is pasted into the newly created word doc, the formating of the word content is going for a toss, and I am not able to manage this portion of the work.I have tried different ways to maintain the proper "look and fill" of the word content but not able to manage it properly.


I want the word doc to be created in the following format


Employee Name: ____________________________


ID: ______________


Name of the Idea: _______________________________________________________________________


Synopsis:

_______________________________________________________________________


_______________________________________________________________________


_______________________________________________________________________


_______________________________________________________________________


_______________________________________________________________________


Benefits / Process Impact:

_______________________________________________________________________


_______________________________________________________________________


_______________________________________________________________________


______________________________________________________________________


As of now I have tried in this way to acheive the same:


Sub NewWordDoc()

Dim tempidea As String

Dim r As Integer

r = 1

'Dim t As Word.Range

Dim appWD As Word.Application


Set appWD = CreateObject("Word.Application.12")

appWD.Visible = True


Sheets("Data").Select


FinalRow = Range("A9999").End(xlUp).Row

For i = 2 To FinalRow

tempidea = "Idea" & r

Sheets("Data").Select

Range("A" & i & ":E" & i).Copy

Sheets("Template").Select

Range("D3").PasteSpecial Transpose:=True

Range("A1:F8").Copy

appWD.Documents.Add

appWD.Selection.Paste

' With appWD.Selection

' .PasteExcelTable linkedtoexcel:=False, wordformatting:=True, RTF:=False

' .TypeParagraph

' End With

' Set t = appWD.Documents.Add.Content

'' t.Paste

' With t 'working with the table range

' .Style = "GreenBar" 'set the style created for the table

' 'we can use the range object to do some more formatting

' 'here, I'm matching the table with using the Excel range's properties

' .Tables(1).Columns.SetWidth (Rng.Width / Rng.Columns.Count), wdAdjustSameWidth

' End With

appWD.ActiveDocument.SaveAs Filename:=tempidea

r = r + 1

appWD.ActiveDocument.Close

Next i

appWD.Quit


End Sub


Please help me understand what should I exactly do to create a well formatted word doc from excel data.


Regards,

Kaushik
 
Ok....I have solved it. Here it goes:


Sub NewWordDoc()


Dim FinalRow As Integer

Dim r As Integer

Dim WordApp As Word.Application

Dim WordDoc As Word.Document

FinalRow = Range("A9999").End(xlUp).Row

r = 1

For i = 2 To FinalRow


Set WordApp = CreateObject("Word.Application")

Set WordDoc = WordApp.Documents.Add

SaveAsName = ThisWorkbook.Path & "" & "Idea" & r & ".doc"

WordApp.Visible = True

'FinalRow = Range("A9999").End(xlUp).Row


If WordDoc.ActiveWindow.View.SplitSpecial <> wdPaneNone Then

WordDoc.ActiveWindow.Panes(2).Close

End If

If WordDoc.ActiveWindow.ActivePane.View.Type = wdNormalView Or WordDoc.ActiveWindow. _

ActivePane.View.Type = wdOutlineView Then

WordDoc.ActiveWindow.ActivePane.View.Type = wdPrintView

End If


WordDoc.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader

With WordApp.Selection

.ParagraphFormat.Alignment = wdAlignParagraphCenter

.Font.Name = "Calibri"

.Font.Size = 14

.TypeText Text:="Idea Submission Form"

.HomeKey Unit:=wdLine, Extend:=wdExtend

End With


WordDoc.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter

With WordApp.Selection

.Font.Size = 10

.Font.Name = "Calibri"

.Paragraphs(1).Alignment = wdAlignParagraphRight

.TypeText Text:="Page "

.Fields.Add Range:=.Range, Type:=wdFieldEmpty, Text:= _

"PAGE ", PreserveFormatting:=True

.TypeText Text:=" of "

.Fields.Add Range:=.Range, Type:=wdFieldEmpty, Text:= _

"NUMPAGES ", PreserveFormatting:=True

End With


WordDoc.ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument


With WordApp.ActiveDocument.PageSetup

.TopMargin = Application.InchesToPoints(0.8)

.BottomMargin = Application.InchesToPoints(0.8)

.LeftMargin = Application.InchesToPoints(0.7)

.RightMargin = Application.InchesToPoints(0.7)

End With


With WordApp

With .Selection

.Font.Name = "Calibri"

.ParagraphFormat.Alignment = wdAlignParagraphJustify

.Font.Size = 12

.Font.Bold = True

.Font.Underline = True

.TypeText Text:="Name : "

.Font.Underline = False

.Font.Bold = False

If Range("A" & (i)).Value <> "" Then

.TypeText Text:=Range("A" & (i)).Value

End If

.TypeParagraph

.TypeParagraph


.Font.Name = "Calibri"

.ParagraphFormat.Alignment = wdAlignParagraphJustify

.Font.Size = 12

.Font.Bold = True

.Font.Underline = True

.TypeText Text:="OHR ID : "

.Font.Underline = False

.Font.Bold = False

If Range("B" & (i)).Value <> "" Then

.TypeText Text:=Range("B" & (i)).Value

End If

.TypeParagraph

.TypeParagraph

.Font.Name = "Calibri"

.ParagraphFormat.Alignment = wdAlignParagraphJustify

.Font.Size = 12

.Font.Bold = True

.Font.Underline = True

.TypeText Text:="Name of Idea : "

.Font.Underline = False

.Font.Bold = False

If Range("C" & (i)).Value <> "" Then

.TypeText Text:=Range("C" & (i)).Value

End If

.TypeParagraph

.TypeParagraph

.Font.Name = "Calibri"

.ParagraphFormat.Alignment = wdAlignParagraphJustify

.Font.Size = 12

.Font.Bold = True

.Font.Underline = True

.TypeText Text:="Synopsis : "

.Font.Underline = False

.Font.Bold = False

If Range("D" & (i)).Value <> "" Then

.TypeText Text:=Range("D" & (i)).Value

End If

.TypeParagraph

.TypeParagraph


.Font.Name = "Calibri"

.ParagraphFormat.Alignment = wdAlignParagraphJustify

.Font.Size = 12

.Font.Bold = True

.Font.Underline = True

.TypeText Text:="Process Impact : "

.Font.Underline = False

.Font.Bold = False

If Range("E" & (i)).Value <> "" Then

.TypeText Text:=Range("E" & (i)).Value

End If

.TypeParagraph

.TypeParagraph


End With

End With


'WordApp.Dialogs(wdDialogFilePrint).Display


With WordApp

.ActiveDocument.SaveAs Filename:=SaveAsName

.ActiveDocument.Close

.Quit

End With

r = r + 1

Next i


Set WordDoc = Nothing

Set WordApp = Nothing


Application.StatusBar = ""

MsgBox "Idea Form was created and saved in " & ThisWorkbook.Path


End Sub


Kaushik
 
Hi, kaushik03!


Considered uploading a sample input file (workbook) and a sample output file (document) with the output formatted manually set as an example?


Regards!
 
You must log in or register to reply here.
Back
Top