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