Challenging Excel Export

[Engelking]

I wonder if anyone can point me in the right direction. I want to export an Access report grouped by 49 different categories with Page breaks at each category. The hard part is I want each Category to go to a different worksheet in the same Excel Workbook. The users want it in excel to work with and I have to cut and paste the info 49 times to get each category to be a separate worksheet. I think this can only be accomplished with code and possibly loop through each category in the recordset, then send it to excel, but I'm not that good with code. Thanks.

 

[GaryC]

I used something like this - but depends on a number of things which is best approach.

Set appXL = CreateObject "Excel.Application.8")

appXL.Workbooks.Open xlfilename, 0, False

appXL.Visible = True

'Activate the required sheet e.g. Sheet2
appXL.Sheets(2).Activate

Set db = CurrentDb

Set ts = db.OpenRecordset("myrecordset", DB_OPEN_SNAPSHOT)

ts.MoveFirst
Do Until ts.EOF
With appXL.ActiveSheet.Range("a2:a65000")
appXL.Worksheets(2).Cells(row_no, Column_no).Value = ts![Forecast Date]
End With
ts.MoveNext
Loop

' you will need to loop through all sheets all fields - so you may have a complex set of nested loops !!

appXL.ActiveWorkbook.Save
appXL.Application.Quit


'Hope that gives you a clue !

p.s.
Hope you can sort out the formatting - this box is not wide enough!!


[This message has been edited by GaryC (edited 05-23-2001).]

 

[Engelking]

Thanks, I will not be able to work on this for a couple of days. I will let you know results. Bill