So I thought I was getting advanced here and writing my own procedure to export a recordset to Excel. But it seems that I am only building what would be the query string to generate a query that would house the data that I want to export.
However, I am building the query string with the actual data, so it is more of a CSV list that I want to export, oh I am running in circles here!
What I am wanting to do, and I hope I am not far off from it - is to populate row 1 with header information - check that is easy for me to do - then starting in row 2 cell A start populating the data from my recordset, and after writing each variable, then move over one column, so it would write like such
A2, B2, C2, D2, E2, etc etc
This is the syntax I had, but obviously I am screwing the pooch here. Can someone get me on the right track of exporting this recordset to Excel instead of building a long CSV String with it?
Code:
strB = locale & ", "
With rsSub
If Not .EOF And Not .BOF Then
.MoveLast
.MoveFirst
Do While Not .EOF
purchaseItem = .Fields(1).Value
itemDesc = .Fields(2).Value
numpurchase = .Fields(3).Value
If sConcat <> "" Then
sConcat = sConcat & ", " & purchaseItem & ", " & itemDesc & ", " & numpurchase & ", "
Else
sConcat = purchaseItem & ", " & itemDesc & ", " & numpurchase
End If
.MoveNext
Loop
End If
End With
rsSub.Close
strB = strB & Left(sConcat, Len(sConcat) - 1)