mercmannick
07-10-2009, 03:04 AM
'transfer data to Excel
intLastRow = ShDest.Cells(Rows.Count, 1).End(xlUp).Row 'find last row in column A
Range("A" & intLastRow + 1).CopyFromRecordset rst ' Copy from the recordset starting at the Row after the last row of data in column A
Have this bit code to transfer recordset to Excel , after each record how can i add a blank row.
sSQL = "SELECT [Sql Man Plan].[Roll No], [Sql Man Plan].Customer, [Sql Man Plan].[Wo Ref] AS WO, BOMm.[Cw No], BOMm.Type, BOMm.[Part No], BOMm.Drawing, BOMm.[quantity required] AS [Per Roll]" _
& " FROM [Sql Man Plan] INNER JOIN BOMm ON [Sql Man Plan].[Roll No] = BOMm.[roll no]" _
& " WHERE (((BOMm.[roll no])='" & ShDest.Range("O1").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O2").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O3").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O4").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O5").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O6").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O7").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O8").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O9").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O10").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O11").Value & "'));"
Many Thanks
Nick
intLastRow = ShDest.Cells(Rows.Count, 1).End(xlUp).Row 'find last row in column A
Range("A" & intLastRow + 1).CopyFromRecordset rst ' Copy from the recordset starting at the Row after the last row of data in column A
Have this bit code to transfer recordset to Excel , after each record how can i add a blank row.
sSQL = "SELECT [Sql Man Plan].[Roll No], [Sql Man Plan].Customer, [Sql Man Plan].[Wo Ref] AS WO, BOMm.[Cw No], BOMm.Type, BOMm.[Part No], BOMm.Drawing, BOMm.[quantity required] AS [Per Roll]" _
& " FROM [Sql Man Plan] INNER JOIN BOMm ON [Sql Man Plan].[Roll No] = BOMm.[roll no]" _
& " WHERE (((BOMm.[roll no])='" & ShDest.Range("O1").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O2").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O3").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O4").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O5").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O6").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O7").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O8").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O9").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O10").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O11").Value & "'));"
Many Thanks
Nick