Using VBA Macro a Simple Example

    Hi All

     

               I am posting a small example of VBA Scripting to Export a chart into Excel sheet.

     

              I got the script Here.

     

    Paste the below script in module editor Ctrl +M

     

     

     

     

    1. Sub ExcelExpwCaption 
    2.      'Set the path where the excel will be saved  
    3.      filePath = "C:\Test.xls" 
    4.  
    5.      'Create the Excel spreadsheet  
    6.      Set excelFile = CreateObject("Excel.Application") 
    7.      excelFile.Visible = true 
    8.      'Create the WorkBook 
    9.      Set curWorkBook = excelFile.WorkBooks.Add 
    10.      'Create the Sheet 
    11.      Set curSheet = curWorkBook.WorkSheets(1) 
    12.  
    13.      'Get the chart we want to export 
    14.      Set tableToExport = ActiveDocument.GetSheetObject("CH01") 
    15.      Set chartProperties = tableToExport.GetProperties 
    16.      tableToExport.CopyTableToClipboard true 
    17.  
    18.      'Get the caption 
    19.      chartCaption = tableToExport.GetCaption.Name.v 
    20.      'MsgBox chartCaption 
    21.  
    22.      'Set the first cell with the caption 
    23.      curSheet.Range("A1") = chartCaption 
    24.      'Paste the rest of the chart 
    25.      curSheet.Paste curSheet.Range("A2") 
    26.      excelFile.Visible = true 
    27.  
    28.      'Save the file and quit excel 
    29.      curWorkBook.SaveAs filePath 
    30.      curWorkBook.Close 
    31.      excelFile.Quit 
    32.  
    33.      'Cleanup 
    34.      Set curWorkBook = nothing 
    35.      Set excelFile = nothing 
    36. End Sub 

     

    Change the properties (Image attached).

     

    set the button properties (Image attached).

     

    Add or Edit the script with the Object Id of the chart which you need to Print.

     

    Click button to see the result.

     

    It is a very very basic example for newbies I will come up with some new and complex samples once I have familiar with macros.