sql query in vba not getting data from temporary sheet

[Shanki]

Hi All,

I am using ADODB connection to query sheets in excel. There is one temporary sheet which holds some data while macro is running and this data is dynamic..But when I query this temp sheet i dont get anything..but if sheet is saved then i get data.. Its like i put data in temp sheet but it cant be read until its kind of commited...But i dont know how to do that...

Can someone please help me out?

Thanks

 

[Jon von der Heyden]

Welcome to the forum!

Sheet or workbook?

I would write it out to a temp workbook, then query the workbook, and then delete the workbook. What bit are you stuck on? Perhaps you should post the code.

 

[Shanki]

Thanks,

Its a sheet...actuaaly i have two machines one server and one windows 7..its working on server not on windows 7..both have office 2010

below is code

'Iterate through all rows in Result sheet
    For rowIndex = 2 To totalRows
       
        
        strSQL = "SELECT DISTINCT c.F10 , c.F11 FROM [Sheet1$] c WHERE c.F1= '" & r1 & "' AND c.F2 = '" & r2 & "' AND c.F3='" & r3 & "' AND c.F4='" & r4 & "' AND c.F12 = 'Yes' ORDER BY c.F11"
        rs.Open strSQL, cn, 3, 3
            
        Worksheets("Temp").Cells(1, 1).CopyFromRecordset rs
        rs.Close
        
        
        strSQL = "SELECT DISTINCT a.F12 , t.F2 FROM [Sheet2$] a LEFT JOIN [Temp$] t ON (a.F12 = t.F1) WHERE a.F8 = '" & r1 & "' AND a.F9 = '" & r2 & "' AND a.F10 = '" & r3 & "' AND a.F11 = '" & r4 & "' AND a.F1 = '" & r5 & "' ORDER BY t.F2"
        rs.Open strSQL, cn, 3, 3
        Worksheets("Temp").Cells(1, 3).CopyFromRecordset rs

        If rs.RecordCount > 0 Then
             rs.MoveFirst
                 Do Until rs.EOF
                     atrAud = atrAud & rs.Fields(1) & "#)" & rs.Fields(0).Value & "," & Chr(10)
                     rs.MoveNext
                Loop
            End If
        rs.Close
      
       Worksheets("Result").Cells(rowIndex, 8) = atrAud
       atrAud = ""
[COLOR=#ff0000]//NOT READING FROM TEMP BUT WHEN I CHECK IN DEBUG TEMP HAS DATA//[/COLOR]
       strSQL = "SELECT COUNT(t.F1) FROM [Temp$] t WHERE t.F1 IS NOT NULL"
       rs.Open strSQL, cn, 3, 3
       Worksheets("Result").Cells(rowIndex, 7).CopyFromRecordset rs
       rs.Close
        
[COLOR=#ff0000]//NOT READING FROM TEMP BUT WHEN I CHECK IN DEBUG TEMP HAS DATA//[/COLOR]
       
       strSQL = "SELECT COUNT(t.F3) FROM [Temp$] t WHERE t.F3 IS NOT NULL"
       rs.Open strSQL, cn, 3, 3
       Worksheets("Result").Cells(rowIndex, 6).CopyFromRecordset rs
       rs.Close
        

       strSQL = "SELECT t.F1,t.F2 FROM [Temp$] t WHERE t.F1 IS NOT NULL AND t.F1 NOT IN (SELECT tp.F3 FROM [Temp$] tp WHERE tp.F3 IS NOT NULL ORDER BY tp.F2) ORDER BY t.F2"
       rs.Open strSQL, cn, 3, 3
       Worksheets("Temp").Cells(1, 6).CopyFromRecordset rs
       If rs.RecordCount > 0 Then
            rs.MoveFirst
                Do Until rs.EOF
                    atrAud = atrAud & rs.Fields(1) & "#)" & rs.Fields(0).Value & "," & Chr(10)
                    rs.MoveNext
                Loop
       End If
       Worksheets("Result").Cells(rowIndex, 9) = atrAud
       atrAud= ""
       rs.Close
       'Clear contents of temp sheet
       Worksheets("Temp").Cells.ClearContents
   Next rowIndex

 

[Jon von der Heyden]

Does it work on Sheet1$ and Sheet2$?

I'm wondering if Temp$ is an invalid table name? I don't actually know but maybe worth trying after using a different name.

Or maybe check the table structure. Are the field names in row 1? And maybe even consider naming the table range and then refer to the name rather than the sheet.

Are all the sheets in the same book and are you using the same connection?

 

[Shanki]

I tried with different name and also checked with table instead of sheet...But untill workbook is saved updated values are not read from sql query...

and same is working on diff machine...

 

[Jon von der Heyden]

Well the saving part makes sense to me. If you don't want it saved then dump the data to a temp workbook, save it and then query the temp workbook.

To save the workbook you use:

Call ThisWorkbook.Save