asked on

Excel Macro run from access command to export

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

ASKER CERTIFIED SOLUTION

jppinto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

larspanky

ASKER

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

larspanky

ASKER

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

larspanky

ASKER

Thanks