Exporting query to Excel showing related months grouped

I have this query I'd lide to export to Excel and group related monthly data allowing a space between the grouped monthly data. Is this possible? The data below is a sample of data records to be grouped by date and time using the date function to grab the month value. Is it possible to create a space between the monthly groupings? How????????

May 2004
dtetmeEndProdctn
5/1/04 11:00:00 PM
5/4/04 7:10:00 AM
5/14/04 3:05:00 PM
5/2/04 8:30:00 PM
5/7/04 10:30:00 AM
5/14/04 3:00:00 AM

April 2004
dtetmeEndProdctn
4/7/04 10:35:00 AM
4/13/04 10:00:00 AM
4/21/04 7:55:00 PM
4/26/04 3:25:00 PM
5/1/04 6:59:00 AM

Thanks in advance for all you assitance on this matter.

If you sent this data to a report, then to excel, you may be able to retain
the formatting

--
Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.fmsinc.com/consulting/

"Terri" wrote in message
...
I have this query I'd lide to export to Excel and group related monthly
data allowing a space between the grouped monthly data. Is this possible?
The data below is a sample of data records to be grouped by date and time
using the date function to grab the month value. Is it possible to create a
space between the monthly groupings? How????????

May 2004
dtetmeEndProdctn
5/1/04 11:00:00 PM
5/4/04 7:10:00 AM
5/14/04 3:05:00 PM
5/2/04 8:30:00 PM
5/7/04 10:30:00 AM
5/14/04 3:00:00 AM

April 2004
dtetmeEndProdctn
4/7/04 10:35:00 AM
4/13/04 10:00:00 AM
4/21/04 7:55:00 PM
4/26/04 3:25:00 PM
5/1/04 6:59:00 AM

Thanks in advance for all you assitance on this matter.

Steve:

Are you sure? How would one do this? I have a report with similar data. I've tried to pump out the data from the report to excel. However, I keep getting an "Subscript Out Of Range" error. Can you tell me what's wrong or supply sample code for accomplishing this?

Thanks,



----- [MVP] S.Clark wrote: -----

If you sent this data to a report, then to excel, you may be able to retain
the formatting

--
Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.fmsinc.com/consulting/

"Terri" wrote in message
...
I have this query I'd lide to export to Excel and group related monthly
data allowing a space between the grouped monthly data. Is this possible?
The data below is a sample of data records to be grouped by date and time
using the date function to grab the month value. Is it possible to create a
space between the monthly groupings? How????????
May 2004
dtetmeEndProdctn
5/1/04 11:00:00 PM
5/4/04 7:10:00 AM
5/14/04 3:05:00 PM
5/2/04 8:30:00 PM
5/7/04 10:30:00 AM
5/14/04 3:00:00 AM
April 2004
dtetmeEndProdctn
4/7/04 10:35:00 AM
4/13/04 10:00:00 AM
4/21/04 7:55:00 PM
4/26/04 3:25:00 PM
5/1/04 6:59:00 AM
Thanks in advance for all you assitance on this matter.

"Terri" wrote in message
...
I have this query I'd lide to export to Excel and group related monthly data
allowing a space between the grouped monthly data. Is this possible? The data below
is a sample of data records to be grouped by date and time using the date function to
grab the month value. Is it possible to create a space between the monthly groupings?
How????????

May 2004
dtetmeEndProdctn
5/1/04 11:00:00 PM
5/4/04 7:10:00 AM
5/14/04 3:05:00 PM
5/2/04 8:30:00 PM
5/7/04 10:30:00 AM
5/14/04 3:00:00 AM

April 2004
dtetmeEndProdctn
4/7/04 10:35:00 AM
4/13/04 10:00:00 AM
4/21/04 7:55:00 PM
4/26/04 3:25:00 PM
5/1/04 6:59:00 AM

Hi Terri,

PMFBI

How comfortable are you with VBA?

I might just adapt "Copying an Entire Recordset" code from

ACC2000: Using Automation to Transfer Data to Microsoft Excel
http://support.microsoft.com/?kbid=210288

It looks like you are sorting DESC on the
"yyyymm", but then I don't understand how
5/1/04 gets in April 2004 group......

Assuming your query SQL looked like:

SELECT
dtetmeEndProdctn
FROM yourtable
ORDER BY
Format(dtetmeEndProdctn, "yyyymm") DESC,
dtetmeEndProdctn;

then the code might look something like:

Dim DB As DAO.Database, Rs As DAO.Recordset
Dim i As Integer, j As Integer
Dim RsSql As String
Dim CurrentValue As Variant
Dim CurrentField As Variant
Dim Workbook As Object
Dim xlApp As Object
Dim Sheet As Object
Dim strMoYr As String

Set DB = DBEngine.Workspaces(0).Databases(0)

RsSql = "SELECT dtetmeEndProdctn " _
& "FROM yourtable ORDER BY " _
& "Format(dtetmeEndProdctn, "yyyymm") DESC, " _
& "dtetmeEndProdctn;"


Set Rs = DB.OpenRecordset(RsSql, dbOpenDynaset)
Set xlApp = CreateObject("Excel.Application")
xlApp.workbooks.Add
Set Sheet = xlApp.activeworkbook.sheets(1)

strMoYr = ""
j = 1 'row counter
Rs.MoveFirst

' Loop through the Microsoft Access records and copy the records
' to the Microsoft Excel spreadsheet.
Do Until Rs.EOF
CurrentValue = Rs!dtetmeEndProdctn
'do we have same "yyyymm"?
If Format(CurrentValue,"yyyymm") = strMoYr Then
'have same, so write it to Excel
Sheet.cells(j, 1).Value = CurrentField
Else
'not the same
strMoYr = Format(CurrentValue,"yyyymm")
'skip a row
j = j +1
'month groupheader
Sheet.cells(j, 1).Value = Format(CurrentValue,"mmmm yyyy")
'new value for this record in next row
j = j +1
Sheet.cells(j, 1).Value = CurrentField
End If

Rs.MoveNext
j = j + 1
Loop

'** you may want to do something different here
' Print the Microsoft Excel spreadsheet.
Sheet.PrintOut
' Close workbook without saving.
xlApp.activeworkbook.saved = True
Set Sheet = Nothing
xlApp.Quit
Set xlApp = Nothing

The above code is untested, but hopefully
it gives you a good starting point.

Apologies again for butting in.

Good luck,

Gary Walter

to save xls to a hard coded filename

Set xlApp = CreateObject("Excel.Application")
Set Workbook = xlApp.Workbooks.Add
Set Sheet = Workbook.Worksheets(1)
'** instead of ***
'xlApp.workbooks.Add
'Set Sheet = xlApp.activeworkbook.sheets(1)

then end of code could be like


'Save the Workbook and Quit Excel
Workbook.SaveAs "C:\somename.xls"

Set Workbook = Nothing
Set Sheet = Nothing
xlApp.Quit
Set xlApp = Nothing