How to create bar graph in excel automatically by following same excel file data

See more:

VB

VB12

Hi friends,

Here i have problem for creating bar graph in excel automatically by following same excel file data. The needed flow of my program is:

1) Open and read the targeted table in file "C:\Users\Thaneskumar\Desktop\Copy of SBL_Release_Yield Loss .xlsx".

2) Create another excel file and paste the table into it. The location is "C:\Users\Thaneskumar\Desktop\test".

3) Then create a bar graph in the same "test.xlsx" file by referring the table were copied.

i'm successfully done 1 and 2 steps. But the step 3 is really challenging. I try to make coding and search in internet also, i does not get any solution to solve it.

Thus anyone can help me please. My coding is shows in below.

Thanks.

Regards,
Thanes

Code:

Imports System
Imports System.IO
Imports System.Data
Imports System.Reflection
Imports System.Drawing
Imports Excel = Microsoft.Office.Interop.Excel

Module Module1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook, xlWorkBook2 As Excel.Workbook
    Dim xlWorkSheet, xlWsheet2 As Excel.Worksheet
    Dim xlSourceRange, xlDestRange As Excel.Range
    Dim ExcelApp As Excel.Application
    Dim ExcelSheet As Excel.Worksheet
    Dim MyDate As String
    Dim MyMonth As String
    Dim MyYear As String
    Dim Mydirectory As String
    Dim MyExtension As String
    Dim MyFileName As String
    Public R As Integer
    
    Sub Main()

        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Users\Thaneskumar\Desktop\Copy of SBL_Release_Yield Loss Update_HL_20150813072418.xlsx")
        MyDate = Format(Now, "dd")
        MyMonth = Format(Now, "MM")
        MyYear = Format(Now, "yyyy")
        Mydirectory = "C:\Users\Thaneskumar\Desktop\test"
        MyExtension = ".xlsx"
        MyFileName = Mydirectory + MyDate + MyMonth + "_" + MyYear + MyExtension
        ExcelApp = CreateObject("Excel.Application")
        xlWorkBook2 = ExcelApp.Workbooks.Add
        ExcelSheet = xlWorkBook2.Worksheets(1)

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("SBL Trend(daily)")
        '~~> Set the destination worksheet
        xlWsheet2 = xlWorkBook2.Sheets("Sheet1")

        '~~> Set the source range
        xlSourceRange = xlWorkSheet.Range("A18:Q22")
        ''~~> Set the destination range
        xlDestRange = xlWsheet2.Range("B3")

        '~~> Copy and paste the range
        xlSourceRange.Select()
        xlSourceRange.Copy()
        xlWorkBook.Close()
        xlWorkBook = Nothing
        xlDestRange.Select()
        xlDestRange.PasteSpecial()
        generategraph()
        xlWorkBook2.SaveAs(MyFileName)
        xlWorkBook2.Close()
        xlWorkBook2 = Nothing
    End Sub
    Public Sub generategraph()
       
        Console.WriteLine("Generating Auto Report")
        xlWsheet2 = xlWorkBook2.Worksheets(1)
        'rename the sheet
        xlWsheet2.Name = "test"
        xlWsheet2.Range("A1:AZ400").Interior.ColorIndex = 2
        xlWsheet2.Range("A1").Font.Size = 12
        xlWsheet2.Range("A1").Font.Bold = True
        xlWsheet2.Range("A1:I1").Merge()
        xlWsheet2.Range("A1").Value = "SBL Trend(daily)"
        xlWsheet2.Range("A1").EntireColumn.AutoFit()
        'format headings
        xlWsheet2.Range("A3:R3").Font.Color = RGB(255, 255, 255)
        xlWsheet2.Range("A3:R3").Interior.ColorIndex = 5
        xlWsheet2.Range("A3:R3").Font.Bold = True
        xlWsheet2.Range("A3:R3").Font.Size = 10

''''''''''''Here i needs Help''''''''''''''''

End Sub
End Module