Solved: An EXCEL to WORD Conversion question

[austenr]

I have a macro that makes a greenbar report (every other line light green) that works in EXCEL (Thanks DRJ) that I wish to work in Word. The code is below. How can I adapt this to Word?

[VBA] Sub ColorRows()

Dim LastRow As Long
Dim LastCol As Long
Dim i As Long

LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

LastCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For i = 1 To LastRow Step 2
Range(Cells(i, 1), Cells(i, LastCol)).Interior.ColorIndex = 35
Next i

End Sub [/VBA]

Thanks

[Killian]

If you have 2002/3 (or even 2000, I'm not sure) then Table styles have this feature. You can set up a table style to have different formatting, including shading, on alternate rows (odd or even). You'd just need to copy across the data as a table from Excel and assign the style.

[austenr]

Thanks. Have a couple of questions. The macro is from EXCEL not the data. The data is already a text file. All I need to do is highlight every other row light green. I was using the Styles and could not figure out how to alternate every other row. Example please.

[Killian]

I've attached an example of what I mean. It's pretty basic but it should give you an idea.If you run the Excel macro from the same directory as the word file it should work.
I guess, ultimately you'd have a Word template set up and do a File...New from that and save it out
Here's the code in the workbook:[VBA]Sub Data2Word()


Dim rng As Range
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim t As Word.Range
Dim myWordFile As String

myWordFile = ThisWorkbook.Path & "\DocWithTableStyle.doc"

Set rng = Range("A1").CurrentRegion
rng.Copy

Set wdDoc = wdApp.Documents.Open(myWordFile)

Set t = wdDoc.Content
t.Paste
With t
.Style = "GreenBar"
End With

wdApp.Visible = True
wdApp.Activate

End Sub[/VBA]

[MOS MASTER]

Hi Killian,

Love this simple sollution!
In particular the use of the Table Style to get the formatting part done..(nice thinking there..)

[Zack Barresse]

I smell a Kbase entry!!!

[MOS MASTER]

I smell a Kbase entry!!!

Yepzz...The Please Keep It Simple entry!
(Someone could have dealt with this issue with a whole lot more code...)

[Killian]

OK, I'll add that to my 'to do' list
Needs a couple more lines - new file from a Word template, a little formatting to make sure it fits the page and maybe a save... Excel users don't seem to like seeing Word if they can avoid it

[MOS MASTER]

Excel users don't seem to like seeing Word if they can avoid it

Yepz...is the same with Word users versus Excel!

Looking forward to you're entry.