' This VBScript code assumes the table starts in row 1 column 1
    ' (cell A1) on each sheet in a user-specified workbook.

    Dim j, nSheets
    Dim strTblNamePrefix, strWorkBookName
    Dim objExcel, objWorkBook, objApp
    dim lngXlUp, lngXlToLeft, lngXlSrcRange, lngXlYes
    
    ' make sure path to excel file is specified on command line
    ' If not running interactively, comment or delete this If block. Use JMP or other means to test the
    ' Wscript arguments.
    If Wscript.Arguments.Count <> 1 Then
        Wscript.Echo _
              "Please supply the path to an excel file on the command line"
        Wscript.Quit 1
    End If

    ' Initialization
    strTblNamePrefix   = "Tbl_"
    strWorkBookName    = WScript.Arguments.Item(0)
    Set objExcel       = CreateObject("Excel.Application")
    Set objWorkbook    = objExcel.Workbooks.Open(strWorkBookName)
    Set objApp         = objExcel.Application
    objApp.Visible     = True
    objApp.CutCopyMode = False
    lngXlUp            = -4162     ' objExcel.XlDirection.xlUp
    lngXlToLeft        = -4159     ' objExcel.XlDirection.xlToLeft
    lngXlYes           = 1         ' objExcel.XlYesNoGuess.xlYes
    lngXlSrcRange      = 1    ' objExcel.XlListObjectSourceType.xlSrcRange
    nSheets            = objWorkbook.Sheets.Count

    For j = 1 To nSheets
        objWorkbook.Sheets(j).Activate
        objWorkbook.Sheets(j).Select
        
        ' if cell A1 not empty, format sheet as a table
        If trim(objExcel.Range("A1")) <> "" then
            With objApp.ActiveSheet
                Dim i, n

                n = .ListObjects.Count
                For i = 1 To n
                    ' Strip all table names from curr sheet so this script is
                    ' re-entrant (can't add the same table repeatedly in Excel).
                    ' Also, remove any associated table style as well so that
                    ' a new style can be applied (if applicable).
                    .ListObjects(i).TableStyle = ""
                    .ListObjects(i).Unlist
                Next
                formatTable .Name
            End With
        End If
    Next

    ' Return focus to the first sheet
    objWorkbook.Sheets(1).Activate
    objWorkbook.Sheets(1).Select

   ' Save and Quit
    objApp.ActiveWorkbook.Save()
    objApp.Quit

    Wscript.Quit 0

Sub formatTable(strSheetName)
    Dim lngLastRow, lngLastCol, i, n
    Dim strTblName

    ' Determine table geometry, assuming table starts at cell A1.
    lngLastRow = objApp.ActiveSheet.Cells(1048576, 1).End(lngXlUp).Row
    lngLastCol = objApp.ActiveSheet.Cells(1, 16384).End(lngXlToLeft).Column

    With objApp.ActiveSheet
        ' Create the table reference and select it
        strTblName = strTblNamePrefix & strSheetName
        .ListObjects.Add( _
             lngXlSrcRange, _
             objExcel.Range("A1", objExcel.Cells(lngLastRow, lngLastCol)), _
             , _
             lngXlYes).Name = strTblName

        ' Set table style this has dark blue headers, alternating blue,
        ' white rows and slicers/filters
        .ListObjects(strTblName).TableStyle = "TableStyleMedium6"

        ' Accentuate the headers
        .ListObjects(strTblName).HeaderRowRange.Select
         With objExcel.Selection.Font
            .Name = "Arial Black"
            .Size = 12
            .Bold = True
        End With

    End With

 
    ' Code to AutoFit the entire table
    Dim objCbeg, objCend
    Set objCbeg = objApp.ActiveSheet.Columns(1)
    Set objCend = objApp.ActiveSheet.Columns(lngLastCol)
    objApp.ActiveSheet.Range(objCbeg, objCend).Select
    objApp.ActiveSheet.Range(objCbeg, objCend).EntireColumn.AutoFit
    
    ' Code to Deselect table
    objExcel.Range("A1").Select 
End Sub