Excel Macro run from access command to export

larspanky
larspanky used Ask the Experts™
on
Hello,

I have an Access Database, I export a report to Excel.  This report gets copied and pasted into another Excel spreadsheet and a macro is run.  The macro formats the report, runs totals and gathers names from the user and completes a report that gets sent out.  

They want to cut that process down to a button.  I want to create a command that will export the report to the excel macro sheet, run the macro and save a new formatted file.  Or as close as possible.   Right now I am looking for suggestions.

Thank You
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
You can export the data to an Excel file that has an Open event macro that will print it out to a pdf file automatically, for instance.

jppinto

Author

Commented:
Ok I can start there, it sounds like it will work.  

The code is the current command in the command button.  The Macro Sheet is called PayData_Summary_Co from ACCESS Macro.  

How do I change the command to export straight to the Excel Macro Sheet?  

The export report also varies in length.   How can I blank out or delete all the current data that is in the sheet.  

Thank You
Private Sub Command22_Click()
On Error GoTo Err_Command22_Click

    Dim stDocName As String

    stDocName = "ZZZ_PayData_Summary_Co"
    DoCmd.OutputTo acReport, stDocName

Exit_Command22_Click:
    Exit Sub

Open in new window

Author

Commented:
I worked out a solution.   The code below is a little dirty, but I changed the command button to export the report to a new file and call the Macro sheet.  I added a copy and paste macro to the macro file, and added another macro to save the file and close Excel bringing the user back to the Access command button.  They like it and everyone is happy.  This question is closed.  

jppinto,

I appreciate your thought, it definitely got the ball rolling.  Thank You
Private Sub ExcelMacro_Click()
'Export report and call macro file
On Error GoTo Err_ExcelMacro_Click



    Dim stDocName As String

    stDocName = "ZZZ_PayData_Summary_Co"
       DoCmd.OutputTo acReport, "ZZZ_PayData_Summary_Co", acFormatXLS, "X:\ADP\ZZZ_PayData_Summary_Co.xls"



Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open("X:\ADP\PayData_Summary_Co from ACCESS Macro.xls")
objXL.Visible = True
objXL.Application.Run "AA_PayrollReport"
Exit_ExcelMacro_Click:
    Exit Sub

Err_ExcelMacro_Click:
    MsgBox "Please Run the Next Company"
    Resume Exit_ExcelMacro_Click
    
End Sub


***********************************
Sub CopyPaste()
'
' CopyPaste Macro
' Macro recorded 2/1/2010 by User
'

'
    Workbooks.Open Filename:="X:\ADP\ZZZ_PayData_Summary_Co.xls"
    Cells.Select
    Selection.Copy
    
    Windows("PayData_Summary_Co from ACCESS Macro.xls").Activate
    Cells.Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Windows("ZZZ_PayData_Summary_Co.xls").Activate
    Cells.Select
    Selection.Copy
    Windows("PayData_Summary_Co from ACCESS Macro.xls").Activate
    Cells.Select
    ActiveSheet.Paste
    Windows("ZZZ_PayData_Summary_Co.xls").Activate
    Application.CutCopyMode = False
    ActiveWindow.Close savechanges:=False
    Windows("PayData_Summary_Co from ACCESS Macro.xls").Activate
    Cells.Select
    End Sub

*********************************
Sub SaveAs()
    ThisFile = Range("A5").Value & Format(Date, "mmdd")
    ActiveWorkbook.SaveAs Filename:="X:\Paydata_Reports\" & ThisFile
End Sub

***************************
Sub EOF()
Application.Quit
ActiveWindow.Close savechanges:=False


End Sub

Open in new window

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial