Auto generate headings

[Staal]

Hi,

I have a word document with only 10 lines of bold text. I would like to know how I transform this text from "normal to "heading".

I hope you are able to help me with this problem.

Best regards,

Soren

[lucas]

I'm guessing there is more to this than you have given us so here's an answer to the question asked so far.

Selection.Style = ActiveDocument.Styles("Heading 1")

[fumei]

1. you should never use Normal stle. I never do. Ever. I never use Heading 1 either (or any other built-in style). I only use my own styles. That way I always know what is what.

2. Steve is assuming you are selecting the bold text, which is a fair assumption. If you NOT selecting:

Dim oPara As Paragraph
For Each oPara In ActiveDocument.Paragraphs
If oPara.Range.Font.Bold = True Then
oPara.Style = "Heading 1"
End If
Next
The above has my own assumptions. Primaruly, that your "lines" are actually paragraphs.

[Staal]

Hi Fumei,

Your assumption about the lack of selection was correct so your extended code helped a lot.

However, my "lines" are acutally not paragraphs but cells inside a table. I would highly appreciate any help in modifying the above so that it can be used on cells instead of paragraphs.

Thanks for all your help so far...

[fumei]

This is why is best to try and be clear, and specific.

Why on earth would you be making text in a table cell a Heading 1 style? please explain.

[Staal]

To describe my problem in further I have the following document in excel:

Company Contact email Phone
Microsoft Mr. Gates gates@micro.com 1234
Dell Mr. Dell dell@dell.com 2345
Apple Mr. Jobs jobs@apple.com 3456

I want to convert this into a word document which looks like this:

Microsoft (heading2)
Contact Mr. Gates
Email gates@micro.com
Phone 1234

Dell (heading2)
Contact Mr. Dell
Email dell@dell.com
Phone 2345

The code used to do the above is:

Private Sub Command1_Click()
' Use Project, References menu to add
' Microsoft Word & Excel Object Libraries
Const XL_DOC As String = "C:\Companies.xls"
Const WD_DOC As String = "C:\Companies.dotx"

Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim wdTable As Word.Table

Set xlBook = xlApp.Workbooks.Open(Filename:=XL_DOC, ReadOnly:=True)
Set wdDoc = wdApp.Documents.Add
wdApp.Visible = True

Set wdTable = wdDoc.Tables.Add(Range:=wdApp.Selection.Range, _
NumRows:=200, NumColumns:=2, DefaultTableBehavior:=wdWord9TableBehavior, _
AutoFitBehavior:=wdAutoFitFixed)

With xlBook.Worksheets("Sheet1")
Range("A1").Select

wdTable.Borders.OutsideLineStyle = wdLineStyleNone
wdTable.Borders.InsideLineStyle = wdLineStyleNone

Multiplier = Application.CountA(Range("A1:M1")

With xlBook.Worksheets("Sheet1")

Range("A1").Select
For i = 1 To 8

ActiveCell.Value = i
wdTable.Cell(i, 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i, 2).Range.Text = .Cells(2, i).Text

For j = 1 To 10

wdTable.Cell(i + (j * Multiplier), 1).Range.Text = .Cells(1, i).Text
wdTable.Cell(i + (j * Multiplier), 2).Range.Text = .Cells(2 + j, i).Text
Next j

'bold the company names

wdTable.Cell(1 + (10 * i), 1).Range.Font.Bold = True

Next

End With

End Sub


But in order to make a table of contents of the companies I would like for the company name to be in the style of a header. In order for me to do this I was taking a two step approach:

1. Making the company names bold

2. Transforming the bold text to a specific style (That was were this thread started)

Therefore, I would like help solving either tranforming the bold text into a specific style or preferably changing the style of the text directly without having to make it bold first.

I hope this was specific enough and I would highly appreciate any help from you.

[fumei]

1. what application are you running this from? Not Excel or Word, as your code is making an instance of each. If you are in Excel, you do not need an Excel instance. If you are in Word, you do not need a Word instance. So what application are you in?

2. "preferably changing the style of the text directly without having to make it bold first."

This can be done. You can indeed apply a style directly, and indeed it is the better way.
wdTable.Cell(x, y).Range.Style = "Heading 2"


3. I still fail to see why this is in a table.

[Staal]

1. As this is the first time I am using VBA I might have some unnecessary lines, so if the code can be made simpler I am open to suggestions (I am running from an excel instance)

2. When using the line given (wdTable.Cell(x, y).Range.Style = "Heading 2" ) it shows an error message:

Run-time error ´5834´:
Application-defined or object defined error

This error does not happen when I change the format of the cells to e.g. bold

3. I used the table format in order to improve the layout of the word document. However, I think the way I have defined the table in word is the cause of the problem.

[fumei]

" (wdTable.Cell(x, y).Range.Style = "Heading 2" "

Are you using YOUR parameters for x and y?

"This error does not happen when I change the format of the cells to e.g. bold"

Really? Post your full code please.

If you are running this from Excel, you do not need to make another instance of Excel.
Dim xlApp As New Excel.Application

Just use the instance you are using.
Set xlBook = Application.Workbooks.Open(Filename:=XL_DOC, ReadOnly:=True)

In theory, I see nothing wrong with what you have done to create the table. It is 200 rows/2 columns.

[Staal]

For some odd reason the styles started working after i restarted the computer. I highly appreciate all of your help and my final code was as follows:


Sub GenerateSubsidiaries()

Sheets("Subsidiaries").Select

Dim wdApp As New Word.Application 'a new instance of Word
Dim wdDoc As Word.Document 'our new Word document
Dim wdTable As Word.Table
Dim xlBook As Excel.Workbook
Dim multiplier1 As Long
Dim multiplier2 As Long

Set wdDoc = wdApp.Documents.Add

'Turn some stuff off while the macro is running
Application.ScreenUpdating = False
Application.EnableEvents = False

'generate multipliers to make it dynamic
multiplier1 = Sheets("subsidiaries").Application.CountA(Range("A1:M1"))
multiplier2 = Sheets("subsidiaries").Application.CountA(Range("A2:A500"))

'generate table in word
Set wdTable = wdDoc.Tables.Add(Range:=wdApp.Selection.Range, _
NumRows:=((multiplier1 + 2) * multiplier2 + 2), NumColumns:=2, DefaultTableBehavior:=wdWord9TableBehavior, _
AutoFitBehavior:=wdAutoFitContent)

'change border style
wdTable.Borders.OutsideLineStyle = wdLineStyleNone
wdTable.Borders.InsideLineStyle = wdLineStyleNone

'insert information about the first company
For i = 1 To multiplier1
wdTable.Cell(i + 3, 1).Range.Text = Sheets("Subsidiaries").Cells(1, i).Text
wdTable.Cell(i + 3, 2).Range.Text = Sheets("Subsidiaries").Cells(2, i).Text

'bold the company names
wdTable.Cell(1, 1).Range.Font.Italic = True
wdTable.Cell(3, 1).Range.Font.Bold = True
wdTable.Cell(3 + (i * (multiplier1 + 2)), 1).Range.Font.Bold = True

For j = 1 To multiplier2
wdTable.Cell((3 + i) + (j * (multiplier1 + 2)), 1).Range.Text = Sheets("Subsidiaries").Cells(1, i).Text
wdTable.Cell((3 + i) + (j * (multiplier1 + 2)), 2).Range.Text = Sheets("Subsidiaries").Cells(2 + j, i).Text

'insert company name
wdTable.Cell(1, 1).Range.Text = "Subsidiaries"
wdTable.Cell(3, 1).Range.Text = Sheets("Subsidiaries").Cells(2, 1).Text
wdTable.Cell(3 + (j * (multiplier1 + 2)), 1).Range.Text = Sheets("Subsidiaries").Cells(2 + j, 1).Text

'bold the company names
wdTable.Cell(1, 1).Range.Style = "Heading 1"
wdTable.Cell(3, 1).Range.Style = "Heading 2"
wdTable.Cell(3 + (j * (multiplier1 + 2)), 1).Range.Style = "Heading 2"
Next j

Next

'Turn everything back on
Application.ScreenUpdating = True
Application.EnableEvents = True

'Make our Word session visible
wdApp.Visible = True

End Sub

[fumei]

Where do you Set the Word instance? I am assuming you actually have it your real code, as the following should not work either.
Sheets("Subsidiaries").Cells(1, i).Text
No dot.