open table and copy selected records to excel sheet

[hsingh1981]

Hi all,

I have this table which collects certain data table name "temp_KPIData".

I can open the table and copy the first row field to the excel sheet.

My code for this is this:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Temp_KPIData", dbOpenSnapshot)

'Open a Excel  and move to "source data sheet"

Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet

Set objXL = New Excel.Application
    With objXL
        .Workbooks.Open Me.txtExcelFileLocation
    End With

objXL.Visible = True


Set objSht = objXL.Worksheets("KPI figures 1")


'Add the field names in row 1

objSht.Cells(10, "I").Value = rs.Fields("MaxAllowTarget")




objXL.Visible = True
objXL.UserControl = True
'Close the Database and Recordset
rs.Close
db.Close
'

My question is how do i select certain rows depending on the id field and copy it to the excel sheet.

I have KPI_ID.....in (Temp_KPIData)

So basically if KPIId = 1 copy data to objSht.Cells(10, "I").Value

if KPIId = 24 copy data to objSht.Cells(15, "I").Value

and so on.....not sure how i do this?

many thanks

 

[hsingh1981]

ok kind of sussed it out but can't seem to fill the first records. It gets the other records but not the first? :s

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Temp_KPIData", dbOpenSnapshot)

'Open a Excel  and move to "source data sheet"

Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet

Set objXL = New Excel.Application
    With objXL
        .Workbooks.Open Me.txtExcelFileLocation
    End With

objXL.Visible = True


Set objSht = objXL.Worksheets("KPI figures 1")

rs.MoveNext

Do Until rs.EOF
Select Case rs.Fields("KPI_ID")

Case 4
objSht.Cells(4, "I").Value = rs.Fields("MaxAllowTarget")

Case 33
objSht.Cells(42, "I").Value = rs.Fields("MaxAllowTarget")

Case 34
objSht.Cells(43, "I").Value = rs.Fields("MaxAllowTarget")

End Select


rs.MoveNext
Loop

rs.Close
objXL.Visible = True
objXL.UserControl = True
db.Close

 

[PHV]

Get rid of the first rs.MoveNext ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hsingh1981]

thankyou for replying

Tried that and still its not getting the value and placing it on the excel sheet. The other values does appear?

 

[SkipVought]

Do Until rs.EOF
Select Case rs.Fields("KPI_ID")

Case 4
objSht.Cells(4, "I").Value = rs.Fields("MaxAllowTarget")

Case 33
objSht.Cells(42, "I").Value = rs.Fields("MaxAllowTarget")

Case 34
objSht.Cells(43, "I").Value = rs.Fields("MaxAllowTarget")

End Select


rs.MoveNext
Loop

the ROW value in Excel NEVER CHANGES!!!

What do you expect to happen when MoveNext occurs????

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hsingh1981]

The row value in excel does change. Its the column I that remains constant.

The Move next goes through each KPI_ID and get the relevant value from the field "MaxAllowTarget".

It then goes to excel and places it in the relevant cell

In excel

Cell

I4 is 74%
I42 is 95%
I43 is 90%


For some reason it does not add the first value in....It populates the rest of the values in excel?

 

[SkipVought]

Does rs.Fields("KPI_ID") have a value of 4 in the resultset that you return?

Use you watch window to insepct each value in your loop.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Perhaps this ?
Select Case [!]Val([/!]rs.Fields("KPI_ID")[!])[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886