Access to Excel Coding HELP =(

[Guest]

Hey,
I'm formatting excel via Access.
Most of the code (from klatuu) works.

Some stuff returns error messages though.

For instances,
xlSheet.Range("C3:E10").Borders.LineStyle = xlContinuous
works

while,
xlSheet.Range("C3:E10").Borders.Weight = Thin
returns
"Error #1004: Unable to set the Weight property of the Borders class"


..Columns(1).HorizontalAlignment = xlCenter
returns
"Error #1004: Unable to set the Horizontal Alignment property of the Range
class"


Any Help/Advice would be really Really Appreciated.
Thanks~

 

[Alex Dybenko]

Hi,
run macro recorder in excel, do necessary formatting manually, then stop
macro recorder and see what code it generate, use the same in your access
proc, just add xlSheet. where necessary

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

HI Kaseano,

I am just curious. Why do you do it in Access, stead of Excel?

Thanks.

 

[Guest]

because there were a bunch of pre existing tables + queries I needed in
Access, Plus the people i'm writing the program for are comfortable with
access. Just sorta automating their process of transferring the data from
access to excel, then making it look "pretty".

 

[Guest]

I ran the macro recorder in excel and it produced the same results from the
original post. =(

Hopefully someone has an aswer to this.

 

[Guest]

hi Kaseano,

try this
xlSheet.Range("C3:E10").Borders.Weight = xlThin

ExcelSheet.Application.Columns(1).Select
With ExcelSheet.Application.Selection
.HorizontalAlignment = xlCenter
End With

HTH Paolo

 

[Guest]

"unable to set the Weight property of the Border class"

"unable to set the HorizontalAlignment property of the Range class" =(

this sucks.

 

[Alex Dybenko]

Well, then you do something wrong in access, if code works in excel - then
it should work in access also, perhaps you did not declared all constants,
xlCenter for example (if you have late bindings)

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Keith Wilby]

"unable to set the Weight property of the Border class"

"unable to set the HorizontalAlignment property of the Range class" =(

this sucks.

This works for me.

Dim objSht As Excel.Worksheet
Dim objRange As Range

Set objRange = objSht.Range("A1").CurrentRegion

With objRange
.Select
.Borders.Weight = xlThin
End With

Set objRange = Nothing
Set objSht = Nothing

HTH - Keith.
www.keithwilby.com

 

[Guest]

bah turns out I didn't have excel in the Access VB references.
Sorry for being dumb, thanks for all the responses everyone.