PDA

View Full Version : Solved: Excel Update Links Problem



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

lucas
06-08-2009, 02:47 PM
Maybe instead of
ActiveWorkbook.Save
ActiveWindow.Close

try:
ActiveWorkbook.close true
the true tells it to save changes.

bryVA
06-09-2009, 07:29 AM
Great thanks lucas that helps but I have an issue with this part now.

ActiveSheet.PivotTables("PivotTable9").RefreshTable

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
06-09-2009, 08:07 AM
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
06-10-2009, 07:04 AM
Great. Thanks.