Export Query to Excel 2010 Per unique field

[the_fuzz]

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 worksheets 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 [Brokerage] 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

 

[vbaInet]

    [COLOR="blue"]Const [/COLOR]mypath As String = "C:\Users\Richard\Desktop\1Life Broker Services\Automatic Reports\Commission Excels\Commission Excel - "
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Commission Statement - 1Life Broker Services", [COLOR="blue"]mypath [/COLOR]& Format(Date, "dd[COLOR="red"]/[/COLOR]mm[COLOR="Red"]/[/COLOR]yyyy") & ".xls", True, "[COLOR="blue"]SheetName[/COLOR][COLOR="Red"]![/COLOR]"

You include the sheet name in the Range parameter of the method. I've highlighted some important/suggested parts (scroll to the end).

Plus I'm sure your spreadsheet doesn't contain a slash "/"

 

[the_fuzz]

Thanks VBAinet. I will try this today and let you know

 

[vbaInet]

Oki dok!

 

[the_fuzz]

Hi

Thanks for the ideas and here is the current situation:

The file is exported successfully however just a few hiccups:

Firstly the query only exported 1 excel file to the folder where there should be about 40 different files as I wanted a separate workbook for each "Brokerage" which it is not doing.

Secondly, the file format is XLS. Is it possible to export to XLSX. I am using Access and Excel 2010 if this matters at all.

Thirdly, Is it at all possible to format the font, bold the header which is the 1st row and even shade the 1st row light grey? Also to autofit and centre the columns in the output excel file?

Lastly, I noticed the order of the fields is the same as in the query design but not the same when I run the query as I have moved certain fields. Is it possible to export using the sequence of the fileds I have after running the query as opposed to the fileds in query designer.

Thanks again for all of you help so far.

Richard

 

[vbaInet]

You want to look here:

http://www.btabdevelopment.com/ts/exportformrecordset

There are lots of different examples to follow.

 

[the_fuzz]

Cool. Thanks pal. will do some reading and testing. Thanks

 

[vbaInet]

No worries!

Look to the left, you'll see a section with the other examples.

 

[the_fuzz]

I cannot seem to find an example if how to export using a unique field so that during the export each "brokerage" receives their own workbook named after their brokerage. Or am I blind

 

[vbaInet]

Your objective is two parts:
1. Transfer data from an Access table/query to a Spreadsheet.
2. For each brokerage do 1.

If you go back to step 1, modify your code so that it saves the workbook and closes it. Get that working then move to step 2. Unless you completely know what you're doing, you can't do both at once.

 

[the_fuzz]

Thanks. I understand now. Does not seem as simple as when exporting a report to pdf which I have got working 100%

 

[vbaInet]

By the way, you OP is slightly different from your other post. Do you actually want to save to different workbooks or save to separate worksheets within one workbook?

 

[the_fuzz]

Definitely separate workbooks so that each brokerage receives a separate workbook.

 

[vbaInet]

Alright, not what your original post said.

See how you get on and let me know.

 

[the_fuzz]

Sorry about that. I see i said separate worksheets and not workbooks.

 

[vbaInet]

That's not a problem. Just let me know how it's going.