VBA SQL fetching the old cache Data

[pwnyadav007]

Hi All,

I am using the SQL query in Excel and fetch the data from Sheet1 to Sheet2 and after Sheet2 to Sheet3 via one code and it is working fine in the Excel 2013 but when I am running this in Excel 2007, Then is transfer the data on Sheet1 to Sheet2 and it is not transfer the data from Sheet2 to Sheet3. If in Sheet2 has any data before updating the data from Sheet1 then Sheet3 is updated with the old Data of Sheet2. I have resolve the problem by Saving the Workbook after first query and then run the second query. But due to large data it is taking time, So is there any quick solution of that.

Here is an example:
I have 3 sheets in the Workbook: Sheet1, Sheet2, Sheet3. Now,I have created a dummy database in Sheet1 and write this Script at the backend:



Sub SQL123()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * FROM [Sheet1$]"

rs.Open strSQL, cn, 3, 3

Sheet2.Range("A2").CopyFromRecordset rs
For iCols = 0 To rs.Fields.Count - 1
Worksheets("Sheet2").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
cn.Close

'ThisWorkbook.Save
cn.Open strCon
strSQL = "SELECT * FROM [Sheet2$]"

rs.Open strSQL, cn, 3, 3
Sheet3.Range("A2").CopyFromRecordset rs
For iCols = 0 To rs.Fields.Count - 1
Worksheets("Sheet3").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next

End Sub

Here I want to eliminate "'ThisWorkbook.Save" line.


Regards,
Pawan

[xlnitwit]

Hi,

You need to wrap your code in code tags please, following the forum rules.

It would also help if you explain why you are copying the same data from sheet to sheet using two separate queries.

[RowanB]

Was there ever a resolution to this problem? I'm having the same problem on Excel 2013. Any help with this would be much appreciated.