The title pretty much says it all, I'm looking for a way to export this query to a specific column in excel so that I don't have to create a bunch of separate excel files.
The query itself does a calculation using dates and I want to run it for each month of the year. So in pseudo code something like:
For i = 1 to 13
Export Query with to cell i with parameters
StartDate = DateSerial(Me.txtYear, i, 1),
EndDate = DateSerial(Me.txtYear, i+1, 1)
i++
Next
Also right now the calc is dubbed As Month but I would be awesome if I could some how get it to change dynamically and have the actual month be the field header.
if you can create the query that creates your data, then something along the lines of this should help.
In the code I use to drop data to excel, I use this to write the headers:
fldCount = rs.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).value = rs.Fields(iCol - 1).Name
Next
and then this to write the data into the worksheet in A2
xlWs.Cells(2, 1).CopyFromRecordset rs