Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
clear rows and insert records from recordset on refresh
Hi Everyone,
I need some assistance with trying to achieve what I'm wanting to do. I have a worksheet that has a series of headings and a row that sums up the totals. I am guessing that there will be further calculations but for now I'm trying to achieve the first step which is to delete the existing data and insert the new data. For example, If I want to look at a certain branch's data, I insert their account number and refresh the calculation. This will bring up all the records associated with that branch and provide subtotals. I have managed to succesfully create an ADO connection to an MS Access database with the code below, but unfortunatley for me it's not clearing the data and I can't seem to find how I go about doing it. I did think of using .XLDOWN to delete the existing data and .INSERT to insert the data but I have a feeling that there is something more practicable to do this. Any assistance would be appreciated. <CODE 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 </CODE |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code - Clear and Insert & If Statement | Excel Programming | |||
Insert Decimal Places and Clear Zeros | Excel Discussion (Misc queries) | |||
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 |