Reports analyzed in Excel Macro. Can it be done?

[Brott]

Ok power users.
I have a form with a button. This button is set up as a Macro. This Macro is set to Analyze/Export one table in Excel then close.
No big deal right, right.

Now I would like to have all my reports do the same thing. Right now I go in and open the reports individually then analyze/export it into Excel. The file name will always remain the same name so it can keep writing over the old one.

If it has to be done via vba can someone help with that?
Access file name is ABC
Table name is XYZ
Report Name is 123

Excel file name DEF

Thanks for any assistance.

 

[Norie]

The best place to start with this would be to convert the existing macro to VBA via Tools>Macro.

That should create code that can be adapted to do what you want, which by the way isn't particularly clear, to me anyway.

 

[MyBoo]

I too, am confused over your statement, not fully understanding what you are trying to do.

It sounds like you click on a button that exports your table to a file in Excel format. I don't follow how you think it analyzes your data. Am I correct here or does this also open Excel for you... displaying your data?

The question that is not clear is do you want one button to export all of your reports/tables or do you want a button for each report/table?

Maybe if you list the code for the button and if it truely calls a macro, provide the steps in the macro also. This might get you the help you request.

 

[Brott]

Ok,
Thanks for looking at this.
Let me try an do a better job this time. When your trying to explain it the first time with added frustration you tend to miss things.

High level overview.

From a Swithboard I would like a single button to export the data from a table and multiple reports to their own excel file.

(My comment with regard to Analyze came from when you are in Access under Tools --> Office Links --> Analyze it with MS Excel)

I used Access own Action to Transfeer Spreadsheet
*******************************************
[However here is the code for that]

Private Sub ExportTableReport_Click()
On Error GoTo Err_ExportTableReport_Click

Dim stDocName As String

stDocName = "TransferSpreadsheet"
DoCmd.RunMacro stDocName

Exit_ExportTableReport_Click:
Exit Sub

Err_ExportTableReport_Click:
MsgBox Err.Description
Resume Exit_ExportTableReport_Click

End Sub

*******************************************
In the Macro Action Arguments it defines where the file will be placed.

I am simply looking to get my table and reports to an Excel file.
As I said before the file names will continue to be the same at all times.

I hope this help fill in any gaps.

 

[VicRauch]

Brott,

You have two methods available to you to do what I understand you want.
1. Within VBA, select the object you want exported into Excel format, then use the DoCmd.RunCommand acOutputToExcel. This will export the object in Excel format to the folder your database is in, then launch Excel so you can view the spreadsheet just exported.

OR
2. Again, within VBA, use the DoCmd.TransferSpreadsheet command which will allow you to name which object you want exported, name the folder and the name of the Excel file to receive the new spreadsheet file. This method will not open Excel so you can view the new spreadsheet. But you can, if you wish, open Excel yourself from the code.

HTH,

 

[MyBoo]

Ok,
Private Sub ExportTableReport_Click()
On Error GoTo Err_ExportTableReport_Click

Dim stDocName As String

stDocName = "TransferSpreadsheet"
DoCmd.RunMacro stDocName

Exit_ExportTableReport_Click:
Exit Sub

Err_ExportTableReport_Click:
MsgBox Err.Description
Resume Exit_ExportTableReport_Click

End Sub

*******************************************

If you want ONE button to save your reports in Excel format... do the following:

Create one macro for each report you want to export... we will use macro1, macro2, macro3, etc., for this example.

edit your existing code to reflect the following...

stDocName = "macro1"
DoCmd.RunMacro stDocName
stDocName = "macro2"
DoCmd.RunMacro stDocName
stDocName = "macro3"
DoCmd.RunMacro stDocName
etc, etc,

Now, when you click the button, each macro will run, exporting each report. It's not pretty, but it will do what I understand you are asking for.

 

[Brott]

Thanks to both of you!

I do appreciate it.