We receive a lot of spreadsheets from our clients that contain links to their internal servers. Obviously we can not update these links and we get the following error:
I'm trying to run some VBA on these as they come in and I'm having a few problems. This being one of them.
Is it possible to disable all linking with VBA or maybe another way so it doesn't interrupt the script?
Andy.
Re: Links to other spredsheets.
Have a look at the available options for the Workbook Open command - You'll find one for 'UpdateLinks'. Set True/False as needed.
Re: Links to other spredsheets.
Sub ProcessFiles()
Dim Filename, Pathname As String
Dim wb As Workbook
Pathname = ActiveWorkbook.Path & "\Files\"
Filename = Dir(Pathname & "*.xls")
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
Do While Filename <> ""
Set wb = Workbooks.Open(Pathname & Filename)
''''''''''''''''''''''''''''''''
Workbooks[B].Open[/B].UpdateLink = False ' Error where bold
'Application.DisplayAlerts = False
'Application.AskToUpdateLinks = False
'Application.DisplayAlerts = True
''''''''''''''''''''''''''''''''
DoWork wb
wb.Close SaveChanges:=True
Filename = Dir()
Loop
MsgBox "Highlighted RAM"
End Sub
Sub DoWork(wb As Workbook)
With wb
Dim vCell As Range
'Loop through every used cell in the active worksheet
For Each vCell In ActiveSheet.UsedRange
'If the cells value contains "ram" and highlight row
If InStr(vCell.Value, "ram") > 0 Then
vCell.EntireRow.Interior.ColorIndex = 6
End If
Next
End With
End Sub
I'
the error is where I've highlighted in bold. "argument not optional" is what I get. I've tried putting set = before but the error is the same. Also I'm not 100% its in the right place. I don't know if its obvious but I'm no VBA expert haha.
Re: Disable 'Update Links' message when opening workbook
That makes sense. Also thanks for pointing me to the workbook commands, it helps me to get a better understanding and now I see how to use these commands better.
Don’t have an account yet? Register yourself now and be a part of our community!