Reffering to Excel Chart from Access

[silentwolf]

Hi guys,

unfortunatelly I am getting an runtime Error 438, Object not supported.
When I try to copy a Chart from an Excel Worksheet.

here is the Code.

"Hole Anwendung"

Function HoleAnwendung(strName As String) As Object
    On Error Resume Next
    Set HoleAnwendung = GetObject(, strName)
    If HoleAnwendung Is Nothing Then
        Set HoleAnwendung = CreateObject(strName)
    End If
End Function

and the code I am trying to use to insert a Diagram to a Word Document

Sub CreateBasicWordReportFromTemplate()
   
    Set m_appWord = HoleAnwendung("Word.Application")
    Dim SaveName As String
    Dim FileExt As String
   
    With m_appWord
'        .Visible = True
'        .Activate
        .Documents.Add ("C:\Users\Albert\AppData\Roaming\Microsoft\Templates\Movie Report Template.dotx")
   
        Set m_appExcel = HoleAnwendung("Excel.Application")
   
        Dim wkbExcel As Excel.Workbook
        Dim wksExcel As Excel.Worksheet
       
        Set wkbExcel = m_appExcel.Workbooks.Open("C:\Users\Albert\Desktop\Movies.xlsx")
        Set wksExcel = wkbExcel.Worksheets("Tabelle1")
 
        wksExcel.Range("A2", wksExcel.Range("A2").End(xlDown).End(xlToRight)).Copy
 
'        .Selection.GoTo wdGoToBookmark      'early binding
        .Selection.GoTo what:=-1, Name:="TableLocation"     'late binding
        .Selection.Paste
       
        wkbExcel.Diagramm1.ChartArea.Copy

        .Selection.GoTo what:=-1, Name:="ChartLocation"     'late binding
        .Selection.Paste
       
        If .Version <= 11 Then
            FileExt = ".doc"
        Else
            FileExt = "docx"
        End If
       
        SaveName = Environ("UserProfile") & "\Desktop\Movie Report " & _
            Format(Now, "yyyy-mm-dd-hh-mm-ss") & FileExt
       
        If .Version <= 12 Then
            .ActiveDocument.SaveAs SaveName
        Else
            .ActiveDocument.SaveAs2 SaveName
        End If
       
        .ActiveDocument.Close
        .Quit

        wkbExcel.Close
        m_appExcel.Quit
       
    End With

    Set m_appWord = Nothing
    Set m_appExcel = Nothing
End Sub

In my workbook I have created a Diagramm1 for some data which is in the worksheet "Diagramm1"

I can not figure out how I can refer to the diagramm so I hope to get som advice from you!

The issue is on the line " wkbExcel.Diagramm1.ChartArea.Copy"

Many thanks

Albert

 

[arnelgp]

is your Chart on Separate Sheet?
i think it is, then you just Export the chart to as png

wkbExcel.Diagramm1.Export Environ$("userprofile") & "\documents\chart.png"

now, go to this forum on how to insert picture to your word document:

ms access - Insert Image into Word by using VBA - Stack Overflow

 

[silentwolf]

Hi arnelgp,

yes it is on a seperate sheet the "Sheetname" is "Diagramm1"

The problem is that I like to insert the diagramm to a bookmark in the Word Template file.

        Diagramm1.ChartArea.Copy

        .Selection.GoTo what:=-1, Name:="ChartLocation"     'late binding
        .Selection.Paste

"ChartLocation" is the bookmark to insert the chart

I will look into your link..

Thanks )

 

[arnelgp]

yes look in the Link, it is inserting the Image in the Bookmark.

 

[silentwolf]

yes look in the Link, it is inserting the Image in the Bookmark.

Hi,
unfortunatelly I am still getting the Runtime Error 438 with your suggestion of saving the Diagramm1

Object does not support Property or method.. well in my translation from German to English

Somehow my referenz to that object is not working properly...
What am I missing?

 

[June7]

This was the only thing I could get to work without having to save png file:

wkbExcel.Worksheets("Diagramm1").ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Copy

My chart is named "Chart 2" - with a space. Excel assigned it and I can't get it to accept change.

I tried declaring and setting a chart object as shown in https://stackoverflow.com/questions/22871774/copy-and-paste-an-excel-chart-with-a-vba-macro-in-word.

 

[arnelgp]

"Sheetname" is "Diagramm1"

goto vba and see what Chart number is "Diagramm1"
then use that name in your code (usually it is Chart1 or Chart2, etc).

 

[silentwolf]

Thanks guys for the reply.

I will try your suggestions..

 

[silentwolf]

Hi again,

this is what the vb Editor shows

Diagramm1(Diagramm1)

I have been trying all your suggestions but I either get the Runtime Error 438 or a runtimeError 9 , Index out of range.

I am not sure where I am doing it wrong...

Where can I check what name the actual Chart has?

First time I do something with a chart...

 

[arnelgp]

create new chart on new workbook and test it again.

 

[silentwolf]

Unfortunatelly same issue I can't seam to get the reference right.

I will keep trying..

Thanks anyway for your help!

 

[silentwolf]

I am not sure what I suppose to try again.

The worksheet name ="Diagramm1"
The chart when I click on it and assign a macro to it it tells me that the chart is also named "Diagramm1"

I have tried to reference the worksheet like
dim wksChart as Excel.Worksheet

set wksChart = wkbExcel.Worksheets("Diagramm1")

wksChart.Diagramm1.ChartArea.Copy

but also nothing I always get the runtime Error 438 Object not ...

I am about to finished with my ideas how I suppose to reference that Chart from Access--

 

[June7]

You tried syntax shown in post #6?
If you want to provide your files for analysis, follow instructions at bottom of my post.

 

[silentwolf]

Yes I did try your post too but got also an Error.. I think it was runtime Error 9 Out of scope or somthing down that line

Ok I will downsize it and put it on here.

Cheers

 

[silentwolf]

Hi I did attach a Folder with all relevant Documents maybe someone could take a look to see what I am missing to get this chart into the Word Dokument.

Hope this is correct how I attached the folder?


Many thanks

Albert

 

[June7]

First time I viewed workbook and selected Diagramm1 sheet, threw error: "The reference is not valid. References for titles, values, or sizes must be a single cell, row, or column." I saved workbook and next time I opened it, no error.

Code throws "subscript out of range" error. I cannot find chart name in your workbook. In my workbook, when I select chart I see chart name in name box next to formula box. This appears to be cause of error. Need to name the chart. Unfortunately, I don't know how to do that. When I created chart in my workbook, Excel assigned a name. Should be able to change name but I haven't figured that out either.

Exactly how did you create chart? My chart sits on worksheet. I can select it and move it. Your chart is not on worksheet - it is the worksheet.

I manually selected your chart, copied, pasted to Tabelle2. Now chart 'floats' on sheet and I see name. I changed code to reference this sheet. And my suggested code works!

wkbExcel.Worksheets("Tabelle1").ChartObjects("Diagramm 1").Activate
ActiveChart.ChartArea.Copy

 

[arnelgp]

i save your .dotx to .docx and use .docx in the code.
i need not explain but see the changes made in your code.
also some paths are changed to currentproject.path so you need
to change them back in the code.

Note this can be easily done using a Table and a Report in pure Access.

 

[silentwolf]

Hi,
hmm ok..I just created to chart by selecting the Data and then once I did with right click and then create the Chart.
The other time with the ribbon.

I am not really familiar with charts not really using them just wanted to be able to make it work in Access for later reference.

 

[silentwolf]

Hi arnelgp,

thanks for your work I will update and look at your code!

Note this can be easily done using a Table and a Report in pure Access.

Ok yes I guess that would be a better and simpler way doing so.

I will keep that in mind! But for me was just also a good practice to see if I can make it work.

Many thanks for your input!

Cheers!