Results 1 to 3 of 3
  1. #1
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47

    Running excel macro from access error

    Hi All:

    I have coding that I got from here.

    Code:
    Sub RunExcelMacro()
     Dim xl As Object
     'Step 1:  Start Excel, then open the target workbook.
     Set xl = CreateObject("Excel.Application")
     xl.Workbooks.Open ("C:\VV\Plot\SAPages\Pivot_tables\FWab.xlsm")
     'Step 2:  Make Excel visible
     xl.Visible = False
     'Step 3:  Run the target macro
     xl.Run "Sheet1.WPgraph"
     'Step 4:  Close and save the workbook, then close Excel
     xl.ActiveWorkbook.Close (True)
     xl.Quit
     'Step 5:  Memory Clean up.
     Set xl = Nothing
    I am trying to export a pivot chart as pdf and have the pdf viewed in access. I am having trouble with "step 3". Each time this program is ran, I get the '1004' Application-defined or object-defined error. Debugging takes me to step 3. I've verified that the macro in the excel table is named correctly and the macro runs without issue in excel. Yet, I continue to get '1004' application-defined or object-defined error. Any ideas what is causing the issue?



    Excel macro:

    Code:
    Sub WPgraph()
     Dim pt As PivotTable
         For Each pt In ActiveSheet.PivotTables
         pt.RefreshTable
         Next pt
     Call Workbook_Open
     End Sub
     Sub Workbook_Open()
     Application.ThisWorkbook.RefreshAll
          ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
              "C:\VIJC\TLhfmp\FWab.pdf", Quality:=xlQualityStandard, _
              IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
              True
     End Sub

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    sheets don't have macros. (Sheet1.WPgraph)
    the workbook does (wpgraph)

  3. #3
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47
    Changed the following:

    Code:
    Sub RunExcelMacro()
    Dim xl As Object
    'Step 1:  Start Excel, then open the target workbook.
    Set xl = CreateObject("Excel.Application")
    xl.Workbooks.Open ("C:\VV\Plot\SAPages\Pivot_tables\FWab.xlsm")
    'Step 2:  Make Excel visible
    xl.Visible = False
    'Step 3:  Run the target macro
    xl.Run "WPgraph"
    'Step 4:  Close and save the workbook, then close Excel
    xl.ActiveWorkbook.Close (True)
    xl.Quit
    'Step 5:  Memory Clean up.
    Set xl = Nothing
    Now getting '1004' cannot run macro. The macro may not be available in this workbook or all macros may be disabled.

    I've made sure the macros have been enabled in the workbook (even though it is saved as xlsm). But I am not sure why I get this error

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-11-2013, 04:50 PM
  2. Replies: 8
    Last Post: 06-27-2013, 11:58 AM
  3. Running excel macro's from withing Access
    By zippy483 in forum Programming
    Replies: 5
    Last Post: 03-08-2011, 11:47 AM
  4. Running excel macro from access
    By timpepu in forum Programming
    Replies: 1
    Last Post: 02-26-2010, 11:32 PM
  5. Replies: 2
    Last Post: 09-30-2009, 09:40 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums