Running Excel Modules from Access

[primagic]

I am trying to run a macro that is in excel from Access.

My code is below:

Function exportRewardReport()

'Declare Variables

Dim tDir As String ' Directory report template is saved in
Dim tFile As String ' Name of template file
Dim rDir As String ' Directory report is to be saved in
Dim rFile As String ' Name of report file

'Set variables

tDir = "Y:\HR Systems\Regular Reports\Regular Reports\Reward\"
tFile = "RewardTemplate.xls"
rDir = "Y:\HR Systems\Regular Reports\Regular Reports\Reward\"
'rDir = "C:\Documents and Settings\loftut\Desktop\"
rFile = "Reward Report " & MonthName(Month((DateSerial(Year(Now()), Month(Now()) - 1, Day(Now())))), True) & " " & Year(DateSerial(Year(Now()), Month(Now()) - 1, Day(Now()) - 1)) & ".xls"


'Turn of db warning messages to prevent execution of code being interrupted

DoCmd.SetWarnings False

'Copy report template

FileCopy tDir & tFile, rDir & rFile

'Export all data to report file

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryRewardRenumeration", rDir & rFile, , "Renum"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryRewardPayIncreases_Summary", rDir & rFile, , "Pay Increase - Summary"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryRewardPayIncreases_Detail", rDir & rFile, , "Pay Increase - Detail"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryRewardEqualPay_Summary", rDir & rFile, , "Equal - Summary"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryRewardEqualPay_Detail", rDir & rFile, , "Equal - Detail"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryRewardDeputisingAllowances>12", rDir & rFile, , "Dep Allow >12 mnths"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryReward%AnnualSalary", rDir & rFile, , "% FTE Salary"

MsgBox ("Report Created in Y:\HR Systems\Regular Reports\Regular Reports\Reward\ ")
' Turn db warning messages back on


'************************
'Code added to automate the formatting of the excel file using the macros contain in thed spreadsheet
'AP 12/03/2013

'Get the selected file location
    Dim strFileLocation As String
    strFileLocation = rDir & rFile
    
    'Open Microsoft Excel
    Dim xlApp As Object
    Dim ExcelRunning As Boolean
    
    'Check if Excel is running. If it is use that instance if not create a new instance.
    ExcelRunning = IsExcelRunning()
    If ExcelRunning Then
    Set xlApp = GetObject(, "Excel.Application")
    Else
    Set xlApp = CreateObject("Excel.Application")
    End If
    
    'Hide Excel and do the work in the backgroun
    xlApp.Visible = True
    
    'Open the selected file so we can delete the appropriate rows before importing the data
    Set wb = xlApp.Workbooks.Open(strFileLocation, True, False)
    
'Run the macro
    xlApp.Run ("formatDepAllow12Months")

     
          
    
    
    'Turn off alerts that will get displayed when Excel tries to save the file
    xlApp.DisplayAlerts = False
    
    
    'Save the excel file
    wb.Save
    
    'Close the file
    wb.Close
    
    'Turn back on alerts
    xlApp.DisplayAlerts = True
    
    'Quit Excel
    If Not ExcelRunning Then xlApp.Quit
    Set xlApp = Nothing


DoCmd.SetWarnings True

End Function

The code fails on the line:

xlApp.Run ("formatDepAllow12Months")

I can see the spreadsheet open but I get the error

Run time error 1004. Cannot run the macro 'formatDepAllow12Months'. The macro may not be available in this workbook or all macros are disabled'

I know the macro is there and I have changed the trust settings to enable all macros.

 

[vbajock]

Are you sure the file path is valid and you are actually openning the correct workbook?

 

[bubba100]

You might try

xlApp.Application.Run "formatDepAllow12Months"

 

[primagic]

Making the functions public seemed to work.

Thanks