Runtime Error 1004 PLEASE HELP

[jamesgarf]

Hi All,
So I have code that seems to execute pretty well but when it gets to calling the macro, I am running into an 1004 Error: "Cannot Run the macro 'FilePath!MacroName'. The macro may not be available in this workbook or all macros may be disabled. I have no idea why I am getting this error...I have enabled macros in both Excel and Access. Basically the macro runs a pivot in the excel sheet that was just created. I will highlight the error in the code according to Access.
Thanks!

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

[June7]

Try:

xlApp.Application.Run

Review
http://www.ehow.com/how_4595711_run-...el-access.html

[jamesgarf]

hi June, thanks for your response...I switched the code to xlApp.Application.Run but unfortunately got the same response :[

[June7]

This is something I never tried. Want to provide files for analysis? Follow instructions at bottom of my post.

[John_G]

Hi -

I don't do this with Excel, so this is just a suggestion.

Your wkb. object refers to your newly created Excel file, but you are trying to run a macro in another unopened Excel file; Access maybe can't deal with that.

I do the same type of thing, but with MS Word, so try this:

Create an Excel template file, containing the required macro.
Then, in MS Access,
- Open the template file
- Do an immediate Save As, using your new file name
- Then fill in the data, and the macro should be accessible with xlApp.Run macroname.

That's the syntax I use for MS Access - MS Word.

HTH

John

[jamesgarf]

hmm, I tried doing what you said John but I am just so new to access and VBA that is probably where the problem lies. Let me try and approach it from a different angle by explaining what the macro is and maybe you can help me that way.

All the macro does is create a pivot table of the new information. I couldnt find anything on how to code a pivot into the new excel sheet via Access' VBA

[June7]

The macro is not in the workbook with the data you want to pivot? How does the macro know the new workbook?

Access VBA code could manipulate the data to a 'pivoted' arrangement then export. This could be very complicated VBA and might need to write data to a table but it can be done.

Your options appear to be:
1. suggestion by jamesgarf to use template workbook
2. Access VBA to rearrange the data

If item 2 can be accomplished, might eliminate need for export. Build Access report based on the rearranged data. I do something like that. Review http://forums.aspfree.com/microsoft-...ry-322123.html

[jamesgarf]

okay got it, I am going to go the template route. If I have the template with the macro in it saved as say 'MacroTemplate' in a public file, how would I code? Something like this?

On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.application")
objExcel.Visible = bDev
objExcel.WindowState = wdWindowStateMaximize
End If

Set objExcelApp = objExcel.Application

sExcelTemplate = sExcelTemplatesPath & sExcelTemplate
objExcelApp.Workbooks.Add Template:=sExcelTemplate

Set objExcelBk = objExcelApp.ActiveWorkbook
Set objExcelSht = objExcelBk.Worksheets(1)
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%"
            
            
            xlApp.Application.Run "C:\Users\jg007894\Desktop\PERSONAL.xlsb!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

[June7]

Maybe. Did you try it?

Alternatively, save a renamed copy of template workbook first (this should be fairly simple with CopyFile method of FileSystemObject):

Dim FSO As FileSystemObject
FSO.CopyFile sourceFile, destinationPath & newFilename

Then have your code connect to the new workbook.