VB6 formatting Excel worksheet table

I've exported a MSFlexGrid to an Excel Workbook Worksheet, and have made it visible. That goes well.
I am trying format the used range as a table, using table style TableStyleLight1 (an Excel canned style). I've set up a string variable to hold the table style, but am unable to find the method that will make it work. The columns are autofitted, as per the code, but after that no further formatting is done.

Here is the code I'm using:

' Write FlextGrid-data to an Excel table.
Private Sub FlexGrid_To_Excel()
Dim objXL As New Excel.Application
Dim wbXL As New Excel.Workbook
Dim wsXL As New Excel.Worksheet
Dim intRow As Integer ' counter
Dim intCol As Integer ' counter
Dim TheRows As Long
Dim TheCols As Integer
Dim TableStyle As String
Dim WorkSheetName As String

GridStyle = 1
TableStyle = "TableStyleLight1"
WorkSheetName = "ScheduleD"


If Not IsObject(objXL) Then
MsgBox "You need Microsoft Excel to use this function", _
vbExclamation, "Print to Excel"
Exit Sub
End If

TheRows = MSFlexGrid2.Rows
TheCols = MSFlexGrid2.Cols

On Error Resume Next

' open Excel
objXL.Visible = True
Set wbXL = objXL.Workbooks.Add
Set wsXL = objXL.ActiveSheet

' name the worksheet
With wsXL
If Not WorkSheetName = "" Then
.Name = WorkSheetName
End If
End With

' fill worksheet
For intRow = 1 To TheRows
For intCol = 1 To TheCols
With MSFlexGrid2
wsXL.Cells(intRow, intCol).Value = _
.TextMatrix(intRow - 1, intCol - 1) & " "
End With
Next
Next

' size the column width
For intCol = 1 To TheCols
wsXL.Columns(intCol).AutoFit
Next intCol

'format the entire range as a table ****This doesn't work.
For intRow = 1 To TheRows
wsXL.Range("a1", Right(wsXL.Columns(TheCols).AddressLocal, _
1) & TheRows).Select
Next intRow
wsXL.UsedRange.Name = ("Table1")
wsXL.UsedRange("Table1").Format TableStyle


Set objXL = Nothing
Set wbXL = Nothing
Set wsXL = Nothing

End Sub

Everything goes well until the last part,marked with the ******.

Appreciate help on hot to apply table style.

Thanks.




Please post Excel questions, in the Excel forum.

Please use the .. tags when you post your code. Edit or reply to this post to see how.

Thank you.