CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2010
Redlaw
Redlaw

Reputation: 61

Using function to open and update values in external workbooks, but returning source errors

I've been using a function from another StackOverflow question (I'm SO sorry I can't find the original answer!) to help go through a number of cells in Column L that contains a formula that spits our a hyperlinked filepath. It is meant to open each one (workbook), update the values, then save and close the workbook before opening the next one. See below.

Sub List_UpdateAndSave()
    Dim lr As Long
    Dim i As Integer
    Dim WBSsource As Workbook
    Dim FileNames As Variant
    Dim msg As String
    ' Update the individual credit models
    With ThisWorkbook.Sheets("List")
        lr = .Cells(.Rows.Count, "L").End(xlUp).Row
        FileNames = .Range("L2:L" & lr).Value
    End With
    For i = LBound(FileNames, 1) To UBound(FileNames, 1)
        On Error Resume Next
        If FileNames(i, 1) Like "*.xls*" Then
            Set WBSsource = Workbooks.Open(FileNames(i, 1), _
                                           ReadOnly:=False, _
                                           Password:="", _
                                           UpdateLinks:=3)
            If Err = 0 Then
                With WBSsource
                    'do stuff here
                    .Save
                    .Close True
                End With
            Else
                msg = msg & FileNames(i, 1) & Chr(10)
                On Error GoTo 0
            End If
        End If

        Set WBSsource = Nothing
    Next i
    If Len(msg) > 0 Then
        MsgBox "The Following Files Could Not Be Opened" & _
               Chr(10) & msg, 48, "Error"
    End If
End Sub

The problem now is I am using this to work on a Network drive, and as a result it cause pathing issues with the Connections/Edit Links part. Each of the files are stored on S:\... which as a result of using the Hyperlink formula, won't be able to find the source data. See below the example image of a file that as been opened through a hyperlink cell from my original workbook. When I go to update the Edit Links section of it, it shows these errors.

If I open that lettered drive in Windows Explorer and find the file, it works with no problems. Open, Update Values > Save > Close, it says unknown...

(but if I click Update values here they update correctly.)

If opened using a Hyperlink formula in a cell (Also directing to S:\..) it says it contains links that cannot be updated. I choose to edit links and they're all "Error: Source not found". The location on them also starts off with \\\corp\... and not S:\.

Anyway to fix this? Apologies for the long winded question.

Upvotes: 0

Views: 140

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

I'm adding this as an answer as it contains code and is a bit long for a comment.
I'm not sure if it's what you're after though.

The code will take the mapped drive and return the network drive, or visa-versa for Excel files. DriveMap is the variable containing the final string - you may want to adapt into a function. 

Sub UpdatePath()

    Dim oFSO As Object
    Dim oDrv As Object
    Dim FileName As String
    Dim DriveMap As String

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    FileName = Range("A1")

    If InStr(oFSO.GetExtensionName(FileName), "xls") > 0 Then

        For Each oDrv In oFSO.drives
            If oDrv.sharename <> "" Then
                'Changes \\corp\.... to S:\
                If InStr(FileName, oDrv.sharename) = 1 Then
                    DriveMap = Replace(FileName, oDrv.sharename, oDrv.Path)
                End If

                'Changes S:\ to \\corp\....
'                If InStr(FileName, oDrv.Path) = 1 Then
'                    DriveMap = Replace(FileName, oDrv.Path, oDrv.sharename)
'                End If

            End If
        Next oDrv

    End If

End Sub

Upvotes: 0

Related Questions