ADO Query Excel From Excel 1

[ClulessChris]

I've been running a varient for the following code to query 1 Excel workbook from another Excel work without problem for about 18 months. Now it's stopped reading the field 'Closed' (this is a date formatted column "dd/mm/yyyy hh:mm") although it will read other columns of the same format.

I'm totaly chasing my tail to see the bug. can you help?

    Dim Conn                    As New ADODB.Connection
    Dim oRS                     As New ADODB.Recordset
    Dim sSQL                    As String
    
    On Error Resume Next
        
    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:/Temp/XLDump-091843.xls; " & _
            "Extended Properties=""Excel 8.0;HDR=Yes;"";"
    
    sSQL = "SELECT * " & _
            "FROM [Sheet1$] " & _
            "WHERE [Team Folder] ='Team E' "
            
    oRS.Open sSQL, Conn, adOpenStatic, adLockOptimistic, adCmdText
        Debug.Print oRS.RecordCount
        
        Do Until oRS.EOF
            Debug.Print oRS.Fields.Item("Closed")
            oRS.MoveNext
        Loop

    oRS.Close

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[ClulessChris]

To be more specific here is the code throwing the error

Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:/Temp/XLDump-091843.xls; " & _
            "Extended Properties=""Excel 8.0;HDR=Yes;"";"
           
    For J = lStartRow To lLastRow
        sSQL = "SELECT * " & _
            "FROM [Sheet1$] " & _
            "WHERE [Team Folder] ='" & WorkSheetName & "'"
                   oRS.Open sSQL, Connection, adOpenStatic, adLockOptimistic  ', adCmdText
        sRtn = Split(IndivITCOCases(oRS), ",")

here is an extract from the 'IndivITCOCases' procedure in to which the recordset is passed

With RecSet
        Do Until .EOF         'loop through each Team Name
            If ![Current Owner] = "Closed - Case Completed" Then        'Case has been closed
'                Count same day clear
                If Year(!Received) = Year(![Closed]) And _
                        Month(!Received) = Month(![Closed]) And _
                        Day(!Received) = Day(.Fields.Item(![Closed])) Then
                    lSameDayClear(0) = lSameDayClear(0) + 1
                    If InStr(!Subject, "SRS") <> 0 Then lSameDayClear(1) = lSameDayClear(1) + 1
                               
'                Count Worked in 1 week
                ElseIf DateDiff("d", !Received, !Closed, vbMonday, vbFirstFourDays) <= 7 Then
                    lWorkedInWeek(0) = lWorkedInWeek(0) + 1
                    If InStr(!Subject, "SRS") <> 0 Then lWorkedInWeek(1) = lWorkedInWeek(1) + 1
                    
                End If
                
'            Case still open
            ElseIf DateDiff("d", !Received, !Closed, vbMonday, vbFirstFourDays) >= 10 Or _
                DateDiff("d", !Received, Now(), vbMonday, vbFirstFourDays) >= 10 Then
'                Count Over 10 days Old
                lOver10Days(0) = lOver10Days(0) + 1
                If InStr(!Subject, "SRS") <> 0 Then lOver10Days(1) = lOver10Days(1) + 1
                
                If DateDiff("d", !Received, !Closed, vbMonday, vbFirstFourDays) >= 20 Or _
                    DateDiff("d", !Received, Now(), vbMonday, vbFirstFourDays) >= 20 Then
'                    Count Over 20 days old
                    lOver20Days(0) = lOver20Days(0) + 1
                    If InStr(!Subject, "SRS") <> 0 Then lOver20Days(1) = lOver20Days(1) + 1
                
                End If
            
            End If
            .MoveNext
            
        Loop
    End With

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[SkipVought]

Hi,

You never state EXACTLY WHICH STATEMENT has the error.

Now it's stopped reading the field 'Closed' (this is a date formatted column "dd/mm/yyyy hh:mm") although it will read other columns of the same format.

I would check the data in that specific column in the SOURCE worksheet. Change the FORMAT for that COLUMN to GENERAL. ALL the real date values will display the NUMERIC DATE/TIME SERIAL VALUE. If you have some cells that do NOT behave this way, they are TEXT values, not Date/Time values. In that case...
[tt]
1. enter a 1 in an empty cell
2. COPY that cell
3. select ALL the data in that column (numeric & text)
4. Edit > Paste Special -- MULTIPLY
[/tt]
Now you should have ALL dates, unless there was a fundamental problem with the date/time TEXT values. Your query should work for that column.


Skip,

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

 

[SkipVought]

I just noticed something else. IF any of your TEXT values have DAYS (dd/mm/yyyy greather than 12, this conversion will most likely fail.

Rather do this...
[tt]
1. select that column
2. Data > Text to columns -- FIXED
3. put a field separator between the date & time
4. select the DMY Date conversion for date
5. finish
[/tt]
CAVEAT: your TIME will still be a string. Use the MULTIPLY by 1 method.

Skip,

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

 

[ClulessChris]

Skip,

Thanks for your time. There is no Error as such, it's just that the code isn't reading the info that 'I Know' is there.

I'll look further into the formats and see where that leads.

IF any of your TEXT values have DAYS (dd/mm/yyyy greather than 12, this conversion will most likely fail.

thanks for the tip could you help me understand why this is may be?

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[SkipVought]

Because Bill Gates & Co. lives in the USA ie default to mm/dd/yyyy.

Skip,

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