Prev: Warning off by deleting a sheet
Next: Match formula to match values in multiple columns
From: Basta1980 on 21 Apr 2010 04:47
Hi all,

To me really strange, to some of you pretty common. Every morning when I
turn my computer on, startup Excel and run the code below I get a an error
message indicating that there's something happening or wrong with the
commandline;
.CreatePivotTable TableDestination:=ActiveSheet.Range("a1"),
TableName:= _
"Online", DefaultVersion:=xlPivotTableVersion10
End With

When I stop the debugger and again run the same procedure it works. I have
no clue what might be causing this. Anybody got an idea or suggestion?

Sub Online()

'Open Sharepoint

Dim ie As Object
Set ie = CreateObject("Internetexplorer.Application")
ie.Visible = False
ie.Navigate
"https://sharepoint.vodafone.com/vfnetherlands/css1/Lists/Historisch%20overzicht%20online%20diensten/Allitemsg.aspx"

'Connect & Load database

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "OLEDB;Provider=Microsoft.Office.List.OLEDB.1.0;"
.CommandType = 5
.CommandText = Array( _

"<LIST><VIEWGUID>{22072B8A-1B0D-4040-8C79-D8C6AB376857}</VIEWGUID><LISTNAME>{8A209ECB-7C37-4451-8BDD-6D4DEC870E00}</LISTNAME><LISTWEB>https://sharepoint.vodafone.com/vfnetherlands/css1/_vti_bin</LISTWEB><LISTSUBWEB></LISTSUBWEB><RO" _
, _
"OTFOLDER>/vfnetherlands/css1/Lists/Historisch overzicht online
diensten</ROOTFOLDER></LIST>" _
)
.MaintainConnection = False
.CreatePivotTable TableDestination:=ActiveSheet.Range("a1"),
TableName:= _
"Online", DefaultVersion:=xlPivotTableVersion10
End With

'Table options
ActiveSheet.PivotTables("Online").AddFields
RowFields:=Array("Weeknummer", _
"Wie")
ActiveSheet.PivotTables("Online").PivotFields("Wie").Orientation = _
xlDataField
Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False

'Close Sharepoint

ie.Quit
Set ie = Nothing

End Sub

Thanks,

Basta
 | 
Pages: 1
Prev: Warning off by deleting a sheet
Next: Match formula to match values in multiple columns