VBA update links

[chriscsmith]

Afternoon all!

I've searched various forums including this one and ozgrid and can't seem to find a solution '1004 error'.

1. I have a master file hosted on Sharepoint - no one opens or uses this file other than me; and I only use it for monitoring.
2. This master file has vlookup links (essentially cell references) to 104 other excel workbooks hosted on Sharepoint.
3. Users access their workbooks make updates, save and close.
4. The master file captures all updates that have been made through all 104 workbooks, compiles an analysis based on an organizational heiarchy, recognizes the input from the highest level of management and places it into 3 seperate fields.
5. Now, the when any other users throughout the company access their workbook via sharepoint, the field fields that are recognized (through the highest level of mgmt) get pulled from the master and placed in their workbook through a vlookup.

With all that said, everything works wonderfully and quite seamless except for 1 issue. I have to access the master file to 'update links' and save the file. Otherwise, the 104 workbooks will only pull those 3 input fields based on whenever the last update link/save was done. If the file needs to be open to generate the vb, then I can surely leave it open in the background. I do enjoy sleep, so if anyone has ideas or has done something similar previously I would greatly appreciate it.

Here is what I have thus far (advanced apologies if the embedded code table doesn't appear, I couldn't locate the toggle for that):

Dim mdteScheduledTime As Date
Sub RefreshData()
ThisWorkbook.UpdateLink Name:="http://spintranet.mitchell.com/calibration/workbook1.xlsm", Type:=xlExcelLinks
ThisWorkbook.UpdateLink Name:="http://spintranet.mitchell.com/calibration/workbook2.xlsm", Type:=xlExcelLinks
ThisWorkbook.UpdateLink Name:="http://spintranet.mitchell.com/calibration/workbook3.xlsm", Type:=xlExcelLinks
ThisWorkbook.UpdateLink Name:="http://spintranet.mitchell.com/calibration/workbook4.xlsm", Type:=xlExcelLinks
' And so on with the other 100 workbooks
mdteScheduledTime = Now + TimeSerial(0, 1, 0)
Application.OnTime mdteScheduledTime, "RefreshData"
End Sub
Sub StopRefresh()
Application.OnTime mdteScheduledTime, "RefreshData", , False
End Sub

 

[chriscsmith]

Do any VB programmers out there have insight/experience with this?!

 

[chriscsmith]

Just an update: I figured it out.

The code has to be placed in a module, not a sheet or ThisWorkbook.

Also, the landing page cannot be protected when the code is refreshed on intervals.

 

[cme263]

Chris,
FYI.
According to the online help, you should be able to use the UpdateLink without specifiing a file name, and it is suppose to default to all links.
I doesn't work.
-----
' ActiveWorkbook.UpdateLink
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
-----
Thanks to JS411, using the name above works.
This will eliminate 104 specific entries, and protects against file_name changes.

RefreshData does not update Pivot Tables, if you use pivot tables, I'd be interesested to know how you updated them? I have about 80 tables on 3 dozen worksheets.

 

[chriscsmith]

When I wrote my last reply I hadn't launched the workbooks to all the intended parties. So needless to say, the VB never worked properly. There may be other situations where it does, but based on my process it didn't.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I had the 104 workbooks loaded on Sharepoint, with a Master excel file (also on Sharepoint) housing the entries made on each of the 104 workbooks. The master excel file did an analysis of all entries to provide the hiearchy of entries in the highest job level within the company. These entries were then called back to each of the 104 workbooks showing the 'final result'.<o></o>
<o></o>
The only way for me to get these links to update was to either open the file manually, or manually 'update links' through the Excel UI ribbon tab. The only other alternative would be to create a batch process and set it up through windows scheduler; which I opted out of doing.<o></o>
<o></o>
I read on a few other websites that the VB doesn't work correctly using this function through Excel and Microsoft has acknowledged. I had two other career VB professionals that I work with look at it and they couldn’t determine a solution either. It may work properly through a Shared Network Drive and we know it defiantly works properly if its saved directly on your PC; but I can confirm it DOES NOT WORK on Sharepoint using a URL to reference the worksheet and the cell. If you do find a solution, please do let me know because I still can’t sleep at night.<o></o>

 

[chriscsmith]

Are the worksheets with the pivots updated?

Here's the code I had for my pivot table tab...

Private Sub Worksheet_Activate()
Dim pt As PivotTable
For Each pt In Me.PivotTables
pt.RefreshTable
Next pt
End Sub

Sub DirectPivotSort()
ActiveSheet.PivotTables("Direct_Zone_Comparisons").PivotSelect _
"'Zone | Performance/Potential | Employee Name'[All]", xlLabelOnly + xlFirstRow _
, True
Range("F27").Select
ActiveSheet.PivotTables("Direct_Zone_Comparisons").PivotFields( _
"2011 Performance Rating").AutoSort xlAscending, "2011 Performance Rating"
End Sub

 

[Jerry Sullivan]

RefreshData does not update Pivot Tables, if you use pivot tables, I'd be interesested to know how you updated them? I have about 80 tables on 3 dozen worksheets.

cme263, Here are two techniques you can use to refresh all the PivotTables in your Workbook:

Sub RefreshAllPivots1()
    ActiveWorkbook.RefreshAll
End Sub

Sub RefreshAllPivots2()
   Dim PC As PivotCache
   For Each PC In ActiveWorkbook.PivotCaches
      PC.Refresh
   Next PC
End Sub

chriscsmith, The above code probably won't solve your SharePoint problem.
I have access to a SharePoint Server and will try to look at this later today.

Have you tried setting up a DataConnection to an External File?

 

[chriscsmith]

I did attempt to use the data connection, but had the same problem in updating the links