Advanced Export to Excel

[chrisguk]

I have the following code running from a simple click event:

Private Sub btnWeeklyReport_Click()
On Error GoTo Do_Nothing
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "WeeklyReport", txtExportFile
    
    
    MsgBox "The tables have been successfully exported to " & txtExportFile & "."
Exit Sub
Do_Nothing:
    MsgBox "Export has failed.  An error occurred or the user terminated the operation."
    
End Sub

I am aware of the output to option but this one works as the user can manually adjust the filename and destination in and unbound field on the form.

My question is I dont want it to pick up a table but a Query instead and maybe with some simple formatting too.

How can this be achieved?

 

[vbaInet]

I am aware of the output to option but this one works as the user can manually adjust the filename and destination in and unbound field on the form.

The OutputTo command also has a destination part. If you leave this argument blank you will get a prompt to enter a file name and destination. A much better way of doing it. What is wrong with this method?

My question is I dont want it to pick up a table but a Query instead and maybe with some simple formatting too.

You can use a query in that method too.

 

[speakers_86]

bob larson has some great tools on his site. Check that out.

 

[Trevor G]

I have the following code running from a simple click event:

Private Sub btnWeeklyReport_Click()
On Error GoTo Do_Nothing
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "WeeklyReport", txtExportFile
 
 
    MsgBox "The tables have been successfully exported to " & txtExportFile & "."
Exit Sub
Do_Nothing:
    MsgBox "Export has failed.  An error occurred or the user terminated the operation."
 
End Sub

I am aware of the output to option but this one works as the user can manually adjust the filename and destination in and unbound field on the form.

My question is I dont want it to pick up a table but a Query instead and maybe with some simple formatting too.

How can this be achieved?

If you want the formatting to be done then you have to control the workbook so you would need to add code to the above to open the workbook and add your formatting as well.

What formatting do you need? Have you recorded a macro in Excel to format a sample workbook and then look at the code and it will help you then apply that within your routine to export to Excel.

As you have been given some ideas about the exportin the query I will skip that part of your question.

 

[speakers_86]

I believe bob larsons code can handle some formatting.

 

[vbaInet]

I believe bob larsons code can handle some formatting.

It would have been even more useful to include the link

http://www.btabdevelopment.com/ts/default.aspx?PageId=10

 

[speakers_86]

Or he could have referred to Aphrodite, which I am posting in the sample db forum now. That answers your last question to my too, btw.

 

[speakers_86]

Alright! I don't want you moping around so here is the link, since the mods will take some time to approve my post.

https://docs.google.com/open?id=0B-6thAUP_S0sMjUwNWJmZGUtMjJjZi00MWM0LTgxNTItN2ZhOGE3ZDdlOTU0