dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Thread: problems using VB.NET to create an Excel spreadsheet w/ graph

  1. #1
    Join Date
    Aug 2001
    Posts
    1,447

    problems using VB.NET to create an Excel spreadsheet w/ graph

    I am creating an excel spreadsheet in VB.NET. I can create the spreadsheet just fine, putting in the data values what I want. The next step is to get the VB.NET code to create a chart in the sheet. I got the Excel code to do this by running a macro while creating the chart I want, and it works fine in the spreadsheet, but when I put it into VB.NET code as below, I get the error described in the embedded comments.
    Code:
        '
        '
        '
        Public Sub createExcel5(ByVal symbol As String)
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim misValue As Object = System.Reflection.Missing.Value
            Dim xlWorkSheet1 As Excel.Worksheet
            Dim xlStockHLC As Integer = 88  ' code for the particular style chart I want
            '
            ' create the excel spreadsheet to put the data in
            '
            xlApp = New Excel.ApplicationClass
            xlWorkBook = xlApp.Workbooks.Add(misValue)        
            xlWorkSheet1 = xlWorkBook.Sheets("sheet1")
            xlWorkSheet1.Columns("A:A").NumberFormat = "$#,##0.00"   ' format the high price column as 2-decimals currency
            xlWorkSheet1.Columns("B:B").NumberFormat = "$#,##0.00"   ' format the low price column as 2-decimals currency
            xlWorkSheet1.Columns("C:C").NumberFormat = "$#,##0.00"   ' format the closing price column as 2-decimals currency
            For ix = 0 To nalocal - 1
                xlWorkSheet1.Cells(ix + 1, 1) = alocal(ix, 0)
                xlWorkSheet1.Cells(ix + 1, 2) = alocal(ix, 1)
                xlWorkSheet1.Cells(ix + 1, 3) = alocal(ix, 2)
            Next ix
            '
            ' create the chart (using Excel functions)
            '
            xlWorkSheet1.Range("A1:C" & CStr(nalocal)).Select()
            xlWorkSheet1.Range("C" & CStr(nalocal)).Activate()
            xlWorkSheet1.Shapes.AddChart.Select()
    
            '====== ERROR STATEMENTS AND DESCRIPTION ======
            '
            ' the following statment gets error message --- Public member 'SetSourceData' on type "Worksheet' not found
            ' but in a worksheet macro in Excel it works fine (I got it by recording a macro while creating the 
            ' chart that I want this VB.NET program to create)
            ' In the macro, the string "'Sheet1'!$A$1:$C$" & CStr(nalocal) is actually "'Sheet1'!$A$1:$C$81"
            ' but this is supposed to generalize it. NOTE: the value of nalocal is definitely not the problem;
            ' they are declared at the top of this module as Private alocal(1000, 3) As Single, nalocal As Integer
            '
            xlWorkSheet1.SetSourceData(Source:=xlWorkSheet1.Range("'Sheet1'!$A$1:$C$" & CStr(nalocal)))
            '
            ' Thinking that maybe Excel needs quote literals on the source string, I changed the above statement to 
            ' add quote literals to both sides of the string, like this:
            '
            xlWorkSheet1.SetSourceData(Source:=xlWorkSheet1.Range(Chr(34) & "'Sheet1'!$A$1:$C$" & CStr(nalocal) & Chr(34)))
            '
            ' and then I get the message --- Exception from HRESULT:0x800A03EC 
            ' rather than a statement that the method 'SetSourceData' doesn't exist
            '
            '========== END ERROR DESCRIPTION
    
    
            xlWorkSheet1.ChartType = xlStockHLC
            '
            ' save the spreadsheet, then release everything
            '
            xlWorkSheet1.SaveAs(FILEBASE & "MISC\daily analysis\" & txtEndDate.Text & "_" & symbol & "_5min.xlsx")
            xlWorkBook.Close()
            xlApp.Quit()
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
        End Sub
    I'd appreciate any advice on what I might be doing wrong.


    Thanks
    phinds
    vs2008, 3.5SP1 Version 9.0.21022.8 RTM

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: problems using VB.NET to create an Excel spreadsheet w/ graph

    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Aug 2001
    Posts
    1,447

    Re: problems using VB.NET to create an Excel spreadsheet w/ graph

    Quote Originally Posted by dglienna View Post
    Interesting resource and I appreciate your taking the time to post the link, but unfortunately it has NOTHING to do with my problem, since it's all about creating charts IN VB.NET forms, and what I'm trying to do is to use VB.NET to create an Excel spreadsheet and then use the Excel methods to create a chart in the Excel spreadsheet.
    phinds
    vs2008, 3.5SP1 Version 9.0.21022.8 RTM

  4. #4
    Join Date
    Aug 2009
    Location
    NW USA
    Posts
    173

    Re: problems using VB.NET to create an Excel spreadsheet w/ graph

    The Chart needs the SourceData not the Worksheet. Try something like this (or some reference to the Shapes Collection):
    Code:
    xlWorkSheet1.Shapes.item(1).SetSourceData(Source:=xlWorkSheet1.Range("'Sheet1'!$A$1:$C$" & CStr(nalocal)))

  5. #5
    Join Date
    Aug 2001
    Posts
    1,447

    Re: problems using VB.NET to create an Excel spreadsheet w/ graph

    Quote Originally Posted by Mur16 View Post
    The Chart needs the SourceData not the Worksheet. Try something like this (or some reference to the Shapes Collection):
    Code:
    xlWorkSheet1.Shapes.item(1).SetSourceData(Source:=xlWorkSheet1.Range("'Sheet1'!$A$1:$C$" & CStr(nalocal)))
    Mur16, thanks for this helpful reply. I tried all kinds of stuff with Shapes and things and although you are clearly pointing me in the right direction, I still couldn't get it. Lucky for me I found a sample on the internet that lays it out nicely and I post my version here in case it might be helpful to others.

    put the following at the top of the module (and don't forget to also set a new reference to the Microsoft excel Object Library)
    Imports Excel = Microsoft.Office.Interop.Excel

    This is the code to create an excel file, fill several columns and create an Excel chart using 3 of the columns

    Code:
        '
        '
        '
        Public Sub createExcelChart(ByVal symbol As String)
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim misValue As Object = System.Reflection.Missing.Value
            Dim xlWorkSheet1 As Excel.Worksheet
            Dim chartPage As Excel.Chart
            Dim xlCharts As Excel.ChartObjects
            Dim myChart As Excel.ChartObject
            '
            ' create the excel spreadsheet to put the data in
            '
            xlApp = New Excel.ApplicationClass
            xlWorkBook = xlApp.Workbooks.Add(misValue)               ' ???
            xlWorkSheet1 = xlWorkBook.Sheets("sheet1")
            xlWorkSheet1.Columns("B:B").NumberFormat = "$#,##0.00"   ' format the opening price column as 2-decimals currency
            xlWorkSheet1.Columns("C:C").NumberFormat = "$#,##0.00"   ' format the high price column as 2-decimals currency
            xlWorkSheet1.Columns("D:D").NumberFormat = "$#,##0.00"   ' format the low price column as 2-decimals currency
            xlWorkSheet1.Columns("E:E").NumberFormat = "$#,##0.00"   ' format the close price column as 2-decimals currency
            xlWorkSheet1.Columns("F:F").NumberFormat = "###,###,###" ' format the volume  column as a comma-formatted long
            For ix = 0 To nalocal5min - 1   ' GLOBAL ARRAY alocal5min HAS ALREADY BEEN FILLED
                xlWorkSheet1.Cells(ix + 1, 1) = alocal5min(ix).time
                xlWorkSheet1.Cells(ix + 1, 2) = alocal5min(ix).open
                xlWorkSheet1.Cells(ix + 1, 3) = alocal5min(ix).high
                xlWorkSheet1.Cells(ix + 1, 4) = alocal5min(ix).low
                xlWorkSheet1.Cells(ix + 1, 5) = alocal5min(ix).close
                xlWorkSheet1.Cells(ix + 1, 6) = alocal5min(ix).vol
            Next ix
            '
            ' create the chart (using Excel functions)
            '
            xlCharts = xlWorkSheet1.ChartObjects
            myChart = xlCharts.Add(350, 50, 500, 250)
            chartPage = myChart.Chart
            chartPage.SetSourceData(Source:=xlWorkSheet1.Range("'Sheet1'!$C$1:$E$" & CStr(nalocal5min)))
            chartPage.ChartType = Excel.XlChartType.xlStockHLC
            chartPage.Legend.Delete()
            '
            ' save the spreadsheet, then release everything
            '
            xlWorkSheet1.SaveAs(FILEBASE & "MISC\daily analysis\" & txtEndDate.Text & "_" & symbol & "_5min.xlsx")
            xlWorkBook.Close()
            xlApp.Quit()
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
        End Sub
    phinds
    vs2008, 3.5SP1 Version 9.0.21022.8 RTM

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)