summary sheets

[neilb]

Hi

I have a summary sheet that will be taking data from different weekly spreadsheets currently using formulas.

The problems is that alot of the weekly spreadsheets are not created yet so when you open the spreadsheet it keeps asking you to check each link. There is 52 * 12 links!!

Is there a better way to do this? or a macro that I can use to do this as it opens?

Any help would be much appreciated.

 

[santeria]

Have you created links to existing or non-existing files ?

 

[neilb]

non existing files for each week of 2004

 

[Andrew Poulsom]

Choose Tool|Options|Edit tab and check Ask to update automatic links. When you open the target workbook you will be prompted to update the links. If you choose No you won't get the File Not Found dialog box.

You can use this code to update the links to the workbooks that exist:

Sub UpdateLinks()
    Dim Links As Variant
    Dim i As Integer
    Dim Link As String
    Links = ActiveWorkbook.LinkSources
    If Not IsEmpty(Links) Then
        For i = 1 To UBound(Links)
            If FileExists(Links(i)) Then
                ThisWorkbook.UpdateLink Name:=Links(i), Type:=xlExcelLinks
            End If
        Next i
    End If
End Sub

Private Function FileExists(fname) As Boolean
'   Returns TRUE if the file exists
    Dim x As String
    x = Dir(fname)
    If x <> "" Then FileExists = True _
        Else FileExists = False
End Function

 

[Greg Truby]

Andrew,

I was looking at this one. I saw some constants in the Object Browser, namely XlLinkStatus, xlLinkStatusMissingFile and xlLinkStatusMissingSheet. But I can't for the life of me figure out where they get used. Any idea where these constants come into play?

 

[santeria]

Can you create "shell" files to get around the file not found issue ?

Might even help in building up a macro if you structure your files now, say... choose if you want the weekly files in one directory, or do you want a breakdown of Monthly, Quarterly, etc... Kind of pre-planning the directory infrastructure...

 

[Andrew Poulsom]

Andrew,

I was looking at this one. I saw some constants in the Object Browser, namely XlLinkStatus, xlLinkStatusMissingFile and xlLinkStatusMissingSheet. But I can't for the life of me figure out where they get used. Any idea where these constants come into play?

Greg,

Don't know - I don't have those constants in Excel 2000.

I imagine they can be used as arguments in some updated method. Tyr searching Help.

 

[neilb]

I have already created the folder structure so that the main folder holds summary sheet is in its own folder and each weeks data is in its own folder. Like

Main folder - Summary
- Week 1
- Week 2
etc

With that vba (being a novice) can I put this in so that when the book is opened it will automaticaly update with no questions?

I don't really want to create a load of empty workbooks in each week as I have access automatically sending them across?

Many thanks

Many thanks

 

[Andrew Poulsom]

You can run it from the Workbook_Open event procedure, like this:

Private Sub Workbook_Open()
   Call UpdateLinks
End Sub

 

[Greg Truby]

Andrew - VBA help was not very helpful. Not much assistance if one's starting point is a constant. But by guessing based on the few clues offered in help on the LinkInfo method I sussed it out. Not really helpful here since you've already got this one resolved in a manner that doesn't rely on something only in XL2002, but here's where they came into play:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> LinkStatusTest()
    <SPAN style="color:#00007F">Dim</SPAN> strMsg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strLinkStats(10) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, lsLinkStatus <SPAN style="color:#00007F">As</SPAN> XlLinkStatus
    
    strLinkStats(0) = "OK"
    strLinkStats(1) = "Missing File"
    strLinkStats(2) = "Missing Sheet"
    strLinkStats(3) = "Old (Not Updated)"
    strLinkStats(4) = "Source Not Calc'd"
    strLinkStats(5) = "Indeterminate"
    strLinkStats(6) = "Not Started"
    strLinkStats(7) = "Invalid Name"
    strLinkStats(8) = "Source Not Open"
    strLinkStats(9) = "Source Open"
    strLinkStats(10) = "Copied Values"
    
    alinks = ActiveWorkbook.LinkSources(XlLink.xlExcelLinks)
    <SPAN style="color:#00007F">If</SPAN> IsEmpty(alinks) <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(alinks)
        strMsg = strMsg & "Link " & i & ":" & vbTab & alinks(i) & vbCr
    <SPAN style="color:#00007F">Next</SPAN> i
    MsgBox strMsg
    strMsg = ""
    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(alinks)
        strMsg = "Link " & i & ":" & vbTab & alinks(i) & vbCr & vbCr
        lsLinkStatus = ActiveWorkbook.LinkInfo(alinks(i), xlLinkInfoStatus)
        strMsg = strMsg & strLinkStats(lsLinkStatus)
        MsgBox strMsg
    <SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>