Hi All,
So I have some code in Access that creates an excel spreadsheet via a table in excel and then is running a macro from a static file. All the information populates but when the Macro is called, I keep erroring out with 1004: "Cannot run the macro "FilePath!PivotName'. The Macro may not be available in this workbook or all macros may be disabled."

Im stumped because made macros accessible and have the workbook that contains the macro open. Please help me out here. Here is the full code. I will highlight the trouble / debug error 


Private Sub port_2_Click()
'On Error GoTo Err_port_2_Click
Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim xlApp As Object
    Dim wkb As Object
    Dim rng As Object
    Dim strExcelFile As String
    Dim strTable As String
    Dim SQLname As String
    Dim iCol As Integer
    Dim rowsToReturn As Integer
    Dim objSheet As Object
    Dim tblrst As DAO.Recordset
     
    DoCmd.SetWarnings False
    
    Set db = CurrentDb '<<<Connect to this currently open database
    
    '       Clear out old sales file

        SQLname = "DELETE [Temp: Detail].* AS Temp " & _
                    "FROM [Temp: Detail];"

        DoCmd.RunSQL SQLname

    

        'Update With new Sales data

        SQLname = "INSERT INTO [Temp: Detail] ( Sales_Name, Sales_Rep, " & _
                "Customer, MasterCustomer, TotalCustomerSales, TotalCost, TotalGP, TotalGM ) " & _
                "SELECT File.Sales_Name, File.Rep_Copy, File.Total_Customer, File.MasterCustomer, File.Total_Sales, File.Total_Cost, File.Total_GP, File.Total_GM " & _
                "FROM File;"

        DoCmd.RunSQL SQLname
        
'connect to Salesman table
    Set tblrst = db.OpenRecordset("Salesmen")
     
    tblrst.MoveFirst '<<<move to the first record of primary table
     
    Do Until tblrst.EOF '<<<Loop commands until end of recordset reached
    
         'SQL statement used to query chosen records in data table
         
        strTable = "SELECT [Temp: Detail].* FROM [Temp: Detail] INNER JOIN Salesmen " _
        & "ON [Temp: Detail].Sales_Rep = Salesmen.Sales_Numb " _
        & "WHERE (Salesmen.Sales_Numb = " & tblrst!sales_Numb & ");"       '^<<<You must use a Bang (!) between tblrst & field name to get the field's value
         
'<<<Creates new excel file named for current primary table's field value
        
        strExcelFile = "\\GSCNAS04.hsi.hughessupply.com\wccs_public$\082 Sales\Oracle Back Up\Test Files\" & tblrst!sales_Numb & "_summary_09_2012_" & tblrst!Salesperson & "DailySales" & "_"

        
        Set rst = db.OpenRecordset(strTable) '<<<Open query results from data table for primary table's current field value
         
         'get number of records in recordset
        If rst.EOF = False And rst.BOF = False Then
         
        rst.MoveLast '<<<Required to get total number of records in recordset
        rowsToReturn = rst.RecordCount
        rst.MoveFirst '<<<Move back to start of recordset
         '^ if MoveFirst is not used then only last record will be gathered

         
        
            If rowsToReturn <= rst.RecordCount Then '<<<Do as long as there are records to work
        
             'set reference to Excel to make Excel visible
            Set xlApp = CreateObject("Excel.Application")
            xlApp.Application.Visible = False '<<<Make Excel invisible to user
            
             'set references to workbook and worksheet
            Set wkb = xlApp.Workbooks.Add '<<<Create new workbook
            Set objSheet = xlApp.ActiveWorkbook.Sheets(1) '<<<Add worksheet to workbook
             
             'write column names to the first worksheet row
            For iCol = 0 To rst.Fields.Count - 1
                objSheet.Cells(1, iCol + 1).Value = rst.Fields(iCol).Name
            Next
             
             'specify cell range to receive data
            Set rng = objSheet.Cells(2, 1)
            
             'copy specified number of records to worksheet
            rng.CopyFromRecordset rst, rowsToReturn
            
             'autofit columns to make data fit
            objSheet.Columns.AutoFit
            
             'bold column headers
            objSheet.Range("A1:H1").Font.Bold = True
            
             'sum columns

            
            'Formatting the rows to show $ and %
            
            objSheet.Range("E:E").NumberFormat = "$#,##0.00"
            objSheet.Range("F:F").NumberFormat = "$#,##0.00"
            objSheet.Range("G:G").NumberFormat = "$#,##0.00"
            objSheet.Range("H:H").NumberFormat = "0.00%"
            
                objSheet.Visible = True

        'Run the target macro
        wkb.Application.Run "\\GSCNAS04.hsi.hughessupply.com\wccs_public$\082 Sales\Oracle Back Up\MacroBook.xlsm!Pivot"

 
             
             'close the workbook
            wkb.SaveAs FileName:=strExcelFile '<<<Save as employee's name
            wkb.Close '<<<Close workbook because we are done with it
             
             'quit excel and release object variables
            Set objSheet = Nothing
            Set wkb = Nothing
            xlApp.Quit
            Set xlApp = Nothing
        End If
       End If
        tblrst.MoveNext '<<<Move to next Salesman
        
    Loop
     
DoCmd.SetWarnings True

    MsgBox "Finished creating Daily Sales reports!"
     
     'close database connection
    tblrst.Close
    Set tblrst = Nothing
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing


Exit_port_2_Click:
    Exit Sub

Err_port_2_Click:
    MsgBox Err.Description
    Resume Exit_port_2_Click
    
End Sub