Solved: Excel Update Links Problem

[bryVA]

I am trying to have excel automatically update my links in a report that uses multiple other files that have pivot tables and I have coded in the following but I seem to have issues with it coming up with a run-time error 1004 Document not saves.

I know that this code is not the best way to do this so any help would be fantastic.

[vba]Sub UpdateVORAwaitingAuthorizationReportPivotTables()
'
' UpdateVORAwaitingAuthorizationReportPivotTables Macro
'
'VOR-AwaitingAuthorizationReport-Flash-SLRO-All-All-PivotTable.xls
Sheets("Sheet1").Select
Range("A1").Select
Workbooks.Open Filename:= _
"\\Vbaslcnas\Shared\Shire\SaltLake\ServiceCenter\Reports\VOR-AwaitingAuthorizationReport-Flash-SLRO-All-All-PivotTable.xls" _
, UpdateLinks:=0
Sheets("AwatingAuthorizationSummaryPT").Select
Workbooks.Open Filename:= _
"\\Vbaslcnas\shared\Shire\SaltLake\ServiceCenter\SourceData\VOR\VOR-AwaitingAuthorizationReport-Flash-SLRO-All-All.xls"
ActiveWorkbook.Save
ActiveWindow.Close
ActiveSheet.PivotTables("PivotTable2").RefreshTable
Range("A3").Select
Selection.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlTopToBottom
ActiveWorkbook.Save
ActiveWindow.Close
'VOR-AwaitingAuthorizationReport-Flash-SLRO-All-GWOT-PivotTable.xls
Workbooks.Open Filename:= _
"Vbaslcnas\Shared\Shire\SaltLake\ServiceCenter\Reports\VOR-AwaitingAuthorizationReport-Flash-SLRO-All-GWOT-PivotTable.xls" _
, UpdateLinks:=0
Sheets("AwatingAuthorizationSummaryPT").Select
Workbooks.Open Filename:= _
"\\Vbaslcnas\shared\Shire\SaltLake\ServiceCenter\SourceData\VOR\VOR-AwaitingAuthorizationReport-Flash-SLRO-All-GWOT.xls"
ActiveWorkbook.Save
ActiveWindow.Close
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Range("A3").Select
Selection.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlTopToBottom
ActiveWorkbook.Save
ActiveWindow.Close
'VOR-AwaitingAuthorizationReport-Flash-SLRO-All-PLCP-PivotTable.xls
Workbooks.Open Filename:= _
"\\Vbaslcnas\Shared\Shire\SaltLake\ServiceCenter\Reports\VOR-AwaitingAuthorizationReport-Flash-SLRO-All-PLCP-PivotTable.xls" _
, UpdateLinks:=0
Sheets("AwatingAuthorizationSummaryPT").Select
Workbooks.Open Filename:= _
"\\Vbaslcnas\shared\Shire\SaltLake\ServiceCenter\SourceData\VOR\VOR-AwaitingAuthorizationReport-Flash-SLRO-All-PLCP.xls"
ActiveWorkbook.Save
ActiveWindow.Close
ActiveSheet.PivotTables("PivotTable9").RefreshTable
Range("A3").Select
Selection.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlTopToBottom
ActiveWorkbook.Save
ActiveWindow.Close
'VOR-AwaitingAuthorizationReport-Flash-SLRO-RatingEPs-All-PivotTable.xls
Workbooks.Open Filename:= _
"\\Vbaslcnas\Shared\Shire\SaltLake\ServiceCenter\Reports\VOR-AwaitingAuthorizationReport-Flash-SLRO-RatingEPs-All-PivotTable.xls" _
, UpdateLinks:=0
Sheets("Summary-PT").Select
Workbooks.Open Filename:= _
"\\Vbaslcnas\shared\Shire\SaltLake\ServiceCenter\SourceData\VOR\VOR-AwaitingAuthorizationReport-Flash-SLRO-RatingEPs-All.xls"
ActiveWorkbook.Save
ActiveWindow.Close
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Range("A6").Select
Selection.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlTopToBottom
ActiveWorkbook.Save
ActiveWindow.Close
End Sub[/vba]

Is there a better way to do this?

Thanks all for your help.

Bryan

[lucas]

Maybe instead of
[VBA]ActiveWorkbook.Save
ActiveWindow.Close
[/VBA]
try:
[VBA]ActiveWorkbook.close true [/VBA]
the true tells it to save changes.

[bryVA]

Great thanks lucas that helps but I have an issue with this part now.

[VBA]ActiveSheet.PivotTables("PivotTable9").RefreshTable [/VBA]

Any Ideas as to why this is the case. Is it having a hard time with the activesheet? I know that there is a Pivot Table 9 in this file. Why would it give me this problem. It gives me "Unable to get the Pivot Tables property of the worksheet class. What am I doing wrong?

Thanks all for your help,

Bryan

[lucas]

I know that there is a pivot table 9 in this file

But is it the activesheet? Qualify your statement directly to the sheet with pivot table 9 maybe?

[bryVA]

Great. Thanks.