vbs script creating pivot table

[daillest319]

i have most of my code created im just having trouble coverting this to work in my vbs script. can anyone help?

    ActiveSheet.PivotTables("Pivot Table").PivotSelect "Year[All;Total]", _
        xlDataAndLabel, True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12632256
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
        ActiveSheet.PivotTables("Pivot Table").PivotSelect "Hospital[All;Total]", _
        xlDataAndLabel, True
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12632256
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveSheet.PivotTables("Pivot Table").PivotSelect "'Column Grand Total'", _
        xlDataAndLabel, True
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12632256
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("E5").Select

 

[daillest319]

I got it to .PivotSelect to select the rows i want but the code craps out and doesnt format at all....any help would be great

	With xlApp.Workbooks("Test.xls").ActiveSheet.PivotTables("PivotTable1")
		.PivotSelect "Year[All;Total]", xlDataAndLabel
                      'THIS PART DOESNT WORK IT SPITS BACK AND ERROR                                
                                .Font.Name = "Times New Roman"
                                .Font.Size = "10"
    END WITH

 

[SkipVought]

hi,

Maybe this...

    With xlApp.Workbooks("Test.xls").ActiveSheet.PivotTables("PivotTable1")
        .PivotSelect "Year[All;Total]", xlDataAndLabel
'THIS PART DOESNT WORK IT SPITS BACK AND ERROR[b]
        With Selection[/b]
            .Font.Name = "Times New Roman"
            .Font.Size = "10"[b]
        End With[/b]
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

or maybe it should be...

    With xlApp.Workbooks("Test.xls").ActiveSheet.PivotTables("PivotTable1")
        .PivotSelect "Year[All;Total]", xlDataAndLabel
'THIS PART DOESNT WORK IT SPITS BACK AND ERROR
        With [highlight]xlApp.[/highlight]Selection
            .Font.Name = "Times New Roman"
            .Font.Size = "10"
        End With
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[daillest319]

cant believe the error was...


With xlApp.Selection



thank you so much

 

[daillest319]

how can add borders?

With xlApp.Workbooks("Test.xls").ActiveSheet.PivotTables("PivotTable1")
        .PivotSelect "Year[All;Total]", xlDataAndLabel
'THIS PART DOESNT WORK IT SPITS BACK AND ERROR
        With xlApp.Selection
            .Font.Name = "Times New Roman"
            .Font.Size = "10"
.xlEdgeBottom = 9 
				'.xlEdgeLeft = 7 
				'.xlEdgeRight = 10 
				'.xlEdgeTop = 8
        End With
    End With 


.xlEdgeBottom = 9 
				'.xlEdgeLeft = 7 
				'.xlEdgeRight = 10 
				'.xlEdgeTop = 8

 

[SkipVought]

I code VBA, so I insinctively ignore the application object: VERY necessary in VBS!!! ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[daillest319]

how can add borders?

With xlApp.Workbooks("Test.xls").ActiveSheet.PivotTables("PivotTable1")
        .PivotSelect "Year[All;Total]", xlDataAndLabel
'THIS PART DOESNT WORK IT SPITS BACK AND ERROR
        With xlApp.Selection
            .Font.Name = "Times New Roman"
            .Font.Size = "10"

'ThIS DIDNT WORK
            .xlEdgeBottom = 9 
	   .xlEdgeLeft = 7 
	   .xlEdgeRight = 10 
	   .xlEdgeTop = 8
        End With
    End With

 

[SkipVought]

example

'.xlEdgeBottom = 9 
    with .Borders(9)
'now do something with the bottom edge border properties

    end with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]