VBScript syntax for formatting cells in excel.

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?
MattMcCantsAsked:
Who is Participating?
 
aeklundConnect With a Mentor Commented:
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
 
WintersVineCommented:
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
 
aeklundCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
WintersVineCommented:
of course, hince my opening question.
0
 
aeklundCommented:
gotcha, I was a bit confused, thanks for clarifying for me.
0
 
MattMcCantsAuthor Commented:
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
 
MattMcCantsAuthor Commented:
Worked perfectly, Thank you so much for the quick answer.
0
 
aeklundCommented:
Glad to help... thx for the points.
0
All Courses

From novice to tech pro — start learning today.