TableStyle with VBScript

Question

Is there any way to Style a table with VBScript? All the solutions I'm finding online are for VBA.

for example, I tried the solution here Excel Macro - Select all cells with data and format as table with the following code

Set objExcel = CreateObject("Excel.Application")
Dim tbl
Set tbl = objWorkbook.ListObjects.Add(xlSrcRange, objWorkbook.Sheets("101").Range("$A$1:$C$26"), , xlYes)
tbl.TableStyle = "TableStyleLight1"

but I get this error

Microsoft VBScript runtime error: Object doesn't support this property or method: 'objWorkbook.ListObjects'

(If you have a solution for this in exceljs that would be even better)

Where objWorkbook getting set? You can;t add list object to a workbook, you need to add them to a worksheet — dgorti, Jan 06 '17 at 23:45
you need `Set objWorkbook = objExcel.Workbooks.Add()` after the first line — Slai, Jan 08 '17 at 23:56
@dgorti objWorkbook is set to objExcel.Workbooks.Open("\report.xlsx") — tumelo, Jan 09 '17 at 12:53
@dgorti when I add it to a sheet instead I get this error ```Microsoft VBScript runtime error: Invalid procedure call or argument``` — tumelo, Jan 09 '17 at 13:25

score 0 · Answer 1 · answered Jan 07 '17 at 16:32

0

I found something you may be able to use. It is from Using Styles to Dress Up Your Worksheets in Excel 2007. What I did was converted it from VBA to VBScript, which really wasn't that hard.

Sub ListStyles()
    Dim objStyle
    Dim objCellRange
    Dim lngCount
    Dim objSheet
    Set objSheet = ThisWorkbook.Worksheets("Config - Styles")
    With objSheet
        lngCount = objSheet.UsedRange.Rows.Count + 1
        For Each objStyle In ThisWorkbook.Styles
            On Error Resume Next
            Set objCellRange = Nothing
            Set objCellRange = Intersect(objSheet.UsedRange, objSheet.Range("A:A")).Find(objStyle.Name, _
            objSheet.Range("A1"), xlValues, xlWhole, , , False)
            If objCellRange Is Nothing Then
                lngCount = lngCount + 1
                .Cells(lngCount, 1).Style = objStyle.Name
                .Cells(lngCount, 1).Value = objStyle.NameLocal
                .Cells(lngCount, 2).Style = objStyle.Name
            End If
        Next
    End With
End Sub

To set this up, double click on the Sheet1 tab and rename it "Config - Styles". Add the code above, then run the script. What you end up with is this:

answered Jan 07 '17 at 16:32

Lou

389
3
20
38

I was not able to make table styles work the same way https://jsfiddle.net/63wa5jh7/ – tumelo Jan 09 '17 at 13:55
Thanks for the answer, but it appears ListObjects needs to be a part of the solution when it comes to table styles. – tumelo Jan 09 '17 at 14:47
I can modify this code and list the Table Style names so I should have access to them... – tumelo Jan 09 '17 at 15:01
One thing that may be the problem is you are adding VBScript to a JavaScript editor. While some things work together, others don't. Also, lines 37 and 38 in your JavaScript editor are part of line 36...they should all be on the same line. That could have something else to do with it. Open up an Excel spreadsheet and the code should work as I've shown. – Lou Jan 09 '17 at 18:38
I was just using jsfiddle to share the code. I didn't run it on jsfiddle. And your code definitely works as shown. I'm just saying I can't get it to work the same way with table styles as it works with regular styles. – tumelo Jan 09 '17 at 21:41

TableStyle with VBScript

1 Answers1