PDA

View Full Version : Solved: change font size.. etc from excel to word



white_flag
08-24-2011, 04:14 AM
Hello

I have an excel file that will create an doc file. Will copy some rows from excel and pasted as tabke in an new document created. this is my code:
What I can not do it is to change the font.name , and the rest from the font, size, bold etc ...

code for change the font size, etc ...


For i = 1 To wdDoc.Tables.Count

wdDoc.Tables(i).Select
With Selection
.Font.Bold = True
.Font.Italic = False
.Font.Name = "Arial"
.Font.Size = "20"
End With

Next i



entire code


Option Explicit

Sub CopyWorksheetsToWord()

Dim OFile As String
Dim filetoopen As String
Dim WeDone As Long
Dim tableTemp As Table
Dim rngTemp As Range
Dim i As Long
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim ws As Worksheet

With Application
.ScreenUpdating = False
.StatusBar = "Creating new document..."
End With

Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add

'Save for closing
OFile = ActiveWorkbook.Name


For Each ws In ActiveWorkbook.Worksheets
ws.Range("B1").Copy
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
ws.Range("A25:K45").Copy
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
Application.CutCopyMode = False
If Not ws.Name = Worksheets(Worksheets.Count).Name Then
With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range
.InsertParagraphBefore
.Collapse Direction:=wdCollapseEnd
'.InsertBreak Type:=wdPageBreak
End With
End If



Next ws
wdApp.Visible = True
For i = 1 To wdDoc.Tables.Count

wdDoc.Tables(i).Select
With Selection
.Font.Bold = True
.Font.Italic = False
.Font.Name = "Arial"
.Font.Size = "20"
End With

Next i
For i = 1 To wdDoc.Tables.Count
wdDoc.Tables(i).AutoFitBehavior wdAutoFitWindow
Next i



For i = 1 To wdDoc.Tables.Count
wdDoc.Tables(i).ConvertToText Separator:=wdSeparateByTabs, NestedTables:=True
On Error Resume Next
Next i


If ActiveWorkbook.Name <> OFile Then
ActiveWorkbook.Close False
End If

' apply normal view
With wdApp.ActiveWindow
If .View.SplitSpecial = wdPaneNone Then
.ActivePane.View.Type = wdPrintView
Else
.View.Type = wdPrintView
End If
End With

wdApp.Visible = True

Set wdDoc = Nothing
Set wdApp = Nothing
Set tableTemp = Nothing
Set rngTemp = Nothing
Set ws = Nothing

'Reset
With Application
.StatusBar = False
.ScreenUpdating = True
End With
End Sub


any ideea?

Kenneth Hobs
08-24-2011, 08:04 AM
I don't see where you added the tables. See if the methods in this thread helps. http://vbaexpress.com/forum/showthread.php?p=168731

white_flag
08-24-2011, 09:27 AM
hello Kenneth

when it paste data from xecel with will be paste as table ?


For Each ws In ActiveWorkbook.Worksheets
ws.Range("B1").Copy
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
ws.Range("A25:K45").Copy
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
Application.CutCopyMode = False
If Not ws.Name = Worksheets(Worksheets.Count).Name Then
With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range
.InsertParagraphBefore
.Collapse Direction:=wdCollapseEnd
'.InsertBreak Type:=wdPageBreak
End With
End If



Next ws



then select the table an apply the changes



For i = 1 To wdDoc.Tables.Count

wdDoc.Tables(i).Select
With Selection
.Font.Bold = True
.Font.Italic = False
.Font.Name = "Arial"
.Font.Size = "20"
End With

Next i


if I look in word
the table is selected but the selection dasn't change:



With Selection
.Font.Bold = True
.Font.Italic = False
.Font.Name = "Arial"
.Font.Size = "20"
End With

Kenneth Hobs
08-24-2011, 09:30 AM
It shows tables added but it does not look good to me. Maybe you can post an example workbook and what the MSWord file would look like. When you paste, you get alot of formatting codes that you may not want in the MSWord file. I suspect that you can get your formatting setup the way you want if you just insert the values into table cells. More tinkering is needed to be sure.

white_flag
08-24-2011, 10:55 AM
please look in attachment

Dave
08-24-2011, 08:28 PM
wdDoc.Tables(1).Cell(1, 1).Range.Font.Size = 14

Change the table cell font individually instead of the whole table seems to work. Perhaps, for each cell in table loop to set font? HTH. Dave

white_flag
08-30-2011, 05:01 AM
For i = 1 To wdDoc.Tables.Count
wdDoc.Tables(i).Select
wdDoc.Tables(i).AutoFitBehavior wdAutoFitWindow
With wdDoc.Tables(i).Range
.Font.Bold = False
.Font.Italic = False
.Font.Name = "Trebuchet MS"
.Font.Size = "9"
End With
Next i