Selecting Range and then Setting the Borders collection 
Author Message
 Selecting Range and then Setting the Borders collection

Make sure you're running at least version 5.0 of VBScript.  The With
statement didn't appear until v5.0.

--
Michael Harris


All I want to do is select a range of cells, and use the Borders collection
in Excel97 with VBScript 5.  I want to manipulate the linestyle and weight.
From the VBA Help files the code copied exactly doesn't work:

objXL is Set objXL = WScript.CreateObject("Excel.Application") for all of
these

(I am on the active sheet)
With Worksheets("Sheet1").Range("B2").Borders(xlBottom)
 .LineStyle = xlBorderLineStyleContinuous
 .Weight = xlThin
 .ColorIndex = 3
End With

This blows with an Expected Statement Error on the "With" Line??

What I reallt want to do is this:

With Worksheets("Sheet1").Range("A1:C1").Borders(xlBottom)
 .LineStyle = xlBorderLineStyleContinuous
 .Weight = xlThick
 .ColorIndex = 3
End With

Same Error message

So I try this:

objXL.Range("A1:C1").Select
objXL.Selection.Borders(xlBottom).LineStyle = xlBorderLineStyleContinuous
objXL.Selection.Borders(xlBottom).weight = xlThick

This blows with an Unknown Runtime Error:

Then I tried this:

objXL.selection.border(xlEdgeBottom).weight = xlThick

This blows with Object doesn't support this property or method:
'selection.border'

I am having trouble coming up with any more variations to this
solution....HELP!.....Please :)

TIA
Shawn



Wed, 17 Oct 2001 03:00:00 GMT  
 Selecting Range and then Setting the Borders collection

Have you tried this?

With objXL.Worksheets("Sheet1").Range("B2").Borders(xlBottom)
...

I'm also assuming that you've defined some local constants for xlBottom, etc.  None of the CreateObject methods add the enumerated constants (defined in the type library of the object you create) to the script's namespace.  With VBA, you're working "from the inside" and the application's root objects, methods, constants, etc. are automatically included in the script's namespace.  When you're working "from the outside" via WSH scripting, this automatic inclusion in the namespace doesn't occur.

--
Michael Harris

All I want to do is select a range of cells, and use the Borders collection
in Excel97 with VBScript 5.  I want to manipulate the linestyle and weight.
From the VBA Help files the code copied exactly doesn't work:

objXL is Set objXL = WScript.CreateObject("Excel.Application") for all of
these

(I am on the active sheet)
With Worksheets("Sheet1").Range("B2").Borders(xlBottom)
 .LineStyle = xlBorderLineStyleContinuous
 .Weight = xlThin
 .ColorIndex = 3
End With

This blows with an Expected Statement Error on the "With" Line??

What I reallt want to do is this:

With Worksheets("Sheet1").Range("A1:C1").Borders(xlBottom)
 .LineStyle = xlBorderLineStyleContinuous
 .Weight = xlThick
 .ColorIndex = 3
End With

Same Error message

So I try this:

objXL.Range("A1:C1").Select
objXL.Selection.Borders(xlBottom).LineStyle = xlBorderLineStyleContinuous
objXL.Selection.Borders(xlBottom).weight = xlThick

This blows with an Unknown Runtime Error:

Then I tried this:

objXL.selection.border(xlEdgeBottom).weight = xlThick

This blows with Object doesn't support this property or method:
'selection.border'

I am having trouble coming up with any more variations to this
solution....HELP!.....Please :)

TIA
Shawn



Wed, 17 Oct 2001 03:00:00 GMT  
 Selecting Range and then Setting the Borders collection

hmmmm.......

You have a point, I am not defining Local Constants for XLBottom..etc...  :)

ok, I am quite fresh to WSH, VBA, VBS. I am used to VB 5 & 6. I have never had the need to define my own local constants before. Well let's say I never knew I had the need :)  anyway, I've looked at the object Browser in VB6 (I am referencing the Excel 8.0 Object Library, Looked all through VBA Help, etc....I am not finding anything that slaps me across the face to help me figure what I need to define and the syntax to do it.

Can you give me an example for xlBottom? or tell me enough to go figure it out?

Thanks for the idea! I imagine this has been a problem I've had in some other ventures with VBS.

Shawn

PS.  it is VBScript 5


    Have you tried this?

    With objXL.Worksheets("Sheet1").Range("B2").Borders(xlBottom)
    ...

    I'm also assuming that you've defined some local constants for xlBottom, etc.  None of the CreateObject methods add the enumerated constants (defined in the type library of the object you create) to the script's namespace.  With VBA, you're working "from the inside" and the application's root objects, methods, constants, etc. are automatically included in the script's namespace.  When you're working "from the outside" via WSH scripting, this automatic inclusion in the namespace doesn't occur.

    --
    Michael Harris


    All I want to do is select a range of cells, and use the Borders collection
    in Excel97 with VBScript 5.  I want to manipulate the linestyle and weight.
    From the VBA Help files the code copied exactly doesn't work:

    objXL is Set objXL = WScript.CreateObject("Excel.Application") for all of
    these

    (I am on the active sheet)
    With Worksheets("Sheet1").Range("B2").Borders(xlBottom)
     .LineStyle = xlBorderLineStyleContinuous
     .Weight = xlThin
     .ColorIndex = 3
    End With

    This blows with an Expected Statement Error on the "With" Line??

    What I reallt want to do is this:

    With Worksheets("Sheet1").Range("A1:C1").Borders(xlBottom)
     .LineStyle = xlBorderLineStyleContinuous
     .Weight = xlThick
     .ColorIndex = 3
    End With

    Same Error message

    So I try this:

    objXL.Range("A1:C1").Select
    objXL.Selection.Borders(xlBottom).LineStyle = xlBorderLineStyleContinuous
    objXL.Selection.Borders(xlBottom).weight = xlThick

    This blows with an Unknown Runtime Error:

    Then I tried this:

    objXL.selection.border(xlEdgeBottom).weight = xlThick

    This blows with Object doesn't support this property or method:
    'selection.border'

    I am having trouble coming up with any more variations to this
    solution....HELP!.....Please :)

    TIA
    Shawn



Wed, 17 Oct 2001 03:00:00 GMT  
 Selecting Range and then Setting the Borders collection

When you're working in VB5/6, you would typically add a a reference to the object's type library via the Project References dialog.  When you do this, the VB IDE adds the enumerated constants, etc. to the project's namespace.  In WSH2, there's a new .ws file format (XML tag based, similar in some ways to the current .wsc file format used to define script components) that will support a <reference> tag that works something like the VB IDE Project References).   There have been a couple of posts from Eric Lippert (MS Scripting Dev group) this past month (April) indicating that the WSH2 beta will be available sometime in the near future.

--
Michael Harris

hmmmm.......

You have a point, I am not defining Local Constants for XLBottom..etc...  :)

ok, I am quite fresh to WSH, VBA, VBS. I am used to VB 5 & 6. I have never had the need to define my own local constants before. Well let's say I never knew I had the need :)  anyway, I've looked at the object Browser in VB6 (I am referencing the Excel 8.0 Object Library, Looked all through VBA Help, etc....I am not finding anything that slaps me across the face to help me figure what I need to define and the syntax to do it.

Can you give me an example for xlBottom? or tell me enough to go figure it out?

Thanks for the idea! I imagine this has been a problem I've had in some other ventures with VBS.

Shawn

PS.  it is VBScript 5


  Have you tried this?

  With objXL.Worksheets("Sheet1").Range("B2").Borders(xlBottom)
  ...

  I'm also assuming that you've defined some local constants for xlBottom, etc.  None of the CreateObject methods add the enumerated constants (defined in the type library of the object you create) to the script's namespace.  With VBA, you're working "from the inside" and the application's root objects, methods, constants, etc. are automatically included in the script's namespace.  When you're working "from the outside" via WSH scripting, this automatic inclusion in the namespace doesn't occur.

  --
  Michael Harris


  All I want to do is select a range of cells, and use the Borders collection
  in Excel97 with VBScript 5.  I want to manipulate the linestyle and weight.
  From the VBA Help files the code copied exactly doesn't work:

  objXL is Set objXL = WScript.CreateObject("Excel.Application") for all of
  these

  (I am on the active sheet)
  With Worksheets("Sheet1").Range("B2").Borders(xlBottom)
   .LineStyle = xlBorderLineStyleContinuous
   .Weight = xlThin
   .ColorIndex = 3
  End With

  This blows with an Expected Statement Error on the "With" Line??

  What I reallt want to do is this:

  With Worksheets("Sheet1").Range("A1:C1").Borders(xlBottom)
   .LineStyle = xlBorderLineStyleContinuous
   .Weight = xlThick
   .ColorIndex = 3
  End With

  Same Error message

  So I try this:

  objXL.Range("A1:C1").Select
  objXL.Selection.Borders(xlBottom).LineStyle = xlBorderLineStyleContinuous
  objXL.Selection.Borders(xlBottom).weight = xlThick

  This blows with an Unknown Runtime Error:

  Then I tried this:

  objXL.selection.border(xlEdgeBottom).weight = xlThick

  This blows with Object doesn't support this property or method:
  'selection.border'

  I am having trouble coming up with any more variations to this
  solution....HELP!.....Please :)

  TIA
  Shawn



Wed, 17 Oct 2001 03:00:00 GMT  
 Selecting Range and then Setting the Borders collection

In Excel's VBA editor, go to the Object browser and select Excel in the Project/Library drop down.  Select <globals> in the Classes pane and in the Members pane, right click and select Group Members (this will put properties together first, then methods, and then constants).  Scroll down until you find the constant you're after (e.g., xlBottom).  Click on it to select it.  In the description pane at the bottom you'll see:

Const xlBottom = -4107 (&HFFFFEFF5)
    Member of Excel.Constants

The description pane is actually a read-only text box.  You can highlight text and copy it to the clipboard.  (Tip: The fastest way to select an entire line is to triple click within the line.)

The VB5/6 IDE Object Browsers work the same way if you add a project reference to the Excel 8.0 Object Library.  If you have a text editor that can "monitor the clipboard", such as EditPlus ($20 at www.editplus.com) or NoteTab Light (free at www.notetab.com), or some other utility that lets you accumulate clipboard text, you can copy several in one visit and paste them all at once.  Better yet, if you use an editor that supports text templates (the two mentioned do), create a template of constants that you frequently use.

--
Michael Harris

Have you tried this?

With objXL.Worksheets("Sheet1").Range("B2").Borders(xlBottom)
...

I'm also assuming that you've defined some local constants for xlBottom, etc.  None of the CreateObject methods add the enumerated constants (defined in the type library of the object you create) to the script's namespace.  With VBA, you're working "from the inside" and the application's root objects, methods, constants, etc. are automatically included in the script's namespace.  When you're working "from the outside" via WSH scripting, this automatic inclusion in the namespace doesn't occur.

--
Michael Harris

All I want to do is select a range of cells, and use the Borders collection
in Excel97 with VBScript 5.  I want to manipulate the linestyle and weight.
From the VBA Help files the code copied exactly doesn't work:

objXL is Set objXL = WScript.CreateObject("Excel.Application") for all of
these

(I am on the active sheet)
With Worksheets("Sheet1").Range("B2").Borders(xlBottom)
 .LineStyle = xlBorderLineStyleContinuous
 .Weight = xlThin
 .ColorIndex = 3
End With

This blows with an Expected Statement Error on the "With" Line??

What I reallt want to do is this:

With Worksheets("Sheet1").Range("A1:C1").Borders(xlBottom)
 .LineStyle = xlBorderLineStyleContinuous
 .Weight = xlThick
 .ColorIndex = 3
End With

Same Error message

So I try this:

objXL.Range("A1:C1").Select
objXL.Selection.Borders(xlBottom).LineStyle = xlBorderLineStyleContinuous
objXL.Selection.Borders(xlBottom).weight = xlThick

This blows with an Unknown Runtime Error:

Then I tried this:

objXL.selection.border(xlEdgeBottom).weight = xlThick

This blows with Object doesn't support this property or method:
'selection.border'

I am having trouble coming up with any more variations to this
solution....HELP!.....Please :)

TIA
Shawn



Wed, 17 Oct 2001 03:00:00 GMT  
 Selecting Range and then Setting the Borders collection
Great info, thanks Michael!

I have just talked Management into deploying this to all 4500+ desktops,
after seeing what could be done with the existing versions. It is a great
alternative to batching, some things you just can't do. Now to think I need
to talk them into the next upgrade for more abilities :)........

Without drawing this out too much,

How can one tell what WSH can do with Office Objects and other cool objects
were not used to grabbing with a script, aside from what is documented at
Microsoft (which ain't much)? I hate to think I have to watch the samples on
the net to be able to tell what can be gotten away with :(

It amazes me that I can use some pieces of the object but not others. Is
there a couple (or more) hints so I can tell what I probably -won't- be able
to do? So I don't get to far down a certain path :)

Shawn


    When you're working in VB5/6, you would typically add a a reference to
the object's type library via the Project References dialog.  When you do
this, the VB IDE adds the enumerated constants, etc. to the project's
namespace.  In WSH2, there's a new .ws file format (XML tag based, similar
in some ways to the current .wsc file format used to define script
components) that will support a <reference> tag that works something like
the VB IDE Project References).   There have been a couple of posts from
Eric Lippert (MS Scripting Dev group) this past month (April) indicating
that the WSH2 beta will be available sometime in the near future.

    --
    Michael Harris


    hmmmm.......

    You have a point, I am not defining Local Constants for XLBottom..etc...
:)

    ok, I am quite fresh to WSH, VBA, VBS. I am used to VB 5 & 6. I have
never had the need to define my own local constants before. Well let's say I
never knew I had the need :)  anyway, I've looked at the object Browser in
VB6 (I am referencing the Excel 8.0 Object Library, Looked all through VBA
Help, etc....I am not finding anything that slaps me across the face to help
me figure what I need to define and the syntax to do it.

    <snip>



Wed, 17 Oct 2001 03:00:00 GMT  
 Selecting Range and then Setting the Borders collection
Whoo-Hoo! It works... some stuff I still can't find but I'll deal with it.
This gets me started....thanks again!! btw I didn't see this till I posted
to your last :)

Thanks
Shawn


    In Excel's VBA editor, go to the Object browser and select Excel in the
Project/Library drop down.  Select <globals> in the Classes pane and in the
Members pane, right click and select Group Members (this will put properties
together first, then methods, and then constants).  Scroll down until you
find the constant you're after (e.g., xlBottom).  Click on it to select it.
In the description pane at the bottom you'll see:

    Const xlBottom = -4107 (&HFFFFEFF5)
        Member of Excel.Constants

    The description pane is actually a read-only text box.  You can
highlight text and copy it to the clipboard.  (Tip: The fastest way to
select an entire line is to triple click within the line.)

    The VB5/6 IDE Object Browsers work the same way if you add a project
reference to the Excel 8.0 Object Library.  If you have a text editor that
can "monitor the clipboard", such as EditPlus ($20 at www.editplus.com) or
NoteTab Light (free at www.notetab.com), or some other utility that lets you
accumulate clipboard text, you can copy several in one visit and paste them
all at once.  Better yet, if you use an editor that supports text templates
(the two mentioned do), create a template of constants that you frequently
use.

    --
    Michael Harris



    Have you tried this?

    With objXL.Worksheets("Sheet1").Range("B2").Borders(xlBottom)
    ...

    I'm also assuming that you've defined some local constants for xlBottom,
etc.  None of the CreateObject methods add the enumerated constants (defined
in the type library of the object you create) to the script's namespace.
With VBA, you're working "from the inside" and the application's root
objects, methods, constants, etc. are automatically included in the script's
namespace.  When you're working "from the outside" via WSH scripting, this
automatic inclusion in the namespace doesn't occur.

    --
    Michael Harris



    All I want to do is select a range of cells, and use the Borders
collection
    in Excel97 with VBScript 5.  I want to manipulate the linestyle and
weight.
    From the VBA Help files the code copied exactly doesn't work:

    objXL is Set objXL = WScript.CreateObject("Excel.Application") for all
of
    these

    (I am on the active sheet)
    With Worksheets("Sheet1").Range("B2").Borders(xlBottom)
     .LineStyle = xlBorderLineStyleContinuous
     .Weight = xlThin
     .ColorIndex = 3
    End With

    This blows with an Expected Statement Error on the "With" Line??

    What I reallt want to do is this:

    With Worksheets("Sheet1").Range("A1:C1").Borders(xlBottom)
     .LineStyle = xlBorderLineStyleContinuous
     .Weight = xlThick
     .ColorIndex = 3
    End With

    Same Error message

    So I try this:

    objXL.Range("A1:C1").Select
    objXL.Selection.Borders(xlBottom).LineStyle =
xlBorderLineStyleContinuous
    objXL.Selection.Borders(xlBottom).weight = xlThick

    This blows with an Unknown Runtime Error:

    Then I tried this:

    objXL.selection.border(xlEdgeBottom).weight = xlThick

    This blows with Object doesn't support this property or method:
    'selection.border'

    I am having trouble coming up with any more variations to this
    solution....HELP!.....Please :)

    TIA
    Shawn



Wed, 17 Oct 2001 03:00:00 GMT  
 Selecting Range and then Setting the Borders collection

As for Office objects, you can essentially do anything with them using WSH and script that you can do with VBA.  Any application that exposes an ActiveX Automation interface (COM IDispatch) can (within limits) be used from any language environment that support ActiveX objects.  The only limits I've encountered have to do with strongly typed method arguments that must be passed by reference (the method needs to modifiy the argument to return data).  Since script languages only support Variants, you're out of luck in this case.  

What you need is a good book on WSH.  Unfortunately there aren't any on the market yet.  I do know that there are at least two are in the works (in know personally of one from Wrox Press authored by Dino Esposito).  For the time being, your best bet is the WSH newsgroup and WSH oriented web sites like

  a.. Ian Morrish's at http://wsh.glazier.co.nz
  b.. Clarence Washington's at http://cwashington.netreach.net
  c.. Steven Bondi's at http://homepages.go.com/~sbondi/powerscripting/
  d.. Gunter Born's at http://ourworld.compuserve.com/homepages/Guenter_Born/index0.htm
  e.. Daren's at http://www.winscripter.com/ as well as a number of others.

As for what's documented at Microsoft, it's all there, you just have to search for it sometimes.  The new organization of the merged MSDN and SBN sites is _much_ easier to use and novigate (IMHO).  I especially like the MSDN site search page that let's you be very specific about what part of this enormous site to search.

--
Michael Harris

Great info, thanks Michael!

I have just talked Management into deploying this to all 4500+ desktops,
after seeing what could be done with the existing versions. It is a great
alternative to batching, some things you just can't do. Now to think I need
to talk them into the next upgrade for more abilities :)........

Without drawing this out too much,

How can one tell what WSH can do with Office Objects and other cool objects
were not used to grabbing with a script, aside from what is documented at
Microsoft (which ain't much)? I hate to think I have to watch the samples on
the net to be able to tell what can be gotten away with :(

It amazes me that I can use some pieces of the object but not others. Is
there a couple (or more) hints so I can tell what I probably -won't- be able
to do? So I don't get to far down a certain path :)

Shawn


    When you're working in VB5/6, you would typically add a a reference to
the object's type library via the Project References dialog.  When you do
this, the VB IDE adds the enumerated constants, etc. to the project's
namespace.  In WSH2, there's a new .ws file format (XML tag based, similar
in some ways to the current .wsc file format used to define script
components) that will support a <reference> tag that works something like
the VB IDE Project References).   There have been a couple of posts from
Eric Lippert (MS Scripting Dev group) this past month (April) indicating
that the WSH2 beta will be available sometime in the near future.

    --
    Michael Harris


    hmmmm.......

    You have a point, I am not defining Local Constants for XLBottom..etc...
:)

    ok, I am quite fresh to WSH, VBA, VBS. I am used to VB 5 & 6. I have
never had the need to define my own local constants before. Well let's say I
never knew I had the need :)  anyway, I've looked at the object Browser in
VB6 (I am referencing the Excel 8.0 Object Library, Looked all through VBA
Help, etc....I am not finding anything that slaps me across the face to help
me figure what I need to define and the syntax to do it.

    <snip>



Wed, 17 Oct 2001 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Need to Place Border in Cell or Selected Range of Excel

2. JScript equivalent to a ranged set, and setting cookies through client-side script

3. selecting page ranges from a postscript file

4. Select Case range

5. read Image Attributes over selected Range

6. selecting a range of posts?

7. how to set border width dynamically?

8. Set border

9. Set left border at source PRINTOUT

10. Programatically setting the SELECTED OPTION of a SELECT

11. Setting the selected option of a select box in code

12. set range propeties in excel

 

 
Powered by phpBB® Forum Software