40 rapporter i egne filer

why do you want to send a report to Excel? Excel is used for calculations, not for showing reports!

Terry, I want to send it to Excel because the recepient in each department must make comments on each line and return it. This is much easier and handy in Excel than in Word

Is the report just ONE report which is grouped on department (afdeling)? If so then I think you need to change this so that one report is created for each department.

THis is just an idea.

If the report is based on a query then in the query you need a criteria for filtering a given department. You can set a criteria field in the query to "look" at a field on a form.

The criteria feld will look something like this

[forms]![YourForm]![FieldContainingDepartment]


Now you need some code in the form which loops through all departments, setting the field FieldContainingDepartment to the department.

For each department (in the loop) you first set the field and then you send the report in a mail as Excel using code something like this.

DoCmd.SendObject acSendReport, "YourReport", acFormatXLS, "WhoToGoesHere", , , "Excel Report"

where "WhoToGoesHere" is maybe a mailgroup for the department

That was was pretty much what I wanted! I have the looping working, but would like to just create a file in a specified folder instead of sending it directly as it needs to be bundled with other information...
So I guess I am just looking for a way of creating an excel file using VBA

Okay... I managed to solve it with this command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query2", "X:\Test\File.xls", True

Thanks for the input Terry!

thanks

You could actually alter the file name to the department name so that you dont go overwriting the same file.

Have a good weekend
Terry

I am doing that already. Filename is a variable. Just wanted to post that I had found a solution so you didn't waste any time on this.

And a great weekend to you from London :-)

Do you live in London?

Yes. But still using Eksperten.dk :-)