VBScripting syntax for Autoformat

[Weezy1957]

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

 

[Jim Rech]

Just do this:

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


--
Jim
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
|
|
| --
| Weezy1957
| ------------------------------------------------------------------------
| Weezy1957's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=24498
| View this thread: http://www.excelforum.com/showthread.php?threadid=380924
|

 

[Weezy1957]

Hi Jim:

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

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

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

Any other ideas?

Weezy

 

[Jim Rech]

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
in message |
| Hi Jim:
|
| Thanks for the answer, but it still won't work.
|
| I have also tried:
| oSSBExcel.Range("A65536").End(xlup).AutoFormat(11)
| and
| oSSBExcel.Range("A65536").End(xlup).AutoFormat(Format:=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?action=getinfo&userid=24498
| View this thread: http://www.excelforum.com/showthread.php?threadid=380924
|

 

[Weezy1957]

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