Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
clear rows and insert records from recordset on refresh
Hi everyone, I tried posting this a few hours ago but it didn't
appear to load. What I am trying to achieve is that when I refresh (calculate) the report, the rows between the headings and totals is deleted and the new data is inserted. I have seen it done before in other applications but I am unable to find the answers. I have created a SQL connection to the access database and it's filtering when I recalculate the spreadsheet on a value in a cell. Unfortunately for me, it is not deleting the previous data and inserting the fresh data. Can someone provide any further assistance? The VBA code on doing the SQL connection is below. Sub import_salaries() Dim conn As New Connection, rec As New Recordset Dim ws As Worksheet Dim sql$, i& Set ws = ThisWorkbook.Worksheets("Sheet1") conn.Open "Provider=microsoft.jet.oledb.4.0;" + _ "Data Source=" + ThisWorkbook.Path + "\HR_Occupancy_Table.mdb" ' T3Lookup = ws.Range("CCLOOKUP").Value sql = "SELECT Employee_No, Employee_Name, Cost_Centre_Description, Salary_Year, T3_CC_Lookup " & _ "FROM HR_OCCUPANCY WHERE T3_CC_Lookup = " & ws.Range("CCLOOKUP").Value & " ORDER BY Cost_Centre_Description " rec.Open sql, conn While Not rec.EOF i = i + 1 ws.[A5].Cells(i) = rec!Employee_No ws.[B5].Cells(i) = rec!Employee_Name ws.[C5].Cells(i) = rec!Cost_Centre_Description ws.[D5].Cells(i) = rec!Salary_Year ws.[E5].Cells(i) = rec!T3_CC_Lookup rec.MoveNext Wend rec.Close: conn.Close End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
clear rows and insert records from recordset on refresh
I saw your earlier post, and here was my reply:
I take that you don't want to perform row-by-row deletions/insertions. You can use the adOpenStatic CursorType to get a scrollable recordset, then use RecordCount to figure out how many rows to clear/insert. Then you can clear out data in one action...much more efficient than deleting data one row at a time. rec.Open sql, conn, adOpenStatic ws.Range("A5").EntireRow.Resize(rec.RecordCount).I nsert shift:=xlDown ' Use RecordCount in conjunction with xlDown to know how many rows to batch delete/insert before adding the recordset. While Not rec.EOF ... rest of code I would imagine you would use a combination of xlDown and Insert to know how many rows to delete beforehand. Hard to give more advice on where to put the data without more specifics. Hope you can adapt this, if not post back with more details. -- Tim Zych www.higherdata.com Compare data in Excel and find differences with Workbook Compare A free, powerful, flexible Excel utility "Santa-D" wrote in message ... Hi everyone, I tried posting this a few hours ago but it didn't appear to load. What I am trying to achieve is that when I refresh (calculate) the report, the rows between the headings and totals is deleted and the new data is inserted. I have seen it done before in other applications but I am unable to find the answers. I have created a SQL connection to the access database and it's filtering when I recalculate the spreadsheet on a value in a cell. Unfortunately for me, it is not deleting the previous data and inserting the fresh data. Can someone provide any further assistance? The VBA code on doing the SQL connection is below. Sub import_salaries() Dim conn As New Connection, rec As New Recordset Dim ws As Worksheet Dim sql$, i& Set ws = ThisWorkbook.Worksheets("Sheet1") conn.Open "Provider=microsoft.jet.oledb.4.0;" + _ "Data Source=" + ThisWorkbook.Path + "\HR_Occupancy_Table.mdb" ' T3Lookup = ws.Range("CCLOOKUP").Value sql = "SELECT Employee_No, Employee_Name, Cost_Centre_Description, Salary_Year, T3_CC_Lookup " & _ "FROM HR_OCCUPANCY WHERE T3_CC_Lookup = " & ws.Range("CCLOOKUP").Value & " ORDER BY Cost_Centre_Description " rec.Open sql, conn While Not rec.EOF i = i + 1 ws.[A5].Cells(i) = rec!Employee_No ws.[B5].Cells(i) = rec!Employee_Name ws.[C5].Cells(i) = rec!Cost_Centre_Description ws.[D5].Cells(i) = rec!Salary_Year ws.[E5].Cells(i) = rec!T3_CC_Lookup rec.MoveNext Wend rec.Close: conn.Close End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clear rows and insert records from recordset on refresh | Excel Programming | |||
Code - Clear and Insert & If Statement | Excel Programming | |||
Insert Next? Or insert a variable number of records...how? | Excel Discussion (Misc queries) | |||
Access Recordset Rows to Excel | Excel Programming | |||
Returning the Number of Rows in a Recordset | Excel Programming |