Word VBA Automation to Excel

[RJ]

I have successfully opened a new Excel Workbook from Word
VBA, added a Worksheet - and can write info to cells.
But i cant seem to get to the point of making the
Selection process work. Here is my code:

'Open a NEW workbook
oXL.Visible = True
oXL.Workbooks.Add
With oXL
Cells.Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
etc...

but at the Selection part (as copied from Excel) it does
not work.
What i am wanting to do is to format the entire sheet
right aligned and then change 2 of the columns to left
align.

Appreciate any assistance...
Thanks

 

[Jim Cone]

RJ,

You need to specify the worksheet that you are changing.

You could add two more variables...
'-----------------------------------
Dim oWB as Excel.Workbook
Dim oWS as Excel.Worksheet

Set oWB = oXL.Workbooks.Add
Set oWS = oWB.Worksheets(1)

oWS.Cells.HorizontalAlignment = xlRight
oWS.Columns("B:C").HorizontalAlignment = xlLeft
'--more stuff

Set oWS = Nothing
oWB.Close SaveChanges:=True
Set oWB = Nothing
oXL.Quit
Set oXL = Nothing
'-----------------------------------

Regards,
Jim Cone
San Francisco, CA

 

[RJ]

Progress made, but now i can not write to the cells!

Here is what i have - the last two line are meant to go
to the Cell and write in "LN"


oXL.Visible = True
Set oWB = oXL.Workbooks.Add
Set oWS = oWB.Worksheets(1)

With oWS
.Cells.HorizontalAlignment = xlRight
.Columns("B").HorizontalAlignment = xlLeft
.Rows(1).Font.Bold = True

.Range("A1").Select
ActiveCell.FormulaR1C1 = "LN"

etc....

Thanks

-----Original Message-----
RJ,

You need to specify the worksheet that you are changing.

You could add two more variables...
'-----------------------------------
Dim oWB as Excel.Workbook
Dim oWS as Excel.Worksheet

Set oWB = oXL.Workbooks.Add
Set oWS = oWB.Worksheets(1)

oWS.Cells.HorizontalAlignment = xlRight
oWS.Columns("B:C").HorizontalAlignment = xlLeft
'--more stuff

Set oWS = Nothing
oWB.Close SaveChanges:=True
Set oWB = Nothing
oXL.Quit
Set oXL = Nothing
'-----------------------------------

Regards,
Jim Cone
San Francisco, CA

"RJ" <[email protected]> wrote in

message news:[email protected]...

 

[Jim Cone]

RJ,

It is best to avoid the use of "With" when using automation.
It only works most of the time.
It is also better to avoid use of any statement with "Active" in it.
Instead (always) use your object references...

oWS.Cells.HorizontalAlignment = xlRight
oWS.Columns("B").HorizontalAlignment = xlLeft
oWS.Rows(1).Font.Bold = True
oWS.Range("A1").Value = "LN"
'or
' oWS.Range("A1").Formula = "=""LN""

Regards,
Jim Cone
San Francisco, CA

Progress made, but now i can not write to the cells!
Here is what i have - the last two line are meant to go
to the Cell and write in "LN"
oXL.Visible = True
Set oWB = oXL.Workbooks.Add
Set oWS = oWB.Worksheets(1)
With oWS
.Cells.HorizontalAlignment = xlRight
.Columns("B").HorizontalAlignment = xlLeft
.Rows(1).Font.Bold = True

.Range("A1").Select
ActiveCell.FormulaR1C1 = "LN"
etc....
Thanks

-snip-