Reffering to Excel Chart from Access (1 Viewer)

silentwolf

Active member
Local time
Today, 16:28
Joined
Jun 12, 2009
Messages
545
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"
Code:
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

Code:
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
 

silentwolf

Active member
Local time
Today, 16:28
Joined
Jun 12, 2009
Messages
545
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.

Code:
        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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:28
Joined
May 7, 2009
Messages
19,169
yes look in the Link, it is inserting the Image in the Bookmark.
 

silentwolf

Active member
Local time
Today, 16:28
Joined
Jun 12, 2009
Messages
545
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

AWF VIP
Local time
Today, 15:28
Joined
Mar 9, 2014
Messages
5,423
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:28
Joined
May 7, 2009
Messages
19,169
"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

Active member
Local time
Today, 16:28
Joined
Jun 12, 2009
Messages
545
Thanks guys for the reply.

I will try your suggestions..
 

silentwolf

Active member
Local time
Today, 16:28
Joined
Jun 12, 2009
Messages
545
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:28
Joined
May 7, 2009
Messages
19,169
create new chart on new workbook and test it again.
 

silentwolf

Active member
Local time
Today, 16:28
Joined
Jun 12, 2009
Messages
545
Unfortunatelly same issue I can't seam to get the reference right.

I will keep trying..

Thanks anyway for your help!
 

silentwolf

Active member
Local time
Today, 16:28
Joined
Jun 12, 2009
Messages
545
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

AWF VIP
Local time
Today, 15:28
Joined
Mar 9, 2014
Messages
5,423
You tried syntax shown in post #6?
If you want to provide your files for analysis, follow instructions at bottom of my post.
 

silentwolf

Active member
Local time
Today, 16:28
Joined
Jun 12, 2009
Messages
545
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

Active member
Local time
Today, 16:28
Joined
Jun 12, 2009
Messages
545
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
 

Attachments

  • SendChartToWord.zip
    40 KB · Views: 201

June7

AWF VIP
Local time
Today, 15:28
Joined
Mar 9, 2014
Messages
5,423
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:28
Joined
May 7, 2009
Messages
19,169
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.
 

Attachments

  • SendChartToWord.zip
    67.6 KB · Views: 206

silentwolf

Active member
Local time
Today, 16:28
Joined
Jun 12, 2009
Messages
545
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

Active member
Local time
Today, 16:28
Joined
Jun 12, 2009
Messages
545
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!
 

Users who are viewing this thread

Top Bottom