Save image of table

Hi,

I am trying to save an image of an excel table. I am getting the following error: Run-time Error '438': Object doesn't support this property or method
It's strange because when i am using a similar code to save the image of a chart it works totally fine, but I am getting an error in case writing a code to save image of table.
Here is the code that brings the above mentioned error:

Sub Table6()
 
    Dim objTable As TableObject
    Dim myTable As Table
 
    Set objTable = Sheets("Sheet6").TableObjects("Table6")
    Set myTable = objTable.Table
 
    myFileName = "name.png"
    On Error Resume Next
    Kill ThisWorkbook.Path & "" & myFileName
    On Error GoTo 0
 
    myChart.Export Filename:=ThisWorkbook.Path & "" & myFileName, Filtername:="PNG"
 
      
End Sub

Do you know what could cause this issue?
Appreciate your help much!
Thanks!
Cavo

Re: Save image of table

Quote from pike;784908

Hi,
The concept is to copy the table to a chart of the same size then create the picture for the chart

try..

Code

Sub test()
    Dim rgExp As Range: Set rgExp = Sheets("Sheet6").Range("Table6[#All]")
    rgExp.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    With Sheets("Sheet6").ChartObjects.Add(Left:=rgExp.Left, Top:=rgExp.Top, Width:=rgExp.Width, Height:=rgExp.Height)
        .Name = "MyChartTablePicture"
        .Activate
    End With
    with Sheets("Sheet6")
        .Paste
        .ChartObjects("MyChartTablePicture").Chart.Export ThisWorkbook.Path & "\MyNewPicture.png", Filtername:="png"
        .ChartObjects("MyChartTablePicture").Delete
   end with
End Sub

Display More

Hi Pike,

Thank you so much. I tried your code, the macro itself works however the excel table which has names and numbers in it, is being saved as a completely white table with no info in it.

What could be missing here?

Thanks,
Cavo

Re: VBA Run-time error 438

Quote from richadj4;784906

Well, Haven't tested this is any way, but at a glance your final line "mychart.export" - you haven't set mychart to anything anywhere..............

Yes, you are right.Is this a correct way of writing it?
Dim myChart As Table or
Dim myTable As Table

Basically i want to save an image of an Excel table.

Re: Save image of table

Quote from pike;784908

Hi,
The concept is to copy the table to a chart of the same size then create the picture for the chart

try..

Code

Sub test()
    Dim rgExp As Range: Set rgExp = Sheets("Sheet6").Range("Table6[#All]")
    rgExp.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    With Sheets("Sheet6").ChartObjects.Add(Left:=rgExp.Left, Top:=rgExp.Top, Width:=rgExp.Width, Height:=rgExp.Height)
        .Name = "MyChartTablePicture"
        .Activate
    End With
    with Sheets("Sheet6")
        .Paste
        .ChartObjects("MyChartTablePicture").Chart.Export ThisWorkbook.Path & "\MyNewPicture.png", Filtername:="png"
        .ChartObjects("MyChartTablePicture").Delete
   end with
End Sub

Display More

Thanks Pike. I checked again the table and chart names are correct. The actual macro works - but what it does it pastes the full picture in the file on that excel tab itself, but it's pasting only empty picture in the folder where i need it to be.

Sorry I cannot share the file, but if you would give me further directions i will test again.

basically we need that picture that is pasting on the excel tab to be pasted in the file instead. What could be the reason?

Re: Save image of table

Quote from pike;784914

and don't use the delete shapes

Code

[CODE]Sub test()
    Dim rgExp As Range: Set rgExp = Worksheets("Sheet6").Range("Table6[#All]")
    rgExp.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    With Worksheets("Sheet6").ChartObjects.Add(Left:=rgExp.Left, Top:=rgExp.Top, Width:=rgExp.Width, Height:=rgExp.Height)
        .Name = "MyChartTablePicture"
        .Activate
    End With
    ActiveChart.Paste
    With Worksheets("Sheet6")
        .ChartObjects("MyChartTablePicture").Chart.Export ThisWorkbook.Path & "\MyNewPicture.png", Filtername:="PNG"
        .ChartObjects("MyChartTablePicture").Delete
         End With
End Sub

Display More

[/CODE]

Thanks.I just tried your last code. It's bringing a run-time error 1004 saying "Copy picture of range class failed"

Re: Save image of table

Thanks for sending. I see your code works perfectly fine in your file.
When i am inputting the same code in my file and changing the worksheet and table names to reflect the correct ones, still getting the run-time error 1004. Don't know what is wrong with my file. I am not allowed to share my file - i am very sorry!
But what could bring this error?
Error is showing to be for the line: rgExp.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

Re: Save image of table

Quote from pike;784920

can you post the complete code you are using?

Hi Pike,

It finally worked! Thank you so much! I used your code below. Your code now works perfectly fine. As i had mentioned earlier for some reason it was not working earlier on that particular file, but was working in other files. I tried changing the name of the worksheet on the file i was working on. So i completely changed the Excel tab name, and afterwards there was no problem with the code. I think probably in the memory the old name of the worksheet or tab was confusing the code. Anyways i am very happy now and thankful! Great job!

I have another question related to VBA, specifcally i am sending an excel report via VBA code, just with one click to an OUtlook Email. The problem is my outlook signature is showing up right at the top of the email body instead of at the end. SHould i open a new post for this new question or we can go from here?

Sub test()
    Dim rgExp As Range
   Set rgExp = Worksheets("Table").Range("Table3[#All]")
    rgExp.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    With Worksheets("Table").ChartObjects.Add(Left:=rgExp.Left, Top:=rgExp.Top, Width:=rgExp.Width, Height:=rgExp.Height)
        .Name = "MyChartTablePicture"
        .Activate
    End With
    ActiveChart.Paste
    With Worksheets("Table")
        .ChartObjects("MyChartTablePicture").Chart.Export ThisWorkbook.Path & "\MyNewPicture.png", Filtername:="PNG"
        .ChartObjects("MyChartTablePicture").Delete
    End With
End Sub

Thanks!
Nanka

Re: Save image of table

Great, thanks!

Re: Save image of table

If I would also post this answer in other forum- should i always reference the website link? I should not just show the code, right?