Sub or function not defined

[singlgl1]

I have a spreadsheet that only requires links to be updated once daily,and that would be when the previous days data is entered.This takes place between midnight and 5 am each morning.The file is a little slow to update so most users select NO when prompted.I'm trying to get a code to work that will,when opening,automatically update without prompted the user if it's being opened between midnight and 5am.Anytime the file is opened outside of these hours,give the user the normal option to update or not to. The following code is what I have so far (Thanks to other forum gurus) but I keep getting a Compile error:Sub or function not defined.I opened the project explorer and inserted the code in "this workbook" code window.I'm using excel 2007.Here's the code as it is now:

 'When the workbook is opened
Private Sub Workbook_Open()
'If the current time is between 12:00 and 5:00
If Hour(Time) >= 0 And Hour(Time) < 5 Then
    MsgBox "yes"
    'User has to update links
    linkUpdate
Else
    'Ask user to update links
    ans = MsgBox("Do you wish to update the links?", vbYesNo, "Update Links")
    'If user clicks yes to updating links
    If ans = vbYes Then
        'Update the links
        linkUpdate
    End If
End If
End Sub

 

[repairman615]

I have searched for the sub linkupdate without success.

Here in your discussion, a reply said this was a built in function, was that the case?

http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/86cba152-a6bf-42ad-b623-17b560e30ffe
Also posted at the above site.

 

[Zack Barresse]

Re: SOLVED: Sub or function not defined

As xenou points out, the linkUpdate is saying you have a routine named so. If no routine is found where applicable, you will get that error. It would need to be either a private routine available in that same code module, or non-private (public) in another code module available.

 

[singlgl1]

Re: SOLVED: Sub or function not defined

Thank you Joel, and for all others who have contributed their input.Here's the code that accomplished my needs.Thanks to all for their patience.

'When the workbook is opened
    Private Sub Workbook_Open()
'If the current time is between 12:00 and 6:00pm
If Hour(Time) >= 0 And Hour(Time) < 18 Then
    MsgBox "Links being updated automatically"
    'User has to update links
 ThisWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
Else
    'Ask user to update links
    ans = MsgBox("Do you wish to update the links?", vbYesNo, "Update Links")
    'If user clicks yes to updating links
    If ans = vbYes Then
        'Update the links
        ThisWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
    End If
    End If
End Sub

 

[Zack Barresse]

Re: SOLVED: Sub or function not defined

The code ThisWorkbook.UpdateLink is quite a bit different than updateLink, fyi.

 

[xenou]

If there really is no new data after 6:00AM I would consider *not* asking if the user wants to update links ...

Therefore (possibly) amend the code 1) always update links between 12 and 5, and otherwise just open normally without updating links (essentially, deleting the whole ELSE block).

And yeah, makes more sense now with ThisWorkbook.UpdateLink

----------
Edit: Though I guess my idea mightn't work if the first person to open the workbook at 5:01 AM doesn't refresh the data and its not been refreshed between 12 and 5. Which in fact happens to be a possibility with the code as it stands too. My preference would still be to automate a task to open the workbook at 5:00AM and refresh it and save it. Mission Accomplished.

 

[Marcelo Branco]

And yeah, makes more sense now with ThisWorkbook.UpdateLink

Indeed!

M.