0

I'm trying to set up a button in MS Word to update the linked Excel data source behind charts. I'll say upfront that I know this is easy with built-in functionality, but I'd like to use a button for those uncomfortable navigating the menus. Also, I've read and drawn from some great articles on this topic in general, but am getting an error not referenced in those pieces.

Testing has shown that Word a) identifies the Excel link as a field (vs a shape) and b) flags this field as type 87 (OCX). When I run the code below, it generates an "object variable or With block variable not set" error. However this appears to possibly be rooted in the field type based on this Microsoft article.

I'm struggling to find an alternative method and would appreciate help... this is my first foray into VBA for Word. Thanks!

Sub CommandButton1_Click()
Dim MyNewFile As Variant
Dim fDialog As FileDialog, result As Integer
Dim fieldCount As Integer

'Set up file dialog
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
    .AllowMultiSelect = False
    .Title = "Select a file"
    .Filters.Clear
    .Filters.Add "Excel files", "*.xlsx,*.xlsb,*.xlsm,*.xls"
End With

'Show the dialog, store the file name
If fDialog.Show = -1 Then
   MyNewFile = fDialog.SelectedItems(1)
End If

fieldCount = ActiveDocument.Fields.Count
For k = 1 To fieldCount
    With ActiveDocument.Fields(k)
    If .Type = 87 Then
     With .LinkFormat
        .SourceFullName = MyNewFile 'BLOWS UP HERE
        .AutoUpdate = True
     End With
     End If
    End With
Next k

End Sub
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
kpk122
  • 1
  • Mmm, FWIW the basic syntax `ActiveDocument.Fields([index]).LinkFormat.SourceFullName = "hard-coded file path"`worked fine for me. You might test by hard-coding exactly that - no loop, no FileDialog - just check the very basic syntax. Also, look at how the original source Excel file/chart is constructed and compare that to the one you're trying to change to. If that doesn't lead you to anything, post the internal field code here so that we can see what kind of LINK field we're dealing with. – Cindy Meister Jan 03 '20 at 20:36
  • @CindyMeister you're amazing for two reasons, I'm all set. By checking the code, your comment above made me realize I was barking up the wrong tree - the "field" I assumed to be the one link in the doc was actually referring to the button. And another of your posts [link](https://stackoverflow.com/questions/51765431/vba-word-change-data-of-charts) helped me find the correct way - I'd checked Shapes but didn't even know to check for InlineShapes which the chart used. After making adjustments everything worked for both charts and tables. – kpk122 Jan 03 '20 at 22:09
  • @CindyMeister one minor hiccup that's only tangentially related to the above. I got everything working perfectly locally. However when I point to a network location the chart / InlineShape works perfectly but the table / Field chokes. This is trying to point to copy of the same exact file, and oddly stumbles whether I use VBA or the File->Info etc menu. It's also fine with a new object, but won't switch an existing one. Do you know what could do that? Again, appreciate your expertise!! – kpk122 Jan 03 '20 at 23:10
  • No, networking is not my area of expertise. Since this also happens in the end-user mode you might try asking this on Super User. Someone there who works more in networks might have more information. – Cindy Meister Jan 06 '20 at 11:14

0 Answers0