Excel Export per unique field to separate workbooks

[Fuzz_cozens]

HiI have a query which I would like to export to excel 2010 and would like separate files saved using a unique field called brokerage. The code below exports the query however does not export separate workbooks as I am missing something perhaps the OutputTo function.Any help would be greatly appreciated



Private Sub Commission_Excel_Click()

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim MyFileName As String

Dim temp As String

Dim mypath As String



mypath = "C:\Users\Richard\Desktop\1Life Broker Services\Automatic Reports\Commission Excels\Commission Excel - "

Set db = CurrentDb()

Set rs = db.OpenRecordset("SELECT distinct FROM [Commission Statement - 1Life Broker Services]", dbOpenSnapshot)

Do While Not rs.EOF

temp = rs("brokerage")

MyFileName = rs("Brokerage") & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Commission Statement - 1Life Broker Services", "C:\Users\Richard\Desktop\1Life Broker Services\Automatic Reports\Commission Excels\Commission Excel - " & Format(Date, "dd/mm/yyyy") & ".xls", True



DoEvents

rs.MoveNext



Loop



rs.Close

Set rs = Nothing

Set db = Nothing

End sub

[June7]

If you want to export to separate workbooks then need to change the name of the workbook for each export.

You are not using the MyPath and MyFileName variables to construct the Excel file name.

What is the temp variable for?

Why does your posted code show & ?

[Fuzz_cozens]

I basically copied the code from a working code which exports a report to pdf per unique field in this case brokerage so i thought would be a minor tweak to export to excel in the same way.

I have never used the spreadsheet code before so am a little lost and so need it to sabe a few hours every week as i have to manually split 1 spreadsheet into 92 workbooks.

[June7]

So did you do the edits as suggested? Does code work now?

[Fuzz_cozens]

Not working yet. Still battling with the code and searching the web. Maybe a little out my depth

[June7]

I think all you need to do is:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Commission Statement - 1Life Broker Services", MyPath & "\" & MyFileName

[Fuzz_cozens]

Thanks very much. Will try shortly. Richard

[Fuzz_cozens]

June7, you are an absolute star. Thanks very much and its working perfectly

I am now going to fiddle around with formatting the excel output in terms of bold, auto centre and shading. I hope I can shout if I get stuck.

Thanks again

Rich

[June7]

You want code in Access to perform those formatting actions in the Excel?

That requires very different code that opens Excel and workbook objects in VBA. Gets rather complicated. Numerous threads have been devoted to the topic.

[Fuzz_cozens]

All I want is for the exported excel file for the top row to be bold and shaded grey and the columns to be auto-centred. Is that a big ask?

[June7]

Doesn't matter how little or how much formatting you want, the method is the same.

[Fuzz_cozens]

Thanks. Will check out options to see if i can do it. Thanks again.

[Fuzz_cozens]

[QUOTE=Fuzz_cozens;237414]June7, you are an absolute star. Thanks very much and its working perfectly

June7, I just noticed that once I execute this code the excel is exported separately into the correct file name by"Brokerage" however in the workbooks is all of the data and not just the data of the respective "brokerage". Any ideas on this?

[June7]

Need to apply filter to the query. That can be complicated. It would be easier to open and export a filtered report.

I just noticed the Recordset sql does not specify field(s) to retrieve. I am surprised it works.