Access rs to Excel sheet

[DMT Dave]

Hi Guys, i beg your forgiveness on this, even after walking away from it and returning, i can't appear to count the number of rows used in excel and add data!!!



Yes i have done similar kind of procedure many times, unlike Sister Sledge lost in music, Dave is Lost in looping

I am trying to add the recordset results to the relevant cells in Excel

I haven't posted all code but all Dims are set pOpen is string path open and fOpen is string File open

I believe it is an event sequence error ?

   strPC = "Customer postcode removed on forum" 
    pOpen = "T:\DMT Ltd\Images\Stored Images\Customer Folder Name removed \"
    fOpen = "Condition Report.xlsx"
    Set apXL = CreateObject("Excel.Application")
    Set xlWB = apXL.Workbooks.Open(pOpen & fOpen)
    Set xlWS = xlWB.Worksheets("Sheet1")
    apXL.Visible = False
    
    Set rs = CurrentDb.OpenRecordset("Select * From tblEdit WHERE ShipmentDate = #" & dtShipDate & "# And PostCode = '" & strPC & "'")
    
    With xlWS
        
        intLR = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Do Until rs.EOF
            
            strSL = rs.Fields("SONumber")
            strMFG = rs.Fields("ItemNo")
            dtShipDate = rs.Fields("ShipmentDate")
             
        .Worksheets(1).Cells(intLR + 1, 1) = dtShipDate
        .Worksheets(1).Cells(intLR + 1, 2) = ""
        .Worksheets(1).Cells(intLR + 1, 3) = strSL
        .Worksheets(1).Cells(intLR + 1, 4) = strMFG
        .Worksheets(1).Cells(intLR + 1, 5) = sDamage
        .Worksheets(1).Cells.EntireColumn.HorizontalAlignment = xlLeft
             
        rs.MoveNext
        
        Loop
        
        xlWB.Save
        xlWB.Close
        apXL.Quit
        
    End With
       
    Debug.Print strSL & "; " & strMFG
    
Set apXL = Nothing


Application.FollowHyperlink pOpen & fOpen

Again, please forgive me as I should be able to achieve this easily, just got lost in it

[June7]

"can't appear to count" - why not, what happens (error message, wrong result, nothing)?

Instead of looping recordset, maybe try CopyFromRecordset method. Only pull necessary fields into recordset.

"SELECT #" & dtShipDate & "# AS Dte, '', SONumber, ItemNo, '" & sDamage & "' AS Dmg FROM tblEdit ..."

xlWs.Range("A" & intLR + 1).CopyFromRecordset rs

[pbaldy]

I would generally use CopyFromRecorset as well, but part of your problem is that you never increment intLR within the loop, so the same row will keep getting overwritten.

[Welshgasman]

Again, if you walked your code, you should be able to find that out.

[DMT Dave]

Sorry, what i should have stated sDamage isn't a field, it's a string from an inputbox set of options

 iCondition = InputBox("Enter The Condition Of Your Images ?" & vbCrLf & vbCrLf & _        
        Chr(149) & " 1 " & Chr(149) & "Good, No Visible Damage" & vbCrLf & vbCrLf & _
        Chr(149) & " 2 " & Chr(149) & "Good, Packaging Damage" & vbCrLf & vbCrLf & _
        Chr(149) & " 3 " & Chr(149) & "Marked, Very Light Damage" & vbCrLf & vbCrLf & _
        Chr(149) & " 4 " & Chr(149) & "Marked, Heavy Damage", "ENTER CONDITION", "1")
        Select Case iCondition
            Case Is = 1
                sDamage = "Good, No Visible Damage"
            Case Is = 2
                sDamage = "Good, Packaging Damage"
            Case Is = 3
                sDamage = "Marked, Very Light Damage"
            Case Is = 4
                sDamage = "Marked, Heavy Damage"

        End Select

So the target is to:

Loop through a set of records on 1 particular postcode

Excel Sheet



If 3 records for that postcode, add the data in from recordset rows 2,3,4 because row 1 is header

then update the condition column (F) according to the SO / item no, this is from iCondition Variable

Have now changed the recordset with these fields so i can use CopyFromRecordset option

i have and do use this option elsewhere but i was thinking i am trying to update this sheet twice, once from rs then update again from iCondition ?

now adjusted to this:

NOTE: not changed to CopyFromRecordset yet, just trying to loop it but can change

    sSQL = "SELECT tblEdit.ShipmentDate, tblEdit.DelTo, tblEdit.PostCode, tblEdit.SONumber, tblEdit.ItemNo " _ 
           & "From tblEdit " _
            & "WHERE (((tblEdit.ShipmentDate)=#" & dtShipDate & "#) AND ((tblEdit.PostCode)='" & strPC & "'));"


    Set rs = CurrentDb.OpenRecordset(sSQL)
    
    With rs
        Do Until rs.EOF
            strSL = strSL & rs.Fields("SONumber")
            strMFG = strMFG & rs.Fields("ItemNo")
            dtShipDate = dtShipDate & rs.Fields("ShipmentDate")
            strPostCode = strPostCode & rs.Fields("PostCode")
            sDelTo = sDelTo & rs.Fields("DelTo")
            rs.MoveNext
        Loop
    End With
    
    With xlWS
    intLR = Cells(Rows.Count, 1).End(xlUp).Row
    
        For i = 1 To rs.RecordCount
         
        xlWS.Range("A" & intLR + 1) = dtShipDate 'RS
        xlWS.Range("B" & intLR + 1) = sDelTo 'RS
        xlWS.Range("C" & intLR + 1) = strPostCode 'RS
        xlWS.Range("D" & intLR + 1) = strSL 'RS
        xlWS.Range("E" & intLR + 1) = strMFG 'RS
        xlWS.Range("F" & intLR + 1) = sDamage' String From iCondition
        
        xlWS.Cells.EntireColumn.HorizontalAlignment = xlLeft
        
        Next i
        
        xlWB.Save
        xlWB.Close
        apXL.Quit
        

    End With

[June7]

I don't think nature of sDamage was ever in question. And doesn't alter suggestions already made.

[DMT Dave]

ah ok thank June7, will go back to posts suggested, frustratingly i should be able to do this in my sleep but lost myself with it

easily done and guess I'm not the only one that does it!!

[June7]

As already noted, you needed to increment intLR within loop, like: intLR = intLR + 1

Original code should work with that edit.

Why don't you do cell formatting outside loop?

[DMT Dave]

Hi June7, i still can't get this to go to Excel!! any indication where it won't go would be great help please ?

I have only just got back to this, one of my drivers new born grandson has passed and had a lot to deal with today along with various other issues, the joys of it all!!!!!!

in my debug print, it shows the SQL statement with the correct dtShipDate and correct postcode strPC result

 sSQL = "SELECT tblEdit.ShipmentDate, tblEdit.DelTo, tblEdit.PostCode, tblEdit.SONumber, tblEdit.ItemNo " _            & "From tblEdit " _
            & "WHERE (((tblEdit.ShipmentDate)=#" & dtShipDate & "#) AND ((tblEdit.PostCode)='" & strPC & "'));"
    
    Debug.Print sSQL
    STOPPED HERE JUST TO TEXT DEBUG 
    Exit Sub
    
    
    Set rs = CurrentDb.OpenRecordset(sSQL)
    
    With rs
        Do Until rs.EOF
        
        intLR = Cells(Rows.Count, 1).End(xlUp).Row
        intLR = intLR + 1
        
            strSL = strSL & rs.Fields("SONumber")
            strMFG = strMFG & rs.Fields("ItemNo")
            dtShipDate = dtShipDate & rs.Fields("ShipmentDate")
            strPostCode = strPostCode & rs.Fields("PostCode")
            sDelTo = sDelTo & rs.Fields("DelTo")
            rs.MoveNext
        Loop
    End With
    
    With xlWS
    
    
        For i = 1 To rs.RecordCount
         
        xlWS.Range("A" & intLR + 1) = dtShipDate
        xlWS.Range("B" & intLR + 1) = sDelTo
        xlWS.Range("C" & intLR + 1) = strPostCode
        xlWS.Range("D" & intLR + 1) = strSL
        xlWS.Range("E" & intLR + 1) = strMFG
        xlWS.Range("F" & intLR + 1) = sDamage
        
        xlWS.Cells.EntireColumn.HorizontalAlignment = xlLeft
        
        Next i
        
        xlWB.Save
        xlWB.Close
        apXL.Quit
        
    End With

[pbaldy]

Did you miss this?

As already noted, you needed to increment intLR within loop, like: intLR = intLR + 1

Original code should work with that edit.

Go back to your original code and add that bit to increment the intLR variable.

[Gicu]

@Paul: he did, but put it in the wrong place plus moved the actual writing to the worksheet outside the loop....
@Dave: just add the intLR = intLR + 1 line to your original code from post 1 after the rs.MoveNext line (but before Loop).

Cheers,

[Welshgasman]

Hi June7, i still can't get this to go to Excel!! any indication where it won't go would be great help please ?

I have only just got back to this, one of my drivers new born grandson has passed and had a lot to deal with today along with various other issues, the joys of it all!!!!!!

in my debug print, it shows the SQL statement with the correct dtShipDate and correct postcode strPC result

 sSQL = "SELECT tblEdit.ShipmentDate, tblEdit.DelTo, tblEdit.PostCode, tblEdit.SONumber, tblEdit.ItemNo " _            & "From tblEdit " _
            & "WHERE (((tblEdit.ShipmentDate)=#" & dtShipDate & "#) AND ((tblEdit.PostCode)='" & strPC & "'));"
    
    Debug.Print sSQL
    STOPPED HERE JUST TO TEXT DEBUG 
    Exit Sub
    
    
    Set rs = CurrentDb.OpenRecordset(sSQL)
    
    With rs
        Do Until rs.EOF
        
        intLR = Cells(Rows.Count, 1).End(xlUp).Row
        intLR = intLR + 1
        
            strSL = strSL & rs.Fields("SONumber")
            strMFG = strMFG & rs.Fields("ItemNo")
            dtShipDate = dtShipDate & rs.Fields("ShipmentDate")
            strPostCode = strPostCode & rs.Fields("PostCode")
            sDelTo = sDelTo & rs.Fields("DelTo")
            rs.MoveNext
        Loop
    End With
    
    With xlWS
    
    
        For i = 1 To rs.RecordCount
         
        xlWS.Range("A" & intLR + 1) = dtShipDate
        xlWS.Range("B" & intLR + 1) = sDelTo
        xlWS.Range("C" & intLR + 1) = strPostCode
        xlWS.Range("D" & intLR + 1) = strSL
        xlWS.Range("E" & intLR + 1) = strMFG
        xlWS.Range("F" & intLR + 1) = sDamage
        
        xlWS.Cells.EntireColumn.HorizontalAlignment = xlLeft
        
        Next i
        
        xlWB.Save
        xlWB.Close
        apXL.Quit
        
    End With

Dave,
Try an think about what you are doing.

What good is incrementing intLR in the recordset loop?
You overwrite your variables with each record in that loop as well.
Then still set the the same row with whatever was in the last record, time and time again, in your second loop, although now that row will be whatever the record count is, nothing to do with your last row in your excel sheet.

If you walk your code and inspect your variables, simple errors like this should be obvious.

[DMT Dave]

thank you guys
@Vlad, that what i couldn't see, sorry, yes can now see it now, move through recordset before intLR prior to looping so the data is within

sorry i couldn't see that guys, again, you all do great on here helping people out so a huge thank you certainly from me...

Will update when i get a chance and come back

[DMT Dave]

Hi Guy's i still can't get the data to excel sheet, moved intLR = intLR + 1 just prior to looping records, also tried changing xlWS and xlWB from objects to Excel Worksheet and Excel Workbook declarations

 fOpen = "Condition Report.xlsx"    
    Set apXL = CreateObject("Excel.Application")
    Set xlWB = apXL.Workbooks.Open(pOpen & fOpen)
    Set xlWS = xlWB.Worksheets("Sheet1")
    apXL.Visible = False
    
    Set rs = CurrentDb.OpenRecordset("Select * From tblEdit WHERE ShipmentDate = #" & dtShipDate & "# And PostCode = '" & strPC & "'")
    
    With xlWS
        
        intLR = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Do Until rs.EOF
            
            strSL = rs.Fields("SONumber")
            strMFG = rs.Fields("ItemNo")
            dtShipDate = rs.Fields("ShipmentDate")
            sDelTo = rs.Fields("DelTo")
            strPostCode = rs.Fields("PostCode")
         
        xlWS.Cells(intLR + 1, 1) = dtShipDate
        xlWS.Cells(intLR + 1, 2) = ""
        xlWS.Cells(intLR + 1, 3) = strSL
        xlWS.Cells(intLR + 1, 4) = strMFG
        xlWS.Cells(intLR + 1, 5) = sDamage
        xlWS.Cells.EntireColumn.HorizontalAlignment = xlLeft
             
        rs.MoveNext
        
        intLR = intLR + 1
        
        Loop
        
        xlWB.Save
        xlWB.Close
        apXL.Quit
        
    End With
       
    Debug.Print strSQL
    
Set apXL = Nothing

[June7]

Could you provide the Excel file you are trying to manipulate?

You added two variables (sDelTo, strPostCode) but don't write them to cells.