Microsoft Office Forums - View Single Post

ptmuldoon · 11-29-2014, 03:32 PM

Thanks Macropod, and you've been great help in the past.

But in this task, the user will actually be picking/choosing a different filename to link to the Word document. So not sure if the relevant path code will do the trick.

I came across this code that does most of what I'm looking for. But I need to modify it to replace the folder path and file name separately (I think). This is because Excel graphs that are OLE links in word show their LINK code slightly different. I think there inlineshapes?

So I'm trying to modify the below to replace the link file path and link file name separately. But the 'FileName' variable is still showing the full path and name, and not the name only.

The original code is by 'theDrew' from MrExcel forums back in 2011. So thanks to him as well in getting me started

Code:

Public Sub changeSource()
    Dim dlgSelectFile As FileDialog 'FileDialog object '
    Dim thisField As Field
    Dim selectedFile, FileName As Variant
    'must be Variant to contain filepath of selected item
    Dim newFile As Variant
    Dim fieldCount As Integer '
    Dim x As Long
    On Error GoTo LinkError

    'create FileDialog object as File Picker dialog box
    Set dlgSelectFile = Application.FileDialog(msoFileDialogFilePicker)
    With dlgSelectFile
        .InitialFileName = ActiveDocument.Path
        .Filters.Clear 'clear filters
        .Filters.Add "Microsoft Excel Files", "*.xls, *.xlsb, *.xlsm, *.xlsx" 'filter for only Excel files
        'use Show method to display File Picker dialog box and return user's action

        If .Show = -1 Then
            'step through each string in the FileDialogSelectedItems collection
            For Each selectedFile In .SelectedItems
                newFile = selectedFile 'gets new filepath
                FileName = .SelectedItems.Item(1)
            Next selectedFile
        Else 'user clicked cancel
            Exit Sub
        End If
    End With
    
    MsgBox "Selected item's Full Path and Name: " & newFile
    MsgBox "The file name only is: " & FileName

'    Set dlgSelectFile = Nothing
'    'update fields
'    With ActiveDocument
'        fieldCount = .Fields.Count
'        For x = 1 To fieldCount
'            With .Fields(x)
'                'Debug.Print x '
'                Debug.Print .Type
'                If .Type = 56 Then
'                    'only update Excel links. Type 56 is an excel link
'                    .LinkFormat.SourceFullName = newFile '
'                    .Update
'                    .LinkFormat.AutoUpdate = False
'                    DoEvents
'                End If
'            End With
'        Next x
'    End With

    
    MsgBox "Source data has been successfully imported."
Exit Sub

LinkError:
Select Case Err.Number
  Case 5391 'could not find associated Range Name
    MsgBox "Could not find the associated Excel Range Name " & _
      "for one or more links in this document. " & _
      "Please be sure that you have selected a valid " & _
      "Quote Submission input file.", vbCritical
  Case Else
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
End Select
End Sub