excel 2010 VBA writing values to cells using ADO SQL strings

[DougP]

I have this code which opens the Excel file and gets the "Sql" string just fine.
I want to then write some data back out to another TAB on the same eeXcel file using the SQLInsert string below.
I am getting an error "Syntax error in INSERT INTO Statement".
here is what the line has created, it has a name and some counts accumulated as it moves in a loop.
Insert into ([Sheet1$]) Select ([Flintstone, Fred (14, 0, 6)]) as Expr1

What do I need to do to make the Insert statement correct?
TIA

    Path = "C:\"
    Filename = "myfile.xls"
    Set cn = New ADODB.Connection
    Set rsT = New ADODB.Recordset
    Set rsT4 = New ADODB.Recordset
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & Path & _
        "\" & Filename & ";Extended Properties=Excel 8.0;"
        .CursorLocation = adUseClient
        .Open
    End With
    Sql = "Select[LDAP Manager], Count([LDAP Full Name]) as Total from [Base$] " & _
            "Group by [LDAP Manager]"
    rsT.Open Sql, cn, adOpenDynamic, adLockPessimistic
....
    SQLInsert = "Insert into ([Sheet1$]) Select ([" & CountData & "]) as Expr1"
    rsT4.Open SQLInsert, cn, adOpenDynamic, adLockPessimistic
    rsT4.Close

DougP

 

[SkipVought]

Doug,

Why not just perform a query and use the CopyFromRecordset into Sheet1..

SQL = "Select ([" & CountData & "]) as Expr1 [b]From [WhatSheet???$][/b]"
rsT4.Open SQL, cn, adOpenDynamic, adLockPessimistic
[b]Sheets("Sheet1").[A2].CopyFromRecordset rsT4[/b]
rsT4.Close

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DougP]

Skip, the data is being created in my procddure it is not copying from another Excel sheet.
I am using a sheet to get the starting data then doing some manipulations and creating "more data". So I thought I would just use a another TAB to store the "more data" until later.
so the "From [WhatSheet???$]" is "From" this variable as follows:
CountData = Manager & " (" & EmplCount & ", " & SOWCount & ", " & TMCount & ")"
that is where the "Select ([" & CountData & "]) as Expr1" in my original code above came from.

can we do that?
this VBA code is actually In Powerpoint. I am using data from Excel to try and automatically create slides.

DougP