bryVA
06-08-2009, 12:29 PM
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.
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
Is there a better way to do this?
Thanks all for your help.
Bryan
I know that this code is not the best way to do this so any help would be fantastic.
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
Is there a better way to do this?
Thanks all for your help.
Bryan