Updating tables in Word

[BigMac'ro]

Hi,


for a report that has to get updates on a montly basis, I try to write a macro that would update all charts and tables in it automatically.


My approach is to bookmark every table and every chart and to export the list of bookmarks to Excel (macro for this works well). I then add, for each bookmark, the path to the file where the relevant table/chart is located, the corresponding worksheet as well as the chart name (or table range).
Let's only discuss the charts here. The macro I am currently working on would read this reference file, grab the charts in the right file and replace them at the bookmark location in word.
I am at a point where it works, here is the code:

Sub UpdateWordFromExcelMappings()
    Dim xlApp As Object
    Dim xlRefWB As Object
    Dim xlDataWB As Object
    Dim xlSheet As Object
    Dim chartObj As Object
    Dim chartImg As Object
    Dim i As Long, lastRow As Long
    Dim wordBookmark As String
    Dim filePath As String, sheetName As String, rangeOrChart As String
    Dim bmRange As Range

    ' Reference file and worksheet names
    Const REF_FILE_PATH As String = "\\Path\to\the\reference\file.xlsx"
    Const TABLE_SHEET As String = "Tables"
    Const CHART_SHEET As String = "Graphs"

    ' Start Excel
    Set xlApp = CreateObject("Excel.Application")
    Set xlRefWB = xlApp.Workbooks.Open(REF_FILE_PATH, False, True)


' === Handle Charts ===
    With xlRefWB.Sheets(CHART_SHEET)
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).row  ' Find last used row in column A
        
        For i = 2 To lastRow
            ' Read and trim values from reference file
            wordBookmark = Trim(CStr(.Cells(i, 1).Text))
            filePath = Trim(CStr(.Cells(i, 5).Text))
            sheetName = Trim(CStr(.Cells(i, 6).Text))
            rangeOrChart = Trim(CStr(.Cells(i, 7).Text))
    
            ' Skip row if required info is missing
            If wordBookmark = "" Or filePath = "" Or sheetName = "" Or rangeOrChart = "" Then
                Debug.Print "Skipping chart row " & i & " due to missing data"
            Else
                ' Open chart source workbook (or reuse the reference file if same)
                If xlRefWB.FullName = filePath Then
                    Set xlDataWB = xlRefWB
                Else
                    If Not WorkbookIsOpen(filePath) Then
                        Set xlDataWB = xlApp.Workbooks.Open(filePath, False, True)
                    Else
                        Set xlDataWB = xlApp.Workbooks(filePath)
                    End If
                End If
    
                ' Try to get the worksheet
                Set xlSheet = xlDataWB.Sheets(sheetName)
    
                ' Try to get the chart object
                Set chartObj = Nothing
                On Error Resume Next
                Set chartObj = xlSheet.ChartObjects(rangeOrChart)
                On Error GoTo 0
    
                If Not chartObj Is Nothing Then
                    ' Copy the chart as an image
                    chartObj.CopyPicture Appearance:=1, Format:=2 ' xlScreen, xlPicture
    
                    ' Paste into Word if bookmark exists
                    If ActiveDocument.Bookmarks.Exists(wordBookmark) Then
                        Set bmRange = ActiveDocument.Bookmarks(wordBookmark).Range
                        bmRange.Paste
                        ActiveDocument.Bookmarks.Add wordBookmark, bmRange ' Re-add the bookmark after paste
                    End If
                Else
                    MsgBox "Chart '" & rangeOrChart & "' not found in sheet '" & sheetName & "'", vbExclamation
                End If
    
                ' Only close the workbook if it's not the reference file
                If Not xlDataWB Is xlRefWB Then xlDataWB.Close False
            End If
        Next i
    End With

    ' Cleanup
    xlRefWB.Close False
    xlApp.Quit
    Set xlApp = Nothing

    MsgBox "Tables and charts have been updated.", vbInformation
End Sub


Function WorkbookIsOpen(filePath As String) As Boolean
    Dim wb As Workbook
    On Error Resume Next ' In case of error (e.g., workbook not found)
    Set wb = xlApp.Workbooks(filePath) ' Try to get the workbook
    On Error GoTo 0 ' Reset error handling
    
    If Not wb Is Nothing Then
        WorkbookIsOpen = True ' Workbook is open
    Else
        WorkbookIsOpen = False ' Workbook is not open
    End If
End Function

The only thing bothering me is that the charts are copied as pictures, and I would like to have editable objects (but not necessarily linked to the source file). I tried to use

chartObj.Copy

instead of

chartObj.CopyPicture

but this throws runtime error 445.


Any idea how to solve this?


Thank you!


(code parts about tables removed here, that's another topic...)

[Italophile]

The first mistake you have made is using Late Binding.
There is no value to using late binding in this instance. The only advantage to late binding is version independence, i.e. you can write code in a newer version and it will theoretically work in an older one. But that advantage is lost unless you also use late binding for the host application code as Office is usually installed as a package. It is better to use early binding when working with other Office applications and reserve late binding for other commonly used libraries, e.g. ADO or XML.

By adding a reference to the Excel library (Tools | References) and declaring the object variables with their correct types (eg. Dim chartObj As xl.Chart) you get the benefit of IntelliSense.

Instead of

chartObj.Copy

What you need is

chartObj.ChartArea.Copy

When pasted this will give you a linked chart but the link can be broken by adding something like

bmRange.InlineShapes(1).Chart.ChartData.Activate
bmRange.InlineShapes(1).Chart.ChartData.BreakLink

[BigMac'ro]

Thank you for the quick answer! Using early binding is for sure better, and I even already had the library references checked. I now modified the variable declarations:

Dim xlApp As Excel.Application
    Dim xlRefWB As Excel.Workbook
    Dim xlDataWB As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim chartObj As Excel.chartObject
    Dim i As Long, lastRow As Long
    Dim wordBookmark As String
    Dim filePath As String, sheetName As String, rangeOrChart As String
    Dim bmRange As Word.Range

But when using

chartObj.ChartArea.Copy

, runtime error 438 occurs
I'll leave the screen a bit and see if I can find a solution later...

[Italophile]

There is a compile error in the code you have posted for Function WorkbookIsOpen.

Set wb = xlApp.Workbooks(filePath) ' Try to get the workbook

the variable xlApp does not exist in this scope.

In your new variable declarations

Dim chartObj As Excel.chartObject

should be

Dim chartObj As Excel.Chart

[macropod]

Why aren't you simply using a template with Links to the Excel workbook & charts, which you could simply update and, if appropriate, break before saving the new document? That would make most of your code redundant and wouldn't require the use of bookmarks.

[BigMac'ro]

Originally Posted by Italophile

There is a compile error in the code you have posted for Function WorkbookIsOpen.

Set wb = xlApp.Workbooks(filePath) ' Try to get the workbook

the variable xlApp does not exist in this scope.

That's true, though i didn't run into a compile error when executing my code But you're right, it's cleaner like this:

Function WorkbookIsOpen(filePath As String, xlApp As Excel.Application) As Boolean

and when calling the function:

If Not WorkbookIsOpen(filePath, xlApp) Then
...

Originally Posted by Italophile

In your new variable declarations

Dim chartObj As Excel.chartObject

should be

Dim chartObj As Excel.Chart

I tried this, but then in this block it somehow jumps to the "else" condition and the message box is displayed

If Not chartObj Is Nothing Then
                    ' Copy the chart as an image
                    'chartObj.CopyPicture Appearance:=1, Format:=2 ' xlScreen, xlPicture
                    chartObj.ChartArea.Copy
                    'chartObj.Copy
    
                    ' Paste into Word if bookmark exists
                    If ActiveDocument.Bookmarks.Exists(wordBookmark) Then
                        Set bmRange = ActiveDocument.Bookmarks(wordBookmark).Range
                        bmRange.Paste
                        ActiveDocument.Bookmarks.Add wordBookmark, bmRange ' Re-add the bookmark after paste
                    End If
                Else
                    MsgBox "Chart '" & rangeOrChart & "' not found in sheet '" & sheetName & "'", vbExclamation
                End If

I'm also wondering why the VBA editor won't let me write

Dim chartObj As Excel.Chart

but always corrects back to

Dim chartObj As Excel.chart

Why aren't you simply using a template with Links to the Excel workbook & charts, which you could simply update and, if appropriate, break before saving the new document? That would make most of your code redundant and wouldn't require the use of bookmarks.

That would be possible if the Excel workbooks wouldn't change. In my case, the report gets regularly updated and so do the Excel files. The old Excel files are kept. For the report in January 2024, I have to refer to excel files called e.g. "202401_Data1.xlsx", "202401_Data2.xlsx",... One month later, it would be "202402_Data1.xlsx", "202402_Data2.xlsx",... That's why I came up with the "reference file" listing all paths to the relevant workbooks. Plus, there are several people working on the report, sometimes with somewhat inconsistent file-naming approaches I think it will be more easy to convince them to keep the reference file up to date than changing their file-naming habits...




(I now also added "Option Explicit" at the beginning of my code to avoid getting into troubles concerning variable declaration)

[macropod]

Originally Posted by BigMac'ro

That would be possible if the Excel workbooks wouldn't change. In my case, the report gets regularly updated and so do the Excel files. The old Excel files are kept. For the report in January 2024, I have to refer to excel files called e.g. "202401_Data1.xlsx", "202401_Data2.xlsx",... One month later, it would be "202402_Data1.xlsx", "202402_Data2.xlsx",... That's why I came up with the "reference file" listing all paths to the relevant workbooks. Plus, there are several people working on the report, sometimes with somewhat inconsistent file-naming approaches I think it will be more easy to convince them to keep the reference file up to date than changing their file-naming habits...

So use VBA code to change the link filenames. Trivial compared to what you're trying to do now. Even with inconsistent filenaming, all you need is a dialog box for selecting the source file. This can all be done from within Word and, depending on what you want to do can be done via DATABASE fields or possibly ADODB - the latter without even opening the workbook. Think about it.

[Italophile]

Originally Posted by BigMac'ro

I'm also wondering why the VBA editor won't let me write

Dim chartObj As Excel.Chart

but always corrects back to

Dim chartObj As Excel.chart

It happens and is of no consequence. What is essential is that the object/datatype is valid. You can use IntelliSense or the Object Browser for that.

[BigMac'ro]

Originally Posted by macropod

So use VBA code to change the link filenames. Trivial compared to what you're trying to do now. Even with inconsistent filenaming, all you need is a dialog box for selecting the source file. This can all be done from within Word and, depending on what you want to do can be done via DATABASE fields or possibly ADODB - the latter without even opening the workbook. Think about it.

Thanks for the input, macropod, that's for sure something I will think about! You say: "all you need is a dialog box for selecting the source file" - that's actually the key point: Around 50 graphs in the document, around 10 people working on it, so there are plenty of source files (that's why I try to gather them in a reference file)...


For the moment, I could achieve what I wanted to do with graphs. As often, a small mistake was hindering me: I noticed the graphs I am working with are not of type chartObject or Chart, but they are actually shapes! Therefore, the code in my previous post always jumped to the "else" condition.

I replaced this code block and now loop through the shapes:

Dim shp As Excel.Shape


For Each shp In xlSheet.Shapes
                    If shp.Type = msoChart And shp.Name = rangeOrChart Then
                        shp.Copy
                        If doc.Bookmarks.Exists(wordBookmark) Then
                            Set bmRange = doc.Bookmarks(wordBookmark).Range
                            bmRange.Paste
                            doc.Bookmarks.Add wordBookmark, bmRange
                        End If
                        chartFound = True
                        Exit For
                    End If
                  Next shp

Thanks again to both of you for your valuable guidance! Now, let's go for the tables in the document

[macropod]

Originally Posted by BigMac'ro

Thanks for the input, macropod, that's for sure something I will think about! You say: "all you need is a dialog box for selecting the source file" - that's actually the key point: Around 50 graphs in the document, around 10 people working on it, so there are plenty of source files (that's why I try to gather them in a reference file)...

In that case, it's time to impose some naming consistency.

What is more, with a little discipline, you could do all this with no VBA code at all! All that would require is that the source files always use the same worksheet & workbook names and are stored in the same folder.* If the ranges in those workbooks expand/contract, simply used named ranges and link to those. That way, whenever you create a new document based on the Word template, it can automatically update the links to the data. You can then break the links (for which you might use a trivial macro that executes before the document is first saved).

*If necessary because the individual monthly files only contain the data for that month, this might be done by making copies of the source files once they've been prepared, renaming the copies with the standard names, then copying them to the required folder.