Getting a chart name

[AccessShell]

I am using Office 2003 Pro. I need to use the Chart Name in some VB6 code. I ran in Excel "Record New Macro". I can record the code to update the chart. When I copy the code into my VB6 program I get no error but the update does not work. I know I am connected to the Excel workbook because I can update cells.

Any insight would be appreciated.

Thanks

[Elroy]

You're lucky, just some code I happen to have lying around:

Option Explicit

Private Sub Form_Click()
    Dim xls As Object
    Dim wbk As Object
    Dim wsh As Object
    Dim cht As Object
    Dim sFileSpec As String
    '


    sFileSpec = "C:\Users\Elroy\Desktop\test.xlsm"


    '
    Set xls = CreateObject("Excel.Application")
    Set wbk = xls.Workbooks.Open(sFileSpec)


    For Each wsh In wbk.Worksheets
        Debug.Print wsh.Name; "                                          (Left,Top)"
        For Each cht In wsh.ChartObjects
            Debug.Print "    "; Format$(cht.Index); "  "; Left$(cht.Name & Space$(50), 50); Right$(Space$(20) & Format$(cht.Left, "#0.000"), 10); Right$(Space$(20) & Format$(cht.Top, "#0.000"), 10)
        Next cht
    Next wsh



    ' Clean up.
    Set cht = Nothing
    Set wsh = Nothing
    wbk.Close False
    Set wbk = Nothing
    xls.quit
    Set xls = Nothing



End Sub

Be sure to change the sFileSpec to your file. That's just one I happen to have handy.

Enjoy,
Elroy

EDIT1: Also, just as an FYI, I tend to always use late-binding for automation, so there's no need for any references to make that work. Also, with what I've given you, I'm assuming that you'll figure out what you actually need from there.

EDIT2: Also, if you grabbed it seconds after I posted it, grab it again. I left a line of code in the loop that would have given you problems (now fixed).

EDIT3: BTW, in Office 2010, you can rename these charts from the ribbon...



I often rename them to my liking in the VBA, but sometimes it's nice to just rename them immediately after they're created (by hand). I'm not sure if that's available in Office 2003 or not.

[AccessShell]

Thank you Elroy. It work like a charm