UpdateLink Question

  • Thread starter Thread starter DS NTE
  • Start date Start date
D

DS NTE

In a workbook on my local PC I have a commandbutton to update links from a
workbook located on a server. The code is shown below. If the connection to
the server is broken and I click the button I get a windows dialog window
which prompts me for the filename of the remote workbook. I have added
errorhandling, but that only fires when i click Cancel in the above
mentioned window.
So my question is: How can I set this up so the user gets informed that the
network connection is lost ?


Sub OppdaterFraTSPC()
'Oppfrisker data fra TSPC-arbeidsboka

On Error GoTo ErrorHandler

ActiveWorkbook.UpdateLink Name:= _
"G:\DRSENT\02.Ukerapporter\TSPC rapporter\Avregningsrapport Ekstern
Leveranse.xls" _
, Type:=xlExcelLinks

Exit Sub

ErrorHandler:
MsgBox "Oppdatering ikke tilgjengelig, bruk/modifiser eksisterende
data.", vbCritical, "Noe gikk galt..."

End Sub


mvh
Knut Egil
 
Hi Knut;

I found the following example in Excel VBA Help to user a
combination of the timer method along with Do Events.

Thanks,

Greg


Dim PauseTime, Start, Finish, TotalTime
If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes
Then
PauseTime = 5 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.
MsgBox "Paused for " & TotalTime & " seconds"
Else
End
End If


-----Original Message-----
In a workbook on my local PC I have a commandbutton to update links from a
workbook located on a server. The code is shown below. If the connection to
the server is broken and I click the button I get a windows dialog window
which prompts me for the filename of the remote workbook. I have added
errorhandling, but that only fires when i click Cancel in the above
mentioned window.
So my question is: How can I set this up so the user gets informed that the
network connection is lost ?


Sub OppdaterFraTSPC()
'Oppfrisker data fra TSPC-arbeidsboka

On Error GoTo ErrorHandler

ActiveWorkbook.UpdateLink Name:= _
"G:\DRSENT\02.Ukerapporter\TSPC
Click to expand...
rapporter\Avregningsrapport Ekstern
 
PauseTime = 5 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
Click to expand...

This little bit of code will cause the loop to run for 5 *days*.
I suspect you want something like

Do While Timer < Start + TimeSerial(0,0,PauseTime)

But as noted elsewhere, it is wholely irrelevant to the original
poster's question.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top