update data in Excel pivot table

[hookahmasta]

An consultant in our company has created a Pivot table where it pulls in data from our SQL database, and generates financial numbers that we can use. However, the numbers can only update whenever someone actually opens the actual Excel file and saves it. Can a macro, or something in the windows scheduler, be scheduled where it can automatically open the file and perform a save?

 

[SkipVought]

Hi,

How do YOU get the data once the PT has updated? Don't YOU open the workbook?

Skip,
[sub]
[red][/red]
[/sub]

 

[hookahmasta]

The PivotTable currently is designed to only re-fresh manually when I click on the exclimation mark. The reason it's designed to do that is.

a) The source database, which is an MSSQL database, is only updated once a night, so having the "refresh on open" option would not be useful, as the data would be the same
b) Currently, if the "refresh on open" option is checked, it would take several minutes for it to load. While that's tolerable on my end, it is perceived to be an ineffiency to the users, and they'll just not use it.

So, I'd like to have the process setup so that the PivotTable refreshes the data once a day, after the MSSQL database has finished updating.

 

[KenWright]

So why not just have cell in the file with a date and time stamp, and then have another cell using the Date and Time function calculating how long since the last time the hardcoded date and time cell was changed.

Then have an event macro tied to the open event interrogate that second cell and if it is past a period of time that would mean there had been an update to the source database, have it refresh the table.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Actually, just hardcode the next time that the source data base will update and then have the code check to see if you have passed that date/time, and if it has then have the code update the table and then put in the next due date/time for the next update. And so on.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

If its a once a day update, then
1 Create a new sheet (HIDDEN) named Update
2 in the Workbook_Open event...

Private Sub Workbook_Open()
  Dim ws As Worksheet, pt As PivotTable
  If Sheets("Update").[A1].Value < Date Then
     For Each ws In Worksheets
        For Each pt In ws.PivotTables
           pt.PivotCache.Refresh
        Next
     Next
     Sheets("Update").[A1] = Date
  End If
End Sub

Skip,
[sub]
[red][/red]
[/sub]

 

[hookahmasta]

Thanks, SkipVought..... the macro is added so that it'll automatically update the data...

However, my consultant wants to put the Excel Pivot table in a shared directory where only certain users have read write access.... the reason being that many of our users (management, especially) are Excel n00bs and they could potentially mangle the pivot tables, change formulas, etc... The idea is to tell the users who only has read access to save whatever data they generated in the pivot table to save that data on their personal shares, or just interact with the pivot tables to get the numbers they want, and not overwrite any changes to the actual excel file. This means that only the users (smart users like me and a couple of others) who have read write access will the macro work. If a user with read only rights opens the file 1st thing in the morning, the macro will not work and hence the old data remains.

So, the problem remains. Is there a way to schedule a process (w/ in windows scheduler, perhaps), on my computer (who has read/write access rights), to open the file, at say, 12:01 a.m. the next day, let the macro run, then do a automatic save and close 10 minutes later?

Hopefully, I explained things well enough so that you'd understand my situation....

 

[SkipVought]

It's gotta run on SOMEONE'S PC OnTime.

AFAIK, it cannot just OPEN on the server and run.

For instance, YOU can schedule it to OPEN at 08:00 on YOUR PC. If your PC is not connected to the server, it will not run.

Skip,
[sub]
[red][/red]
[/sub]

 

[hookahmasta]

Right, and I understand that. However, optimally, I'd like to have the macro to run at like 2 a.m. in the morning on a computer that has full read and write rights, so that the macro can kick in and automatically refresh the data, so when n00b users start opening the files at 8 a.m., the data will be refreshed and ready to go.

Therefore, I need to find a way, on my computer (which has full rights to the share) for to schedule Excel to open that file at like 2 a.m., so that macro can automatically kick in and refresh, then find a way to automatically save the file like 10 to 20 minutes later, so the refreshed data can be saved......

 

[SkipVought]

Take a look at the AfterRefresh event of a QueryTable on

http://support.microsoft.com/default.aspx?scid=kb;en-us;213187

After the AfterRefresh event, save and close

Skip,
[sub]
[red][/red]
[/sub]

 

[hookahmasta]

Here's a VB script I've created....

Set Y = CreateObject("Excel.Application")
Y.Visible = True
Y.Workbooks.Open ("M:\Pivot\Pivot Table Analyses 1.xls")
Y.Wait Now() + TimeValue("0:00:10")
Y.Activeworkbook.Save
Y.Wait Now() + TimeValue("0:00:10")
Y.Quit
Set Y = Nothing

So, it opens the file, waits 10 seconds, saves, waits another 10 seconds, then quits. The time will be adjusted accordingly depending on how long it takes for the Pivot refresh operation to complete. My question is this, however.... would the Pivots refresh during the first wait period, or does it only start when the wait period is over. Obviously, it won't work if it's the latter.

 

[SkipVought]

Set Y = CreateObject("Excel.Application")
Y.Visible = True
set wb = Y.Workbooks.Open ("M:\Pivot\Pivot Table Analyses 1.xls")
for each ws in wb.worksheets
  for each pt in ws.pivottables
    pt.pivotcache.refresh
  next
next
wb.Save
Y.Quit 
set wb = nothing
Set Y = Nothing

Skip,
[sub]
[red][/red]
[/sub]

 

[firefytr]

Also check out this Knowledge Base entry ..

http://vbaexpress.com/kb/getarticle.php?kb_id=80

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci