locked
Error trying to create chart from image in excel vba RRS feed

  • Question

  • I'm trying to create a object using a file name of a temp image and then encode it to base64data to be transmitted to a web server, but i'm getting a error when trying to create the chart object. Here is the error code line: 

    .ChartObjects(1).Chart.Export Filename:=TempFilename, FilterName:="jpg"
    Here is the error: "Permission denied" and errors out.

    Wednesday, April 6, 2016 9:32 PM

All replies

  • Do you have access to TempFilename variable?

    ---

    There is also a bug (Excel 2010 or later) that sometimes if you don't select the Chart before exporting, the generated file will be an useless 0 byte.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Wednesday, April 6, 2016 10:12 PM
  • Do you have access to TempFilename variable?

    ---

    There is also a bug (Excel 2010 or later) that sometimes if you don't select the Chart before exporting, the generated file will be an useless 0 byte.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Yes i have access to the TempFilename variable. It works 95% of the time, but sometimes it randomly does not work. I'm trying to debug it so that it works all the time, but it usually errors out on the line of code above. Also 

    With .Shapes(MyChart)
                    .Width = PicWidth
                    .Height = PicHeight
                End With
               
                .Shapes(MyPicture).Copy
               
                With ActiveChart
                    .ChartArea.Select
                    .Paste
                End With
               
                .ChartObjects(1).Chart.Export Filename:=TempFilename, FilterName:="jpg"
                .Shapes(MyChart).Cut

    Thursday, April 7, 2016 1:00 PM
  • I'll change a little the focus of the subject so we can get to a better solution...

    ---

    I made a discovery some years ago: if when opening the workbook you select each chartobject, the export method won't fail. If I'm right, this is good news.

    I see that you create a temporary chart just to export the image, and then delete (cut) it. In order to this technique works, you can't delete your chartobject (maybe you can delete the chart, I see no problem) at the end. Just leave the chartobject in a temporary worksheet of your workbook.

    The code that you should put at your Workbook_Open event is like this:

    Private Sub Workbook_Open()
        FixExportBug
    End Sub
     
    Sub FixExportBug()
        Dim iWorksheet As Worksheet
        Dim iChartObject As ChartObject
        Dim OriginalSelection As Range
        Dim OriginalWorksheetVisibleState As XlSheetVisibility
         
        For Each iWorksheet In ThisWorkbook.Worksheets
            If iWorksheet.ChartObjects.Count = 0 Then GoTo Continue
            
            Set OriginalSelection = Selection
            
            With iWorksheet
                OriginalWorksheetVisibleState = .Visible
                .Visible = xlSheetVisible
                .Activate
                For Each iChartObject In .ChartObjects
                    iChartObject.Select
                Next iChartObject
                
                Application.Goto OriginalSelection
                .Visible = OriginalWorksheetVisibleState
            End With
        
    Continue:
        Next iWorksheet
    End Sub

    My friend said that if you put a Application.ScreenUpdating = False before this sub to prevent screen flickering, this might not work, but I have not tested yet.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Thursday, April 7, 2016 1:26 PM
  • I decided to go ahead and save a actual file in the folder where the workbook is contained, therefore i can encode that file path as a MSXML object to send to the web service for other things. For the most part it works fine. It's just one of those random instances where it gets hung up on the above code at the top. Have not tested the updated code yet. 

    When I open this excel workbook from another workbook. I'm getting errors sometimes when doing that. If i open the workbook by itself, most of the times it will work fine.

    What is your code above suppose to help to do?

    Thursday, April 7, 2016 2:45 PM
  • I'll change a little the focus of the subject so we can get to a better solution...

    ---

    I made a discovery some years ago: if when opening the workbook you select each chartobject, the export method won't fail. If I'm right, this is good news.

    I see that you create a temporary chart just to export the image, and then delete (cut) it. In order to this technique works, you can't delete your chartobject (maybe you can delete the chart, I see no problem) at the end. Just leave the chartobject in a temporary worksheet of your workbook.

    The code that you should put at your Workbook_Open event is like this:

    Private Sub Workbook_Open()
        FixExportBug
    End Sub
     
    Sub FixExportBug()
        Dim iWorksheet As Worksheet
        Dim iChartObject As ChartObject
        Dim OriginalSelection As Range
        Dim OriginalWorksheetVisibleState As XlSheetVisibility
         
        For Each iWorksheet In ThisWorkbook.Worksheets
            If iWorksheet.ChartObjects.Count = 0 Then GoTo Continue
            
            Set OriginalSelection = Selection
            
            With iWorksheet
                OriginalWorksheetVisibleState = .Visible
                .Visible = xlSheetVisible
                .Activate
                For Each iChartObject In .ChartObjects
                    iChartObject.Select
                Next iChartObject
                
                Application.Goto OriginalSelection
                .Visible = OriginalWorksheetVisibleState
            End With
        
    Continue:
        Next iWorksheet
    End Sub

    My friend said that if you put a Application.ScreenUpdating = False before this sub to prevent screen flickering, this might not work, but I have not tested yet.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Looks like my issues involves with having excel opening with more than 1 instance. For example, having two excel wookbook open at the same time. Any known issues?

    Thursday, April 7, 2016 4:35 PM
  • "What is your code above suppose to help to do?"

    My code "unbugs" your ChartObjects so the method Chart.Export won't bug anymore. The consequence of this is that you won't get the "Pemission denied" error anymore.

    ---

    "For example, having two excel wookbook open at the same time. Any known issues?"

    I'm not aware of the multiple instance problem.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Thursday, April 7, 2016 4:42 PM
  • When the chart is exported, does it save the image as read only or does that depends? Is there a way to save the image is read and write enable so that the picture does not show read only?
    Thursday, April 7, 2016 6:50 PM
  • I believe it is save and write.

    Any case, you can use the SetAttr to change its properties.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Thursday, April 7, 2016 7:18 PM
  • The first excel i'm using is a read only excel, I then open another excel from within that excel which opens a new excel window instances. If i was to export the chart would there be any reason for the exported image to be set as read only because of the first excel instance opened?

    But it's strange, why would it work most of the times, and at some points it doesn't work like it doesn't have permission lets say to write/read to or from that file?

    • Edited by vkid12 Thursday, April 7, 2016 7:35 PM
    Thursday, April 7, 2016 7:35 PM
  • I don't know the answer of your first question, I would have to test. But why are you creating a new Excel instance?

    When you say you're opening a new Excel aren't you meaning a new workbook?

    ---

    As of your second question, I confirmed that sometimes the Export method doesn't work (maybe a bug). But you can try to export again... In my tests, the second time always is able to copy. Try this workaround:

            On Error GoTo TryAgain
            .ChartObjects(1).Chart.Export Filename:=TempFilename, FilterName:="jpg"
            On Error GoTo 0
        End With
    Exit Sub TryAgain: DoEvents Tries = Tries + 1 If Tries > 5 Then MsgBox "Error - couldn't copy the image.", vbCritical Debug.Print 1 / 0 End if Resume End Sub


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Thursday, April 7, 2016 9:11 PM