?
Solved

Excel: Auto open, refresh pivot, save and close

Posted on 2011-10-11
16
Medium Priority
?
2,281 Views
Last Modified: 2012-05-12
I have dozens of Excel Pivot Table spreadsheets that are all linked to SQL views/tables.  The SQL data is updated daily in most cases via automated SSIS packages.  Once the data in SQL (SQL2008R2 by the way) has been updated, I then need to refresh my pivot table and distribute via FTP server.  I know I can set the pivot table to auto-refresh on opening, but is there a relatively straight fwd way to automate the full process?  I don't want to set the pivot table to auto-refresh b/c when the user opens the file I don't want it to try to refresh.  I know they can just ignore the error but it's kind of a pain.  Also, I don't want to have to manually open and save the Excel file.

In the past, I have written a auto-open macro in a seperate excel workbook that opens the target Excel workbook, refreshs, saves and closes both the target and the macro conatining workbook.  Then, as the last step of the SQL SSIS package I simply open the macro workbook.  This does work, but tends to be a bit finicky.  At times I have several sessions of Excel open at the same time and then the quit command no longer works and eventually the whole process fails untill I reboot.

Got any better solutions?  I'm using Excel 2007 typically, but sometimes 2003 as well for some users.

THANK YOU!!!

0
Comment
Question by:gmasterv
  • 8
  • 5
  • 3
16 Comments
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 36951593
Yes. You can do it from a vbscript file.
Save a file as a .vbs file with code something like this in it.
You can run it as a scheduled task in Windows too if you like.
Set objExcel = CreateObject("Excel.Application")

Set objWB = objExcel.Workbooks.Open "C:\path\filename.xls"
objExcel.Visible = True

objWB.MacroName

objWB.Close
objExcel.Quit

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36951626
Sorry, slight modifications (tested and working now)
Set objExcel = CreateObject("Excel.Application")

Set objWB = objExcel.Workbooks.Open("C:\path\filename.xls")
objExcel.Visible = True
objExcel.Run "MacroName"

objWB.Close False 'False to not save
objExcel.Quit

Open in new window

0
 

Author Comment

by:gmasterv
ID: 36951642
I knew there had to be an easier way!!  So I create my Excel pivot table document as always, create refresh macro (lets call it "myrefreshmacro" )and save it in the same document.  Then just replace

objWB.MacroName

with

objWB.myrefreshmacro

and save it as a vbs file.  Then I can run the vbs from SQL, Windows, cmd line, etc?

thanks again!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36951867
That's correct. Except use the code from the second post. The first one doesn't work right.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36951876
If you feel especially adventurous, you could actually put all the refresh code in the vbs file instead of in a macro so the Excel file won't need to have any macros at all. Either way will work just fine.
0
 

Author Comment

by:gmasterv
ID: 36952499
Okay, this works pretty well. One problem, one additonal help request.

After running the macro, it's promting me if I want to overwrite.  Is there a parameter to ignore and always overwrite?

Second, instead of a macro, can you help me with a script that simply does a "Refresh All" before saving and closing?

Thanks again!!
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36952692
Overwrite what? If you want to overwrite the Excel file, you just use Close True
Also objWB.Save will save the workbook.
0
 
LVL 10

Assisted Solution

by:broro183
broro183 earned 500 total points
ID: 36952815
hi gmasterv,

I'm just popping in before Tommy gets a chance ;-)

It seems slightly odd that the code is asking about over-writing, esp based on the code in Tommy's second example - are you using ".saveas" instead of ".close true" or ".save"?
(If the below doesn't help, do you have the correct level of permissions to save to the desired folder?)

However, I think the below wrapper will help.
objExcel.DisplayAlerts = False
'whaat code are you currently using?
objWB.Close True 'False to not save
objExcel.DisplayAlerts = True

Open in new window


'Excel 2007 Help Files states:
'Workbook.RefreshAll Method:  Refreshes all external data ranges and PivotTable reports in the specified workbook.
objWB.RefreshAll

Open in new window


btw, because of excel's object model & the fact that VBA is so closely aligned with VB Script (perhaps they have the same ancestor?) you can probably get most of the initial syntax by recording a macro of your actions from within excel & then modifying the generated code.

hth
Rob
0
 

Author Comment

by:gmasterv
ID: 36952944
Thanks!  I'll try these update a little later tonight and report back.  The "record and edit" is a method I'm very familiar with; pretty much how I learned what little VB I know.

The promt does appear like it's using a save as b/c I'm getting a "A file named {filename} already exists, do you wantr to overwrite"

I've tried both True and False and both still give me the prompt.  
0
 
LVL 10

Expert Comment

by:broro183
ID: 36952979
Cool, I'm off to bed soon, but hopefully Tommy will be back to help you when you need it & hopefully I haven't stepped on his toes too badly when I cut in ;-)

Rob
0
 

Author Comment

by:gmasterv
ID: 36953740
Thanks again both of you!!  This worked perfectly.  Here is a copy of the final code:

Set objExcel = CreateObject("Excel.Application")

Set objWB = objExcel.Workbooks.Open("C:\Filename.xlsx")
objExcel.Visible = True
objWB.RefreshAll

objExcel.DisplayAlerts = False
objWB.Close True 'False to not save
objExcel.DisplayAlerts = True


objExcel.Quit
1
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36956493
My toes are fine, boro. Thanks for helping get this answered quickly.
It is odd that it was prompting for overwrite. It's not supposed to. But turning off alerts is a great way to circumvent that.

Glad it's working well.
0
 
LVL 10

Expert Comment

by:broro183
ID: 36956960
Cool, Tommy I'm glad you don't mind :-)

Yes, I think it is odd as well, so it makes me nervous that we may be masking some other error when/if we toggle the DisplayAlerts property...

Gmasterv, feel free to ignore the following questions if you are happy with your final product.

- I think that since it is being opened on the C drive this should be eliminating the possibility of any network connectivity issues leading to the overwrite request.
- I'm clutching at straws now, but:
are the links to the SQL views "read only"?/
is the file you are initially opening saved on the C drive as Read Only
is ".xlsx" your default type when saving files in excel?

btw, since you are closing the file & quitting excel as part of the code without reviewing the file, you can probably (untested!) remove the line stating "objExcel.Visible = True". I'm reasonably certain the refreshall & the save will work without the application being visible and this may make your code slightly faster. Of course, if the script errors you will have to kill the process relating to the excel instance via the Task Manager.

Rob
0
 

Author Comment

by:gmasterv
ID: 36957037
I thought of removing the visible line but then thought it best to leave it so that if there was a failure I would notice it much quicker.  As to the save prompt, I'm running everything as Admin and nothing is read only or in anyway restricted access.  XLSX is the default when I open an XLXS file in 2007 which this is.

thanks again
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36957189
Actually, I was wrong. The overwrite prompt is supposed to come up if alerts are on. There are two different alerts when you close a file
The "do you want to save" prompt nd the "do you want to overwrite the file" prompt.
The first one is fixed with the True or False on the close.
The second one happens no matter what. There is no parameter that tells it not to prompt for this. It is functioning as it is supposed to and turning off the alerts is the only way to fix it.
1
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36957207
And, gmasterv, I agree that it's a good idea to leave the visible line there.
If you are 100% confident that your code will run perfectly every time, then you can take the line out. If visible is not set to true and it has an error, it will not quit properly and you'll need to pull up task manager and look for Excel.exe in the processes to get rid of it.
1

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question