Consulting

Results 1 to 15 of 15

Thread: Excel 2010 chart's error

  1. #1
    VBAX Tutor Benzadeus's Avatar
    Joined
    Dec 2008
    Location
    Belo Horizonte, Brazil
    Posts
    271
    Location

    Excel 2010 chart's error

    Hello all,

    I use a code to export a chart object to a file:
    [VBA]Sub CarregarFigura(img, sGráfico As String)
    Dim sArquivo As String

    sArquivo = ThisWorkbook.Path & "\temp.bmp"
    Sheets("Gráficos").ChartObjects(sGráfico).Chart.Export Filename:=sArquivo, FilterName:="BMP"

    With img
    .Picture = LoadPicture(sArquivo)
    End With

    Kill sArquivo
    End Sub[/VBA]

    I get an error 481: Invalid picture (translated from portuguese) at line:
    [VBA] .Picture = LoadPicture(sArquivo)
    [/VBA]

    Well, when I go to the sheet that has the charts and CLICK them manually, one by one, the same code works thereafter. Anyone had this issue ever?
    ---
    Felipe Costa Gualberto
    Microsoft Excel MVP
    http://www.ambienteoffice.com.br

  2. #2
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,623
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  3. #3
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,598
    Location
    I have not had that issue, though I think I have seen a previous post somewhere that was reminiscent of your issue. I will see if I can locate it in case there were a resolution.
    For the moment however, have you tried exporting as a different format, or specifically setting ScreenUpdating to true before you run this code. Additionally, do you have SP1 installed?
    Be as you wish to seem

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,961
    Location
    You set img as a Variant. What type of object is it? Where is it located, ActiveSheet, Userform, etc.?

  5. #5
    VBAX Tutor Benzadeus's Avatar
    Joined
    Dec 2008
    Location
    Belo Horizonte, Brazil
    Posts
    271
    Location
    Thank you for the aswers. I attached an example, I'd like you to test this Workbook.
    I'd like to add that this error occured at 3 different PCs: 2 with Excel 2010-64bit and 1 with Excel 2010-32bit.

    @mancubus
    I already use this link's code (adapted) on my procedure.

    @Aflatoon
    I tried others formats too, having the same issue. My Office is not SP1. I already run this code with ScreenUpdate=False. The chart object is at a hidden worksheet.

    @Kenneth Hobs
    The img is a Image control, at a UserForm.
    I set that as Variant because I use the same code to put the image on a UserForm, but that's another history.
    Attached Files Attached Files
    ---
    Felipe Costa Gualberto
    Microsoft Excel MVP
    http://www.ambienteoffice.com.br

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,897
    Location
    Your attachment works fine for me (Vista, Excel 2010, 32 bit)

    Paul
    Attached Images Attached Images

  7. #7
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,598
    Location
    And also for me (Windows XP, Office 2010 32bit with SP1) - I tested all the names in the dropdown just to be sure.

    Have you tested the exported images to see if you can open them in a typical image viewer without error?
    Be as you wish to seem

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,303
    Location
    And here, Windows 7 Ultimate, Excel 2010 SP1.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,598
    Location
    This is the post I was thinking of, which I mentioned earlier.
    Be as you wish to seem

  10. #10
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,961
    Location
    Based on the link that Aflatoon posted, set your code to Select the chart object first.

  11. #11
    VBAX Tutor Benzadeus's Avatar
    Joined
    Dec 2008
    Location
    Belo Horizonte, Brazil
    Posts
    271
    Location
    Thank for your time testing it.
    I attached another Workbook, this time I bet you'll get some trouble trying tot load it

    @Aflatoon
    That topic is exactly what I needed. I'll spend some time studying it.
    Attached Files Attached Files
    ---
    Felipe Costa Gualberto
    Microsoft Excel MVP
    http://www.ambienteoffice.com.br

  12. #12
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,598
    Location
    Does this version work for you?
    Attached Files Attached Files
    Be as you wish to seem

  13. #13
    VBAX Tutor Benzadeus's Avatar
    Joined
    Dec 2008
    Location
    Belo Horizonte, Brazil
    Posts
    271
    Location
    I created a workaround... with this code, I got it working (put on Workbook Open event):

    [VBA]Private Sub Workbook_Open()
    Travar
    Application.WindowState = xlMaximized
    LoopGráficos
    End Sub

    Sub LoopGráficos()
    Dim ws As Worksheet
    Dim co As ChartObject

    Set ws = Sheets("Gráficos")

    With ws
    .Visible = xlSheetVisible
    .Activate
    For Each co In ws.ChartObjects
    co.Select
    Next co
    .Range("A1").Select
    End With

    Sheets("Início").Activate
    End Sub[/VBA]

    As you can see, I selected all charts when the workbook was opened. Worked for me.
    ---
    Felipe Costa Gualberto
    Microsoft Excel MVP
    http://www.ambienteoffice.com.br

  14. #14
    VBAX Tutor Benzadeus's Avatar
    Joined
    Dec 2008
    Location
    Belo Horizonte, Brazil
    Posts
    271
    Location
    @Aflatoon
    Your version did not work. I had to scroll all graphs object in order to make it work.
    ---
    Felipe Costa Gualberto
    Microsoft Excel MVP
    http://www.ambienteoffice.com.br

  15. #15
    VBAX Tutor Benzadeus's Avatar
    Joined
    Dec 2008
    Location
    Belo Horizonte, Brazil
    Posts
    271
    Location
    This is the final version of my workaround:

    [VBA]Private Sub Workbook_Open()
    If CLng(Val(Application.Version)) = 14 Then
    LoopGráficos Sheets("Gráficos")
    'LoopGráficos Sheets("Gráficos2")
    'LoopGráficos Sheets("Gráficos3")
    '...
    'Loop in every sheet that has a chart object
    'that'll be used on a UserForm
    End If
    End Sub

    Sub LoopGráficos(ws As Worksheet)

    Dim co As ChartObject
    Dim blScreenUpdating As Boolean
    Dim sv As XlSheetVisibility

    blScreenUpdating = Application.ScreenUpdating
    Application.ScreenUpdating = True

    With ws
    sv = .Visible
    .Visible = xlSheetVisible
    .Activate
    For Each co In .ChartObjects
    co.Select
    Next co
    .Visible = sv
    End With

    Application.ScreenUpdating = blScreenUpdating

    End Sub[/VBA]

    I tought about scrolling the window to "view" each of the chart objects, but I think that'll not be necessary.

    Also, notice that the code runs only for Excel 2010 versions (32 and 64 bit).
    ---
    Felipe Costa Gualberto
    Microsoft Excel MVP
    http://www.ambienteoffice.com.br

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •