I'm working on a spreadsheet that contains 131 rows (+1 for field names). Each row contains eight columns, four of which contain links to other files. I am working on a macro that first asks a user to enter a directory name, validates it as an existing directory and then replaces each link with a link to the same filename but in a different directory. My code works fine for the first two steps, and it seems to me that it should work fine for the final step--and it does--, but it seems to stall after 28 rows of replacement... at which point i receive a "Run-Time Error '1004': Application-defined or object-defined error."
Please take a look at the code and let me know if you see where there may be a problem. It seems odd to me that it works just fine until it hits that given cell. Any ideas? I thank you in advance for any help!
Nico Paul
Here is the code:
Please take a look at the code and let me know if you see where there may be a problem. It seems odd to me that it works just fine until it hits that given cell. Any ideas? I thank you in advance for any help!
Nico Paul
Here is the code:
Code:
Option Explicit
Function DirExists(ByVal DName As String) As Boolean
Dim sDummy As String
On Error Resume Next
If Right(DName, 1) <> "\" Then DName = DName & "\"
sDummy = Dir$(DName & "*.*", vbDirectory)
DirExists = Not (sDummy = "")
End Function
Private Sub CommandButton1_Click()
Dim BandNo As Double
Dim Community As String
Dim NewFile As String
Dim NewDir As String
NewDir = ChangeLinkedDir.TextBox1.Value
If Right(NewDir, 1) = "\" Then
NewDir = Left(NewDir, Len(NewDir) - 1)
End If
If DirExists(NewDir) And NewDir <> "" Then
Dim i As Integer
For i = 1 To 131
BandNo = Cells(1 + i, 1).Value
Community = Cells(1 + i, 2).Value
NewFile = NewDir & "\[" & BandNo & " " & Community & ".xls]"
Cells(1 + i, 3).Value = "=SUM('" & NewFile & "Summary Table'!$C$50:$E$50)"
ActiveCell.Offset(1, 0).Select
Next i
Else
MsgBox "This directory is invalid.", vbCritical, "Invalid Directory"
End If
End Sub
