Updating tables in Word

BigMac'ro · #1 04-14-2025, 12:19 AM

Quote:

Originally Posted by Italophile

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

Code:

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:

Code:

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

and when calling the function:

Code:

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

Quote:

Originally Posted by Italophile

In your new variable declarations

Code:

Dim chartObj As Excel.chartObject

should be

Code:

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

Code:

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

Code:

Dim chartObj As Excel.Chart

but always corrects back to

Code:

Dim chartObj As Excel.chart

Quote:

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 · #2 04-14-2025, 12:32 AM

Quote:

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.

BigMac'ro · #3 04-15-2025, 07:40 AM

Quote:

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:

Code:

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 · #4 04-15-2025, 05:44 PM

Quote:

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.

Italophile · #5 04-14-2025, 01:43 AM

Quote:

Originally Posted by BigMac'ro

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

Code:

Dim chartObj As Excel.Chart

but always corrects back to

Code:

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Is there a way to stop macro modifications from undoing when Updating Word Linked Tables?	soroush.kalantari	Word VBA	3	06-27-2021 06:15 PM
Problem With Updating Linked Excel Tables in Word 2013	maverick1714	Word	6	09-30-2014 12:30 AM
Word 2013 Fields not updating	awaywithpixie	Word	1	09-10-2013 10:35 AM
Updating lists of figures/Tables in a protected document	IHARKIS	Word	0	03-02-2011 04:39 AM
Auto-updating basic math functions & cross-referencing tables	FranklinBluth	Word Tables	13	11-19-2009 10:26 AM