VBA code help for linking Word file to Excel file

C

cat2102

Hello-

I am using the code pasted below to link Word docs which contain field links
to Excel files. In selecting the Excel file to link to Word doc, the code
opens up the default file location (My Documents), however I would prefer
that it open up the file folder in which the Word document itself is located.
Is there a way to change the code for that to happen? Any assistance is
making that change would be appreciated.

Best,
-Cathy


Sub ChangeLinkedSpreadsheet()

Dim vExistingSpreadsheet As String
Dim vNewSpreadsheet As String
Dim vNumFields As Long
Dim fd As FileDialog
Dim i As Long

vNumFields = ActiveDocument.Fields.Count

If vNumFields = 0 Then
MsgBox "No fields found, exiting."
Exit Sub
End If

vExistingSpreadsheet = ActiveDocument.Fields(1).LinkFormat.SourceFullName
MsgBox "Existing linked spreadsheet is " & vExistingSpreadsheet

' Filepicker dialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.

Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd
.Title = "Select the spreadsheet you want to link this document to"
.AllowMultiSelect = False
.ButtonName = "Link"
.Filters.Add "Excel Template", "*.xlt", 1
.Filters.Add "Excel Files", "*.xls", 2
.Filters.Add "Excel Workbooks", "*.xlw", 3
.FilterIndex = 2

'Use the Show method to display the File Picker dialog box
'The user pressed the action button.

If .Show = -1 Then

For Each vrtSelectedItem In .SelectedItems

vNewSpreadsheet = vrtSelectedItem
Next vrtSelectedItem
Else
Exit Sub
End If

End With

'Set the object variable to Nothing.
Set fd = Nothing

For i = 1 To vNumFields
' ActiveDocument.Fields(i).Select
' Selection.copyformat

If ActiveDocument.Fields(i).Type = wdFieldLink Then
'ActiveDocument.Fields(i).Select
'Selection.copyformat
ActiveDocument.Fields(i).LinkFormat.SourceFullName =
vNewSpreadsheet
'ActiveDocument.Fields(i).Select
'Selection.PasteFormat
End If

ActiveDocument.Fields(i).Update
' ActiveDocument.Fields(i).Select
' Selection.PasteFormat
Next i

End Sub
 
J

Jay Freedman

Just add this line within the 'With fd ... End With' structure:

.InitialFileName = ActiveDocument.Path

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.
 
D

Doug Robbins - Word MVP

Use the .InitialFileName attribute of the FileDialog object as follows

Dim fd As FileDialog
Dim docpath As String 'added code
docpath = ActiveDocument.Path 'added code
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.

Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd
.InitialFileName = docpath 'added code
.Title = "Select the spreadsheet you want to link this document to"
.AllowMultiSelect = False
.ButtonName = "Link"
.Filters.Add "Excel Template", "*.xlt", 1
.Filters.Add "Excel Files", "*.xls", 2
.Filters.Add "Excel Workbooks", "*.xlw", 3
.FilterIndex = 2

'Use the Show method to display the File Picker dialog box
'The user pressed the action button.

If .Show = -1 Then

For Each vrtSelectedItem In .SelectedItems

vNewSpreadsheet = vrtSelectedItem
Next vrtSelectedItem
Else
Exit Sub
End If
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
 
C

cat2102

Thanks to both of you!

Doug Robbins - Word MVP said:
Use the .InitialFileName attribute of the FileDialog object as follows

Dim fd As FileDialog
Dim docpath As String 'added code
docpath = ActiveDocument.Path 'added code
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.

Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd
.InitialFileName = docpath 'added code
.Title = "Select the spreadsheet you want to link this document to"
.AllowMultiSelect = False
.ButtonName = "Link"
.Filters.Add "Excel Template", "*.xlt", 1
.Filters.Add "Excel Files", "*.xls", 2
.Filters.Add "Excel Workbooks", "*.xlw", 3
.FilterIndex = 2

'Use the Show method to display the File Picker dialog box
'The user pressed the action button.

If .Show = -1 Then

For Each vrtSelectedItem In .SelectedItems

vNewSpreadsheet = vrtSelectedItem
Next vrtSelectedItem
Else
Exit Sub
End If
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
Click to expand...
 
B

Ben Slabaugh

Hi guys:
When I run this code I get a
Run-time error '91':
Object variable or With block variable not set

on the following line of code

vExistingSpreadsheet = ActiveDocument.Fields(1).LinkFormat.SourceFullName

I'm working in Word 2007 with a .docm file. I don't know VBA syntax real well so any help is appreciated!

My ultimate goal is to create an "Update Links" button on the first page of the document so that a new user can easily click it, select the new excel file, and have it update the information in all the links automatically.

Thanks
 
J

Jay Freedman

I can reproduce that behavior if the spreadsheet is inserted in the Word document through the Paste Special dialog as an Excel object *without selecting the Paste Link option button*. The result is
an EMBED field, and in that case ActiveDocument.Fields(1).LinkFormat has the value Nothing so the SourceFullName member doesn't exist.

If instead I insert the spreadsheet as an Excel object *and select the Paste Link option button*, the result is a LINK field and the macro works as expected.
 
J

Jay Freedman

By the way, there's no reason to write this as a macro. Instead, customize the Quick Access Toolbar; first set the "Customize Quick Access Toolbar" dropdown at the top right to your template (which
must be open at the time), then select the category "Office Menu" on the left and add the "Edit Links to Files" to the toolbar. This displays the same dialog as clicking Office button > Prepare > Edit
Links to Files, which offers exactly the function you're trying to recreate.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word macro that can call cells out of Excel for a find and replace 0
Working outlook via vba code with xlsx - error 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Would like better understanding of how this macro works. 3
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
VBA word change keys 0
VBA Coding Help for Beginner 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0

Top