Results 1 to 12 of 12

Thread: Auto Open/Refresh Pivots/Save/Close Excel files using VB

Hybrid View

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2012
    Posts
    6

    Auto Open/Refresh Pivots/Save/Close Excel files using VB

    Hi all,
    I am new to VB - I have multiple spreadsheets which I open up weekly and refresh the pivots (connected to xl via Sql server). It very time consuming to open each one and do this. So I was wondering whether I can automate the open/refresh/save/close xl process.

    I got some codes from different sites - but since I am not a developer I am not sure on whether it is proper. Can you guys please help me with it ?

    Dim XLApp
    Dim XLWkb
    Set XLApp = CreateObject("Excel.Application")
    xlapp.visible = true
    xlapp.workbooks.open "C:\Users\x\Desktop\Diags Pivots\ExcelName.xlsx"

    set wr = xlapp.workbooks.Open("C:\Users\x\Desktop\Diags Pivots\ExcelName.xlsx")
    wr.refreshall
    xlapp.visible = true
    wr.Save
    wr.Close
    wr.Quit

    set wr = nothing
    set xlapp = nothing

    This code opens and refreshes the excel - and even saves, but does nt close/exit the excel application.

    Also, I want to simultaneouly perform this same operation on multiple xl files situated in different folders, and save each ones after each are refreshed and each xl file should close.

    Can someone please pass on the code or "optimize" the code which I have pasted above ?

    I also got this piece of code (which I did nt use, as not sure how to embed this with open etc. code sequence ) for refreshing pivots:

    Private Sub Workbook_Open()
    MsgBox Date
    Dim shtTemp As Worksheet
    Dim pvtTable As PivotTable

    For Each shtTemp In ActiveWorkbook.Worksheets
    For Each pvtTable In shtTemp.PivotTables
    pvtTable.RefreshTable
    Next
    Next
    End Sub

    Should I use this or Refreshall will do all the needed xl refreshes ?

    Looking for your response.

    Thanks!

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB

    This code opens and refreshes the excel - and even saves, but does nt close/exit the excel application.
    wr is a workbook object it has no quit method, should be xlapp.quit


    assuming you want to refresh all xlsx in folder, try like
    vb Code:
    1. Dim XLApp as object
    2. Dim wr as object
    3. Set XLApp = CreateObject("Excel.Application")
    4. xlapp.visible = true   ' not required, you do not need to see this happening
    5. mypath = "C:\Users\x\Desktop\Diags Pivots\"
    6. fname = dir(mypath & "*.xlsx")
    7. do while len(fname) > 0
    8.   set wr = xlapp.workbooks.Open(mypath & fname)
    9.   wr.refreshall
    10.   wr.Save
    11.   wr.Close
    12.   fname = dir
    13. loop
    14. xlapp.Quit
    15.  
    16. set wr = nothing
    17. set xlapp = nothing
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3
    Addicted Member sweet_dreams's Avatar
    Join Date
    Apr 2005
    Location
    Poland, Lodz
    Posts
    189

    Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB

    Hi,

    Create in new excel file new module and paste below code. Than just execute RefreshPivots procedure. Below code lists all xlsx files from folder and subfolders in current worksheet. Than it opens each file and refreshes pivot tables.
    Hope it helps.

    Code:
    Sub ListXlsx(strFolderName As String, bolIncludeSubfolders As Boolean)
    
    Dim FSO As Object
    Dim SourceFolder As Object
    Dim SubFolder As Object
    Dim FileItem As Object
    Dim nRow As Long
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder(strFolderName)
    Range("A1").Value = "Files"
    nRow = 2
        
    For Each FileItem In SourceFolder.Files
        If FileItem.Name Like "*.xlsx" Then
            Cells(nRow, 1) = FileItem.Path
            nRow = nRow + 1
        End If
    Next FileItem
    
    If bolIncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListXlsx SubFolder.Path, True
        Next SubFolder
    End If
    
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
        
    End Sub
    
    Sub RefreshSingleFile(strFilePath As String)
    
        Dim objWorkBook As Workbook
        
        Set objWorkBook = Workbooks.Open(strFilePath)
        objWorkBook.RefreshAll
        objWorkBook.Close SaveChanges:=True
    
    End Sub
    
    Sub RefreshAllFiles()
    
        Dim nNumberOfFiles As Long
        Dim n As Long
        Dim strFileName As String
        
        nNumberOfFiles = Range("A2", Range("A2").End(xlDown)).Rows.Count
        
        For n = 1 To nNumberOfFiles
            strFileName = Range("a1").Offset(n, 0)
            RefreshSingleFile (strFileName)
        Next n
    
    End Sub
    
    Sub RefreshPivots()
    
        Call ListXlsx("C:\xls_files\", True) '<<<<<< change path to your folder
        Call RefreshAllFiles
    
    End Sub
    Let me know if you have any questions.

    Regards,
    Sebastian
    using VB 2010 .NET Framework 4.0; MS Office 2010; SQL Server 2008 R2 Express Edition | Remember to mark resolved threads and rate useful posts.

  4. #4

    Thread Starter
    New Member
    Join Date
    Mar 2012
    Posts
    6

    Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB

    Hi Sebastian..Thanks a lot...

    Can you briefly tell me what exactly that code does ? (got confused with those last "sub's" {refreshall, refresh single file and refresh pivots } - why all 3 - instead cant we just do "refresh all" ?

    Also, I am right now trying to schedule the weekly excel refreshes using Windows Task scheduler - So basically I am pasting the code and saving file as ".vbs" and then executing the script using Windows Task scheduler.

    Can I save the code which you send as .vbs and schedule its execution?

    Also, I have few files that needs to be saved as an incremental number with each fiscal week - say FW1 and the next week I refresh and save as FW2, and the next i will save as FW3...and so; while few others I will just save as its current names (no save as needed) - is there a way we can incorporate that logic ?

    This code checks for excels under one folder and subfolders in it ; is there a way I can search on multiple folders ( prespecified folder ?) ??say folder A has 4 xls..folder B has 3 xls and a subfolder with 2 xls

    Thanks a lott again for your help...
    Last edited by VB_newbie82; Mar 29th, 2012 at 06:07 PM. Reason: Needed more additions

  5. #5
    Addicted Member sweet_dreams's Avatar
    Join Date
    Apr 2005
    Location
    Poland, Lodz
    Posts
    189

    Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB

    Quote Originally Posted by VB_newbie82 View Post
    Can you briefly tell me what exactly that code does ? (got confused with those last "sub's" {refreshall, refresh single file and refresh pivots }
    Code that I provided consists of 4 procedures:
    - ListXlsx - iterates through provided as an argument direcory and its subfolders and it lists in column "A" in active worksheet file paths to excel files that it found;
    - RefreshAllFiles - iterates through all values in column "A" in sheet (which are paths to excel files) and for each file it executes RefreshSingleFile;
    - RefreshSingleFile - aim of procedure is to open and refresh excel file provided as an argument;
    - RefreshPivots - executes ListXlsx and RefreshAllFiles procedures

    Quote Originally Posted by VB_newbie82 View Post
    why all 3 - instead cant we just do "refresh all" ?
    Well, it could be done in one sub. I try to divide my code and place code responsible for one thing in one procedure and responsible for thing in other procedure. It makes that I can eg. use same code in different places simply by executing specified procedure.
    Hope that clears a bit.

    Quote Originally Posted by VB_newbie82 View Post
    Can I save the code which you send as .vbs and schedule its execution?
    I don't have experience with vbs scripts and scheduling its execution. But when changed a bit to use late binding it should be ok.

    Code:
    Sub RefreshPivots2(strFolderName As String, bolIncludeSubfolders As Boolean)
    
    Dim FSO As Object
    Dim SourceFolder As Object
    Dim SubFolder As Object
    Dim FileItem As Object
    Dim oExcel As Object
    Dim oWorkBook As Object
    
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder(strFolderName)
    Set oExcel = CreateObject("Excel.Application")
        
    For Each FileItem In SourceFolder.Files
        If FileItem.Name Like "*.xlsx" Then
            Set oWorkBook = oExcel.Workbooks.Open(FileItem.Path)
            oWorkBook.RefreshAll
            oWorkBook.Save
            oWorkBook.Close
        End If
    Next FileItem
    
    If bolIncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            RefreshPivots2 SubFolder.Path, True
        Next SubFolder
    End If
    
    oExcel.Quit
    Set oWorkBook = Nothing
    Set oExcel = Nothing
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    
    End Sub

    Quote Originally Posted by VB_newbie82 View Post
    This code checks for excels under one folder and subfolders in it ; is there a way I can search on multiple folders ( prespecified folder ?) ??say folder A has 4 xls..folder B has 3 xls and a subfolder with 2 xls
    Simple way to check for files in different folders it just to execute code several times, each time for different directory.
    using VB 2010 .NET Framework 4.0; MS Office 2010; SQL Server 2008 R2 Express Edition | Remember to mark resolved threads and rate useful posts.

  6. #6

    Thread Starter
    New Member
    Join Date
    Mar 2012
    Posts
    6

    Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB

    Quote Originally Posted by sweet_dreams View Post
    Code that I provided consists of 4 procedures:
    - ListXlsx - iterates through provided as an argument direcory and its subfolders and it lists in column "A" in active worksheet file paths to excel files that it found;
    - RefreshAllFiles - iterates through all values in column "A" in sheet (which are paths to excel files) and for each file it executes RefreshSingleFile;
    - RefreshSingleFile - aim of procedure is to open and refresh excel file provided as an argument;
    - RefreshPivots - executes ListXlsx and RefreshAllFiles procedures


    Well, it could be done in one sub. I try to divide my code and place code responsible for one thing in one procedure and responsible for thing in other procedure. It makes that I can eg. use same code in different places simply by executing specified procedure.
    Hope that clears a bit.


    I don't have experience with vbs scripts and scheduling its execution. But when changed a bit to use late binding it should be ok.

    Code:
    Sub RefreshPivots2(strFolderName As String, bolIncludeSubfolders As Boolean)
    
    Dim FSO As Object
    Dim SourceFolder As Object
    Dim SubFolder As Object
    Dim FileItem As Object
    Dim oExcel As Object
    Dim oWorkBook As Object
    
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder(strFolderName)
    Set oExcel = CreateObject("Excel.Application")
        
    For Each FileItem In SourceFolder.Files
        If FileItem.Name Like "*.xlsx" Then
            Set oWorkBook = oExcel.Workbooks.Open(FileItem.Path)
            oWorkBook.RefreshAll
            oWorkBook.Save
            oWorkBook.Close
        End If
    Next FileItem
    
    If bolIncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            RefreshPivots2 SubFolder.Path, True
        Next SubFolder
    End If
    
    oExcel.Quit
    Set oWorkBook = Nothing
    Set oExcel = Nothing
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    
    End Sub


    Simple way to check for files in different folders it just to execute code several times, each time for different directory.
    Hi Sebastian...Thanks again..

    Very basic question - I want to check for the XL's in a particular folder and the sub folders within that...so somewhere in the code I need to define the directory path right ? so that, it goes to folder path, and refreshes the files within those.

    So in this piece of code, where do I define the "FOlder path" ? or have you already defined it in the code (I could nt find it )

    Thanks,
    Manu

  7. #7

    Thread Starter
    New Member
    Join Date
    Mar 2012
    Posts
    6

    Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB

    Quote Originally Posted by westconn1 View Post
    wr is a workbook object it has no quit method, should be xlapp.quit


    assuming you want to refresh all xlsx in folder, try like
    vb Code:
    1. Dim XLApp as object
    2. Dim wr as object
    3. Set XLApp = CreateObject("Excel.Application")
    4. xlapp.visible = true   ' not required, you do not need to see this happening
    5. mypath = "C:\Users\x\Desktop\Diags Pivots\"
    6. fname = dir(mypath & "*.xlsx")
    7. do while len(fname) > 0
    8.   set wr = xlapp.workbooks.Open(mypath & fname)
    9.   wr.refreshall
    10.   wr.Save
    11.   wr.Close
    12.   fname = dir
    13. loop
    14. xlapp.Quit
    15.  
    16. set wr = nothing
    17. set xlapp = nothing
    Hi Westconn1, Thanks a ton for that piece of code.

    I have few files which ends with "FW1"..and the number increments each fiscal week when I refresh the file (basically I refresh the excel and save as the next number i.e. save as "FW2"...and each time..number is incremented for few files). While for few others its just refresh and save (which the code which you send me would do).
    So is there a way to "save as " that required file name? while rest to be just saved?(can we have both this logic in the same code ?

    Also, is there a way I can call and refresh excels from different folders ? the code which you send refreshes all the excels in one folder - but is there a way I can open and refresh excels from different folders?

    I am saving the code as .vbs and then scheduling the script to run using "Windows Task scheduler"

  8. #8

    Thread Starter
    New Member
    Join Date
    Mar 2012
    Posts
    6

    Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB

    Quote Originally Posted by westconn1 View Post
    wr is a workbook object it has no quit method, should be xlapp.quit


    assuming you want to refresh all xlsx in folder, try like
    vb Code:
    1. Dim XLApp as object
    2. Dim wr as object
    3. Set XLApp = CreateObject("Excel.Application")
    4. xlapp.visible = true   ' not required, you do not need to see this happening
    5. mypath = "C:\Users\x\Desktop\Diags Pivots\"
    6. fname = dir(mypath & "*.xlsx")
    7. do while len(fname) > 0
    8.   set wr = xlapp.workbooks.Open(mypath & fname)
    9.   wr.refreshall
    10.   wr.Save
    11.   wr.Close
    12.   fname = dir
    13. loop
    14. xlapp.Quit
    15.  
    16. set wr = nothing
    17. set xlapp = nothing
    Hi Westconn1,

    The code first gives an error: VBScript compilation error - Line1 char 11; Error: Expected end of statement

    I took off that "as object" from the declaration statements - so now I have code as "dim wr" - and then the code gives an error:

    dir - type mismatch

    Can you please guide me through again ?

    Thanks a ton !

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB

    This code checks for excels under one folder and subfolders in it ; is there a way I can search on multiple folders ( prespecified folder ?) ??say folder A has 4 xls..folder B has 3 xls and a subfolder with 2 xls
    yes you can do this with the same code, if you have a list of folders, just use another for loop for each folder in the list, or using a recursive procedure for each sub folder, you can just start from a folder and process all subfolders

    So is there a way to "save as " that required file name? while rest to be just saved?(can we have both this logic in the same code ?
    you would need some list of the files to save, saveAs or some property within the file so you can tell which you want to do, you could add a customdocumentproperty to the ones you want to saveAs or someother option based on name
    Last edited by westconn1; Mar 30th, 2012 at 04:33 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB

    Sub RefreshPivots2(strFolderName As String, bolIncludeSubfolders As Boolean)
    as this procedure has parameters, you have to call it from some other procedure, the parameters are the foldername and true to include subfolders, else false
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    New Member
    Join Date
    Mar 2012
    Posts
    6

    Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB

    Quote Originally Posted by westconn1 View Post
    as this procedure has parameters, you have to call it from some other procedure, the parameters are the foldername and true to include subfolders, else false
    Also Westconn....

    If this is my folder path: ("C:\Users\x\Desktop\Diags Pivots\") - then where do I insert this path in that piece of code?

    Thanks Thanks Thanks

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB

    as you are using vbscript the vb dir method is not available to you, i had assumed you were using VBA
    you can use FSO to do the same job

    If this is my folder path: ("C:\Users\x\Desktop\Diags Pivots\") - then where do I insert this path in that piece of code?
    it was included already at line 5
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width