excel automation

[bstice]

I am trying to do three things with automation from access. I have been able to change column width, font size etc, but I haven't been able to figure out how to add a border to a group of cells, change the backgroud color, and change the alighnment to center. Everything works on getting the excel doc open and formatted with the simple stuff. He is a small sample of my code.

Dim xlapp As Object, xlwb As Object, xlws As Object
Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add
Set xlws = xlwb.worksheets("Sheet1")

With xlws
.range("C35:G35").numberformat = "0.0%"
end with

How do I change the alignment of range("A1:a3") to center? How can I change the backcolor for range("b2:b7") to white? Thanks!

Brennan

 

[Brianwarnock]

HorizontalAlignment = xlCenter

The easy way to check out the EXcel commands is to go into Excel and record a macro doing the formatting you require.

Brian

 

[bstice]

Thanks for the quick reply. I have already tried using the macro in excel to build the VBA, but it errors out. I tried the following code, but it didn't work. If you have a moment could you provide some more advise. Thanks

wtih xlws
.range("k3:k5").HorizontalAlignment = xlCenter
end with

 

[Brianwarnock]

I'd better come clean, i've always done formatting in Excel, when I saw your numberformat I assumed that the commands were the same when doing it from ACCESS, but it appears not.

Sorry for the red herring.

Brian

 

[georgedwilkinson]

Sorry to stick my nose in. Just noticed you might have reached an impasse.

What happened when you tried:

wtih xlws
.range("k3:k5").HorizontalAlignment = xlCenter
end with

You realize that "with" is spelled wrong in this sample, right?

So, did it give you a syntax error? Just run with no error but didn't do what you wanted? Mushroom cloud? What? Was there any code high-lighted in yellow? If so, what code? So many questions and so little real information.

I have used this technique many times from Access to pretty up an Access generated xls so I know it can be done...and you're so close!

 

[Brianwarnock]

Ok had a bash at a spreadsheet I was exporting without formatting and this worked, obviously this is a bit of a clumsy makeshify test, but one thing I did discover, and that was i had to add the Excel object library to my references.

Edit just to give you a laugh I also had to remember how not to spell centre

Brian

DoCmd.OutputTo acOutputQuery, "qryfindname", acFormatXLS, "qryfindname.xls", True
Set appExcel = Excel.Application

appExcel.Visible = True
range("e2").select
With selection
.horizontalalignment = xlcenter
End With

 

[Brianwarnock]

Sorry to stick my nose in. Just noticed you might have reached an impasse.

!

Happy for any help from anyone, its not a thing I've everdone, but it was a challenge.

Brian

 

[Brianwarnock]

added this now after the end with, I thought it would be like Excel
Brian

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

 

[bstice]

I apologize for the spelling. I actually did spell "with" correctly in the code, but I just typed "with" because it was easier. Thanks for the advice on the on the excel object reference. I added that and your code worked just fine. Thank you!

 

[georgedwilkinson]

Glad you got it running.

 

[Brianwarnock]

Thanks for the advice on the on the excel object reference. I added that and your code worked just fine. Thank you!

A case of 2 heads better than 1 as we both learnt something.

Brian