-
Mar 28th, 2012, 05:46 PM
#1
Thread Starter
New Member
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!
-
Mar 29th, 2012, 03:13 PM
#2
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:
Dim XLApp as object Dim wr as object Set XLApp = CreateObject("Excel.Application") xlapp.visible = true ' not required, you do not need to see this happening mypath = "C:\Users\x\Desktop\Diags Pivots\" fname = dir(mypath & "*.xlsx") do while len(fname) > 0 set wr = xlapp.workbooks.Open(mypath & fname) wr.refreshall wr.Save wr.Close fname = dir loop xlapp.Quit set wr = nothing 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
-
Mar 29th, 2012, 03:27 PM
#3
Addicted Member
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.
-
Mar 29th, 2012, 05:53 PM
#4
Thread Starter
New Member
Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB
Originally Posted by westconn1
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:
Dim XLApp as object Dim wr as object Set XLApp = CreateObject("Excel.Application") xlapp.visible = true ' not required, you do not need to see this happening mypath = "C:\Users\x\Desktop\Diags Pivots\" fname = dir(mypath & "*.xlsx") do while len(fname) > 0 set wr = xlapp.workbooks.Open(mypath & fname) wr.refreshall wr.Save wr.Close fname = dir loop xlapp.Quit set wr = nothing 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"
-
Mar 29th, 2012, 05:57 PM
#5
Thread Starter
New Member
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
-
Mar 30th, 2012, 02:02 AM
#6
Addicted Member
Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB
Originally Posted by VB_newbie82
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
Originally Posted by VB_newbie82
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.
Originally Posted by VB_newbie82
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
Originally Posted by VB_newbie82
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.
-
Mar 30th, 2012, 04:29 AM
#7
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
-
Mar 30th, 2012, 06:14 PM
#8
Thread Starter
New Member
Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB
Originally Posted by sweet_dreams
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
-
Mar 31st, 2012, 03:26 AM
#9
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
-
Apr 2nd, 2012, 01:35 PM
#10
Thread Starter
New Member
Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB
Originally Posted by westconn1
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:
Dim XLApp as object
Dim wr as object
Set XLApp = CreateObject("Excel.Application")
xlapp.visible = true ' not required, you do not need to see this happening
mypath = "C:\Users\x\Desktop\Diags Pivots\"
fname = dir(mypath & "*.xlsx")
do while len(fname) > 0
set wr = xlapp.workbooks.Open(mypath & fname)
wr.refreshall
wr.Save
wr.Close
fname = dir
loop
xlapp.Quit
set wr = nothing
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 !
-
Apr 2nd, 2012, 02:52 PM
#11
Thread Starter
New Member
Re: Auto Open/Refresh Pivots/Save/Close Excel files using VB
Originally Posted by westconn1
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
-
Apr 3rd, 2012, 04:56 AM
#12
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|