Selecting Range and then Setting the Borders collection
Author |
Message |
Michael Harri #1 / 8
|
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 |
|
|
Michael Harri #2 / 8
|
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 |
|
|
Shaw #3 / 8
|
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 |
|
|
Michael Harri #4 / 8
|
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 |
|
|
Michael Harri #5 / 8
|
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 |
|
|
Shaw #6 / 8
|
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 |
|
|
Shaw #7 / 8
|
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 |
|
|
Michael Harri #8 / 8
|
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 |
|
|
|