Run-Time Error '440' Method 'Run' of object '_Application' failed

[rajeevc]

Hi, I have a VB app that opens an Access DB to run a macro to generate data in a table and then it call an excel macro which also accesses the same DB to get the data and put it in to two pivot tables on two different sheets. The problem I have is when the VB app calls the run method for the excel macro I get the error mentioned int title Run-Time Error '440' Method 'Run' of object '_Application' failed. The code for the VB app is below and the code for the Excel Macro is below that: I am not sure but is it possible that I need to close the connection to the DB in excel macro before making another connection for the second pivot table? I should also mention that this worked before I added the second pivot table.

I am desperate for a solution any help would be greatly appreciated.

VB APP CODE BEGIN

Private Sub Form_Load()
'Create the Access Data
Dim ObjAccess As Access.Application
Dim filesys
Set filesys = CreateObject("Scripting.FileSystemObject")
If filesys.FileExists("C:\Projects\salesqtybymonth\salesqtybymonth.mdb") Then filesys.Deletefile "C:\Projects\salesqtybymonth\salesqtybymonth.mdb"
If filesys.FileExists("C:\Projects\salesqtybymonth\salesqtybymonthmaster.mdb") Then filesys.copyfile "C:\Projects\salesqtybymonth\salesqtybymonthmaster.mdb", "C:\Projects\salesqtybymonth\salesqtybymonth.mdb"
If filesys.FileExists("C:\Projects\salesqtybymonth\salesqtybymonth.xls") Then filesys.Deletefile "C:\Projects\salesqtybymonth\salesqtybymonth.xls"
Set filesys = Nothing
Set ObjAccess = New Access.Application
ObjAccess.Visible = False
strMDbFile = "C:\Projects\salesqtybymonth\salesqtybymonth.mdb"
ObjAccess.OpenCurrentDatabase "C:\Projects\salesqtybymonth\salesqtybymonth.mdb"
ObjAccess.DoCmd.RunMacro ("RUN_SALES_QTY_QUERY")
ObjAccess.Quit
'Destroy the object
Set ObjAccess = Nothing
'MAKE EXCEL SPREADSHEET
'Create the Excel Object
Dim ObjExcel As Excel.Application
Set ObjExcel = New Excel.Application
ObjExcel.DisplayAlerts = False
ObjExcel.Visible = False
strXlsFile = "C:\Projects\salesqtybymonth\salesqtybymonthmastertemplate.xlt"
Set ObjWorkbook = ObjExcel.Workbooks.Open(strXlsFile)
Set objWorksheet = ObjWorkbook.Worksheets(1)
ObjExcel.Run ("salesqtybymonth")
ObjExcel.Quit
Set ObjExcel = Nothing

VB APP CODE END

EXCEL MACRO BEGIN
Sub salesqtybymonth()

'Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "YourSheet"
Sheets("Sheet1").Select
ActiveSheet.Name = "SalesByQuantity"

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\projects\salesqtybymonth\salesqtybymonth.mdb;DefaultDir=C:\projects\salesqtybymonth;DriverId=25;FI" _
), Array("L=MS Access;MaxBufferSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT tblGetallWithPeriodName.ITM_CD, tblGetallWithPeriodName.SO_STORE_CD, tblGetallWithPeriodName.SumOfQTY, tblGetallWithPeriodName.VE_CD, tblGetallWithPeriodName.VSN, tblGetallWithPeriodName.DESCRI" _
, _
"PTION, tblGetallWithPeriodName.MISC_INV_PER_DES, tblGetallWithPeriodName.PERIOD" & Chr(13) & "" & Chr(10) & "FROM `C:\projects\salesqtybymonth\salesqtybymonth`.tblGetallWithPeriodName tblGetallWithPeriodName" & Chr(13) & "" & Chr(10) & "ORDER BY tblGetallWi" _
, "thPeriodName.VE_CD, tblGetallWithPeriodName.VSN")
.CreatePivotTable TableDestination:="SalesByQuantity!R4C1", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("VE_CD")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("VSN")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("DESCRIPTION")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("SumOfQTY"), "Sum of SumOfQTY", xlSum
With ActiveSheet.PivotTables("PivotTable2").PivotFields("PERIOD")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("SO_STORE_CD")
.Orientation = xlPageField
.Position = 1
End With
Range("A4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("VE_CD").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
Range("B4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("VSN").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
Range("C4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("DESCRIPTION").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
'ActiveSheet.Copy
Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A1").Select
ActiveCell.FormulaR1C1 = "Past Year Sales Qty by Month as of " & Now()
'Dim WS As Worksheet
'Set WS = Sheets.Add
'Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MySheet"
'Sheets.Add
Sheets("Sheet2").Select
ActiveSheet.Name = "SalesByAmount"
Range("A4").Select
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\projects\salesqtybymonth\salesqtybymonth.mdb;DefaultDir=C:\projects\salesqtybymonth;DriverId=25;FI" _
), Array("L=MS Access;MaxBufferSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT tblGetallWithPeriodNameAmt.ITM_CD, tblGetallWithPeriodNameAmt.SO_STORE_CD, tblGetallWithPeriodNameAmt.SumOfAMT, tblGetallWithPeriodNameAmt.VE_CD, tblGetallWithPeriodNameAmt.VSN, tblGetallWithPeriodNameAmt.DESCRI" _
, _
"PTION, tblGetallWithPeriodNameAmt.MISC_INV_PER_DES, tblGetallWithPeriodNameAmt.PERIOD" & Chr(13) & "" & Chr(10) & "FROM `C:\projects\salesqtybymonth\salesqtybymonth`.tblGetallWithPeriodNameAmt tblGetallWithPeriodNameAmt" & Chr(13) & "" & Chr(10) & "ORDER BY tblGetallWi" _
, "thPeriodNameAmt.VE_CD, tblGetallWithPeriodNameAmt.VSN")
.CreatePivotTable TableDestination:="SalesByAmount!R4C1", TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion10
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("VE_CD")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("VSN")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("DESCRIPTION")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("SumOfAMT"), "Sum of SumOfAMT", xlSum
With ActiveSheet.PivotTables("PivotTable3").PivotFields("PERIOD")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("SO_STORE_CD")
.Orientation = xlPageField
.Position = 1
End With
Range("A4").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields("VE_CD").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
Range("B4").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields("VSN").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
Range("C4").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields("DESCRIPTION").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
'ActiveSheet.Copy
Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A1").Select
ActiveCell.FormulaR1C1 = "Past Year Sales Amount by Month as of " & Now()

ActiveWorkbook.SaveAs Filename:="C:\projects\salesqtybymonth\salesqtybymonth.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close
End Sub

EXCEL MACRO END