Sign in
 
HomeWindows Server 2012Windows Server 2008 R2Windows Server 2003LibraryForums
Windows Server TechCenter >
> 
> 
refresh all pivot tables within each file recurse

建议的答复 refresh all pivot tables within each file recurse

  • Thursday, December 15, 2011 7:11 PM
     
     
    Sign In to Vote
    0
    How can I make this script refresh ALL worksheets(pivot tables) within a given file. I would really like it to report(log file) on each file found and worksheet that it refreshed. Xcel version 2003 and PS V2.

    $ExcelApp

     

    = new-object -ComObject Excel.Application
    $ExcelApp.Visible = $false
    $ExcelApp    .DisplayAlerts = $false
    foreach($file in (get-childitem -path C:\SOP\NASalesReps *.xls -Recurse)){
    $Workbook = $ExcelApp.Workbooks.Open($file.fullname, 3, $false, 5, $null, $null, $true)$Workbook.RefreshAll()
    $Workbook.Save()
    }
    $ExcelApp

    .Quit()

    Thanks.














     

All Replies

  • Thursday, December 15, 2011 9:28 PM
     
     
    Sign In to Vote
    0

    Based on what you're saying, is it a fair assumption that your script currently works, but does not provide the logging you're looking for?

     

    G. Samuel Hays
     
  • Thursday, December 15, 2011 9:37 PM
     
     
    Sign In to Vote
    0

    It looked like it hit the first sub directory and changed the datetime stamp of the file then appeared
    to hang as it went down the tree. Not sure if I have the correct syntax for the Pivot Tbale refresh and
    yes the logging would be great to see what's it doing.

    Thanks.

     

     
  • Thursday, December 15, 2011 9:45 PM
     
      Has Code
    Sign In to Vote
    0

    (Caveat Lector!) No expert on this ... but, perhaps this will work to get through all of the files. 

     

     

    $ExcelApp = new-object -ComObject Excel.Application
$ExcelApp.Visible = $false
$ExcelApp.DisplayAlerts = $false

foreach($file in (get-childitem -path C:\SOP\NASalesReps *.xls -Recurse)) {
    $Workbook = $ExcelApp.Workbooks.Open($file.fullname, 3, $false, 5, $null, $null, $true)
    $Workbook.RefreshAll()
    $Workbook.Save()
    
    #logging stuff
    #Write-Host $Workbook.Name
    #write-host $Workbook.Path
    # do whatever you want here... $workbook | gm to see what is available
    
    
    $Workbook.Close()
    remove-variable Workbook
}

$ExcelApp.Quit()

    Hope it helps!

     

    G. Samuel Hays
     
  • Friday, December 16, 2011 2:18 PM
     
     
    Sign In to Vote
    0

    The logging helped becasue after it refreshed the first file found Under folder C:\SOP\NASalesReps
    it then started to process c:\My Documents...

    Why or how do I keep it processing from C:\SOP\NASalesReps sub folders and only those folders (Recurse)...  I now understand why it was taking so long to run.

     

    Thanks again.



     
     
  • Friday, December 16, 2011 4:07 PM
     
      Has Code
    Sign In to Vote
    0

    Interesting. I'm not sure why that would be and I'm not in a position to test right now... could you try the following:

     

    $ExcelApp = new-object -ComObject Excel.Application
$ExcelApp.Visible = $false
$ExcelApp.DisplayAlerts = $false

get-childitem -path C:\SOP\NASalesReps *.xls -Recurse | % {
    $Workbook = $ExcelApp.Workbooks.Open($file.fullname, 3, $false, 5, $null, $null, $true)
    $Workbook.RefreshAll()
    $Workbook.Save()
    
    #logging stuff
    #Write-Host $Workbook.Name
    #write-host $Workbook.Path
    # do whatever you want here... $workbook | gm to see what is available
    
    
    $Workbook.Close()
    remove-variable Workbook
}

$ExcelApp.Quit()


    G. Samuel Hays
     
  • Friday, December 16, 2011 4:20 PM
     
     
    Sign In to Vote
    0

    Exception calling "Open" with "7" argument(s): "'' could not be found.

    was the error it threw out..

    Thanks.

     
  • Monday, December 19, 2011 1:48 PM
     
     Proposed Has Code
    Sign In to Vote
    0

    Sorry - I missed a typo... try:

     

    $ExcelApp = new-object -ComObject Excel.Application
$ExcelApp.Visible = $false
$ExcelApp.DisplayAlerts = $false

get-childitem -path C:\SOP\NASalesReps *.xls -Recurse | % {
    $Workbook = $ExcelApp.Workbooks.Open($_.fullname, 3, $false, 5, $null, $null, $true)
    $Workbook.RefreshAll()
    $Workbook.Save()
    
    #logging stuff
    #Write-Host $Workbook.Name
    #write-host $Workbook.Path
    # do whatever you want here... $workbook | gm to see what is available
    
    
    $Workbook.Close()
    remove-variable Workbook
}

$ExcelApp.Quit()


    G. Samuel Hays
     
  • Friday, January 13, 2012 3:28 PM
     
     
    Sign In to Vote
    0
    When I moved this script to execute from another machine and this piece changed:

    get-childitem -path F:\SOP\NASalesReps *.xls -Recurse | % {



    I call this script from a batch file and it now goes back to looking at c:\my documents and doesn't stay in the

     F:\SOP\NASalesReps  -- sub directories...

    Help Thanks!!

     
  • Friday, January 13, 2012 3:38 PM
     
     
    Sign In to Vote
    0

    What happens if you call the script from Powershell?

    Grant Ward

    What's new in Powershell 3.0 (Technet Wiki)

    Network Live Audit - Powershell script
     
  • Friday, January 13, 2012 5:15 PM
     
     
    Sign In to Vote
    0
    It works... I have tried to set working dir with command file then run it but still seems to lose
    correct dir path to process from.

    Thanks.