Microsoft Office Forums - View Single Post

BigMac'ro · 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)