VBScripting syntax for Autoformat
 

I am trying to use AutoFormat in VBScript through SQL Server in a DTS
package. Most everything is going great but I can't figure out the
syntax for this line:

oSSBExcel.Range("A65536").End(xlup).AutoFormat Format:=11

This is supposed to grab the last used row and apply an autoformat. It
works great when run in Excel, but I think it chokes on Format:=11.

Is there a way to say this in VBScript?

Thanks much.

Weezy :confused:


--
Weezy1957
------------------------------------------------------------------------
Weezy1957's Profile: http://www.excelforum.com/member.php...o&userid=24498
View this thread: http://www.excelforum.com/showthread...hreadid=380924

VBScripting syntax for Autoformat
 

Just do this:

oSSBExcel.Range("A65536").End(xlup).AutoFormat 11


--
Jim
"Weezy1957" wrote
in message ...
|
| I am trying to use AutoFormat in VBScript through SQL Server in a DTS
| package. Most everything is going great but I can't figure out the
| syntax for this line:
|
| oSSBExcel.Range("A65536").End(xlup).AutoFormat Format:=11
|
| This is supposed to grab the last used row and apply an autoformat. It
| works great when run in Excel, but I think it chokes on Format:=11.
|
| Is there a way to say this in VBScript?
|
| Thanks much.
|
| Weezy :confused:
|
|
| --
| Weezy1957
| ------------------------------------------------------------------------
| Weezy1957's Profile:
http://www.excelforum.com/member.php...o&userid=24498
| View this thread: http://www.excelforum.com/showthread...hreadid=380924
|

VBScripting syntax for Autoformat
 

Hi Jim:

Thanks for the answer, but it still won't work. :mad:

I have also tried:
oSSBExcel.Range("A65536").End(xlup).AutoFormat(11)
and
oSSBExcel.Range("A65536").End(xlup).AutoFormat(For mat:=11)

I get "unknown run time error" on that line, so I know that is the
offending code.

Any other ideas?

Weezy


--
Weezy1957
------------------------------------------------------------------------
Weezy1957's Profile: http://www.excelforum.com/member.php...o&userid=24498
View this thread: http://www.excelforum.com/showthread...hreadid=380924

VBScripting syntax for Autoformat
 

FYI, you cannot use _any_ named constants in a script. Try this:

Dim XL
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Add
XL.Range("A1:C10").Value = 1000
XL.Range("A65536").End(-4162).AutoFormat 11
XL.Visible = True

--
Jim
"Weezy1957" wrote
in message ...
|
| Hi Jim:
|
| Thanks for the answer, but it still won't work. :mad:
|
| I have also tried:
| oSSBExcel.Range("A65536").End(xlup).AutoFormat(11)
| and
| oSSBExcel.Range("A65536").End(xlup).AutoFormat(For mat:=11)
|
| I get "unknown run time error" on that line, so I know that is the
| offending code.
|
| Any other ideas?
|
| Weezy
|
|
| --
| Weezy1957
| ------------------------------------------------------------------------
| Weezy1957's Profile:
http://www.excelforum.com/member.php...o&userid=24498
| View this thread: http://www.excelforum.com/showthread...hreadid=380924
|

VBScripting syntax for Autoformat
 

Jim:

Thanks much. It works. I did not realize xlup was a named constant (
I am pretty Excel ignorant ).

I just replaced it with your -4162 and it works like a charm.

Weezy :)


--
Weezy1957
------------------------------------------------------------------------
Weezy1957's Profile: http://www.excelforum.com/member.php...o&userid=24498
View this thread: http://www.excelforum.com/showthread...hreadid=380924