EXcel 2010 VBA problem inserting an integer into a cell using ADO SQL string 2

[DougP]

I am trying to add counts of managers from a list. I can get the counts but when its inserted into the cell it puts a single quote in front of it and then Excel won't Sum it since it's a text value and not a number, I need a total at the bottom.
I can see the value of Thecount is an integer when I do ?Thecount in the immediate window since it puts a space in from of it when showing the result.
But the "Insert" statement converts it to a [highlight #FCE94F]'8 instead of 8.[/highlight] Of course the Manager does not have a leading quote. Is this some quirk in Excel? ver 2010?
Even if I format the whole column as a number with no decimal it’s still won't work!
I tried making the ,TheCount as Integer) in the function and that won't work either.
Then I tried to replace the single quote using Ctrl F and Excel says "Can't find what you looking for?"
the number are left justifed and is also a tiny green triangle in the upper left of each cell? What does that mean?
If I manually delete the single quote it then makes it a integer and its right justified in the cell.
So then it will work but this is tooooo time consuming?v Too many rows to do. That's why I write CODE.
The yellow line is the cuprit. it puts a "odd" string in the cell with one single quote which is not there if you search for it?
What do I need to do to make it an integer and not string/text value?

'this function inserts the Manager name into a sheet with counts
Public Sub InsertDataCounts(cn, Sheetname, Manager, TheCount)
  Dim SQLString As String
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    
    SQLString = "Insert into [" & Sheetname & "$] " & _
                "(Manager, TheCounter) Select " & _
                "'" & Manager & "' as Manager, " & _
                "" & TheCount & " as TheCounter"
                   
   [highlight #FCE94F] rst.Open SQLString, cn, adOpenDynamic, adLockOptimistic[/highlight]   
Set rst = Nothing
End Sub

-------------
' this function finds all the managers in another sheet using a group by and a sum
Public Function ReturnCounts(cn, Manager)
    Dim Path As String
    Dim Filename  As String
    Path = ActiveWorkbook.Path
    Filename = ActiveWorkbook.name
    Dim rstTheManager As ADODB.Recordset
    Set rstTheManager = New ADODB.Recordset
    With ActiveWorkbook.Worksheets("Sheet4")
        .Range("A2", .Range("A2").End(xlDown)).Delete
    End With
    Sheets("Sheet4").Activate
    With ActiveSheet
       .Range("A2").Activate
    End With
     SQLTheManager = "SELECT Manager, Count(Manager) as TheCounter  FROM [Sheet1$] " & _
                    "Group by Manager"
    rstTheManager.Open SQLTheManager, cn, adOpenDynamic, adLockOptimistic
    For t = 1 To rstTheManager.RecordCount
         strName = rstTheManager.Fields("Manager").Value
         intCounter = rstTheManager.Fields("TheCounter").Value
         Call InsertDataCounts(cn, "Sheet4", strName, intCounter)
        rstTheManager.MoveNext
    Next
    Set rstTheManager = Nothing
End Function

TIA


DougP

 

[Andrzejek]

I format the whole column as a number with no decimal

Did you try to do it before you place the data in the column?

Have fun.

---- Andy

 

[PHV]

I'd try this also:

SQLString = "Insert into [" & Sheetname & "$] " & _
            "(Manager, TheCounter) VALUES (" & _
            "'" & Manager & "', " & TheCount & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DougP]

Andrzejek, that was it
If you don't set the column up as integer prior then it will never be integer.
this was actually another sheet I was using as a test. so in the real sheet I formatted it to integer first before, and now it works perfect.

Is the anyway to change it?

DougP

 

[SkipVought]

Remember the changing a range's format changes NOTHING in the underlying data.

You could copy a cell containing the number 1

Select your TEXT range

Right-Click > Paste Special -- MULTIPLY > [OK]

The multiplication will coerce a CONVERSION from TEXT to NUMBER for any valuse that can be converted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DougP]

good one Skip

DougP