?
Solved

VBScript syntax for formatting cells in excel.

Posted on 2003-03-31
8
Medium Priority
?
991 Views
Last Modified: 2012-06-21
I've been searching the web for the Syntax to create a simple border around a range of cells in excel for who knows how long. Nothing I have tried works. The syntax used in VBA does not work in VBS. Anyone have any suggestions?
0
Comment
Question by:MattMcCants
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 10

Accepted Solution

by:
aeklund earned 1000 total points
ID: 8241834
Here is some code to put a single line border using pure vbscript:

  Dim oxl 'As Excel.Application
  Set oxl = CreateObject("Excel.Application")
  oxl.Workbooks.Add
 
  Const xlDiagonalDown = 5
  Const xlDiagonalUp = 6
  Const xlEdgeLeft = 7
  Const xlEdgeTop = 8
  Const xlEdgeBottom = 9
  Const xlEdgeRight = 10
  Const xlInsideVertical = 11
  Const xlInsideHorizontal = 12
 
  Const xlContinuous = 1
  Const xlNone = -4142
  Const xlThin = 2
  Const xlAutomatic = -4105
 
  oxl.ActiveSheet.Range("D7:J23").Select
  oxl.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  oxl.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  With oxl.Selection.Borders(xlEdgeLeft)
      .LineStyle = xlContinuous
      .Weight = xlThin
      .ColorIndex = xlAutomatic
  End With
  With oxl.Selection.Borders(xlEdgeTop)
      .LineStyle = xlContinuous
      .Weight = xlThin
      .ColorIndex = xlAutomatic
  End With
  With oxl.Selection.Borders(xlEdgeBottom)
      .LineStyle = xlContinuous
      .Weight = xlThin
      .ColorIndex = xlAutomatic
  End With
  With oxl.Selection.Borders(xlEdgeRight)
      .LineStyle = xlContinuous
      .Weight = xlThin
      .ColorIndex = xlAutomatic
  End With
  oxl.Selection.Borders(xlInsideVertical).LineStyle = xlNone
  oxl.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  oxl.Visible = True
0
 
LVL 2

Expert Comment

by:WintersVine
ID: 8242698
are you running VBscript from Excel?

(note: if you are unsure of Excel's sytax for its internal commands, try recording a Macro, do what you want to do, stop recording the macro, and then edit the macro to see what code excel produced.)

if you are you want the code to look like this:
Range("A1:E9").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("E9").Select
End Sub
0
 
LVL 10

Expert Comment

by:aeklund
ID: 8246323
WintersVine-
I think Matt wants pure vbscript, and not VBA.  When running the macro recorder in Excel, it saves the code as VBA.  In order to make it work for vbscript, (a .vbs file) it needs to be massaged a little.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:WintersVine
ID: 8249770
of course, hince my opening question.
0
 
LVL 10

Expert Comment

by:aeklund
ID: 8254254
gotcha, I was a bit confused, thanks for clarifying for me.
0
 

Author Comment

by:MattMcCants
ID: 8254358
Thanks, Im busy right this second, but I will look into these solutions. And yes it does need to be pure VBScript. I tried to record the macro and I got the VBA code for it, but a direct copy and paste would not work. I also tried so many other ways. The rest of my script works, and this is the finishing touch. Thank you for your help, I will be back shortly with the results.
0
 

Author Comment

by:MattMcCants
ID: 8254743
Worked perfectly, Thank you so much for the quick answer.
0
 
LVL 10

Expert Comment

by:aeklund
ID: 8255032
Glad to help... thx for the points.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question