Error 400

[Pieter]

Below my script that is returning an Error 400. Any suggestions on what may be the cause? ***** used to blank out sensitive info.


Sub Refresh()


'clear the area

'Worksheets("SageSalesData").Range("A1").CurrentRegion.Clear


'create connection

'Dim conn As ADODB.Connection

'Dim rs As ADODB.Recordset

'Dim Cmd1 As ADODB.Command


'Set conn = CreateObject("ADODB.Connection")

'conn.Open "Provider=SQLOLEDB;Data Source= *****sage;Initial Catalog=*****;User Id=******;Password=******;"


'Set Cmd1 = New ADODB.Command


'Call SP with parameters

'Cmd1.ActiveConnection = conn

'Cmd1.CommandText = "dbo.sales_by_territory_sp"

'Cmd1.CommandType = adCmdStoredProc

'Cmd1.Parameters.Refresh

'Cmd1.Parameters("@PeriodDate").Value = Worksheets("Pivot").Range("E1")

'Cmd1.Parameters("@CurrFinYearStart").Value = Worksheets("Pivot").Range("E2")

'Cmd1.Parameters("@PrevFinYearStart").Value = Worksheets("Pivot").Range("J1")

'Cmd1.Parameters("@PrevFinYearEnd").Value = Worksheets("Pivot").Range("J2")


'Set rs = Cmd1.Execute()


'Put data onto spreadsheet

'Worksheets("SageSalesData").Range("A1").CopyFromRecordset rs

'Set rs = Nothing


'Refresh pivot table

Dim pt As PivotTable

Set pt = Worksheets("Pivot").PivotTables("SalesByTerritory")

pt.RefreshTable


'Add header

Worksheets("Pivot").Range("A9").Value = "Region"

Worksheets("Pivot").Range("B9").Value = "Territory"

Worksheets("Pivot").Range("C9").Value = "Customer"

Worksheets("Pivot").Range("D9").Value = "Customer Details"

Worksheets("Pivot").Range("E9").Value = "Sam Qty"

Worksheets("Pivot").Range("F9").Value = "Qty"

Worksheets("Pivot").Range("G9").Value = "Value"

Worksheets("Pivot").Range("H9").Value = "YTD Sam Qty"

Worksheets("Pivot").Range("I9").Value = "YTD Qty"

Worksheets("Pivot").Range("J9").Value = "YTD Value"

Worksheets("Pivot").Range("K9").Value = "'Previous YTD Sample Qty"

Worksheets("Pivot").Range("L9").Value = "'Previous YTD Qty"

Worksheets("Pivot").Range("M9").Value = "'Previous YTD Value"

Worksheets("Pivot").Range("N9").Value = "'Previous Total Sam Qty"

Worksheets("Pivot").Range("O9").Value = "'Previous Total Qty"

Worksheets("Pivot").Range("P9").Value = "'Previous Total Value"

Worksheets("Pivot").Range("A99").Select

Worksheets("Pivot").Range("A99").WrapText = True

Worksheets("Pivot").Columns("D:D").ColumnWidth = 60

Worksheets("Pivot").Columns("E:E").ColumnWidth = 10

Worksheets("Pivot").Columns("F:F").ColumnWidth = 10

Worksheets("Pivot").Columns("G:G").ColumnWidth = 14

Worksheets("Pivot").Columns("H:H").ColumnWidth = 10

Worksheets("Pivot").Columns("I:I").ColumnWidth = 10

Worksheets("Pivot").Columns("J:J").ColumnWidth = 14

Worksheets("Pivot").Columns("K:K").ColumnWidth = 10

Worksheets("Pivot").Columns("L:L").ColumnWidth = 10

Worksheets("Pivot").Columns("M:M").ColumnWidth = 14

Worksheets("Pivot").Columns("N:N").ColumnWidth = 10

Worksheets("Pivot").Columns("O:O").ColumnWidth = 10

Worksheets("Pivot").Columns("P").ColumnWidth = 14


End Sub


Private Sub Worksheet_Activate()


End Sub


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


End Sub


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)


End Sub


Private Sub Worksheet_Calculate()


End Sub


Private Sub Worksheet_Change(ByVal Target As Range)


End Sub


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)


End Sub


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)


End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub

 

[Hui]

Try changing the line

pt.RefreshTable

to

pt.PivotCache.Refresh


and delete all the unused Subroutines at the bottom, below

[pre]

Worksheets("Pivot").Columns("P:P").ColumnWidth = 14
End Sub

[/pre]