Link to home
Start Free TrialLog in
Avatar of cmdolcet
cmdolcetFlag for United States of America

asked on

How to select Data from a different sheet inside an Excel Workbook

I am trying to get a range of cells from a sheet to populate my graph on a different sheet inside my excel workbook. When it hits the line "  chartRange = oWB.oSheet("Sheet1").Range("A1", "A136")" It throws an error "System.MissingMemberException: Public member osheet on type Workbook not found

What is wrong with that line of code?

 Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oRng As Excel.Range
        Dim ValueTemp As String
        Dim oChart As Excel.Chart
        Dim MyCharts As Excel.ChartObjects
        Dim MyCharts1 As Excel.ChartObject

        ' Start Excel and get Application object.
        oXL = CreateObject("Excel.Application")
        oXL.Visible = True

        ' Get a new workbook.
        oWB = oXL.Workbooks.Add
        oSheet = oWB.ActiveSheet

        'Add a new Sheet.
        oSheet = CType(oWB.Sheets.Add(Count:=1), Excel.Worksheet)

        'change page orientation
        oSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape


        oSheet = oWB.Worksheets.Item(2)
        Dim intRowLoc, intcurrentrow, intCurrentCol, intLoop, intOuterLoop, intInnerLoop As Integer
        Dim boolRowFound, boolNextRow As Boolean
        intcurrentrow = 1
        intCurrentCol = 1
        For intInnerLoop = 0 To 135 'Column4.Count - 1
            ValueTemp = (Column4(intInnerLoop)).ToString
            oSheet.Cells(intcurrentrow, intCurrentCol) = ValueTemp
            intcurrentrow += 1
        Next

        oSheet = oWB.Worksheets.Item(1)
        MyCharts = oSheet.ChartObjects
        MyCharts = oSheet.ChartObjects
        MyCharts1 = MyCharts.Add(200, 200, 300, 200)
        oChart = MyCharts1.Chart
        With oChart
            Dim chartRange As Excel.Range
            oSheet = oWB.Worksheets.Item(1)
            chartRange = oWB.oSheet("Sheet1").Range("A1", "A136")

            .SetSourceData(chartRange)
            .ChartType = Excel.XlChartType.xlXYScatterLines
            .HasLegend = False
            .ChartArea.Border.Weight = 1
            .ChartArea.Border.LineStyle = 1
        End With

Open in new window

Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

chartRange = oWB.oSheet("Sheet1").Range("A1", "A136")
should probably be the following:
chartRange = oSheet.Range("A1", "A136")
Avatar of cmdolcet

ASKER

Andy Ainscow...... Yes correct but I need to pull data from "sheet1" to populate my chart on "Sheet2"

How can I do this?
You are dealing with two sheets so declare two worksheet variables like oSheet1 and oSheet2 and then initialize them properly.
how do I bounce back and forth between both sheets?

So if I where to keep the same syntax I would define as your example above. (osheet and osheet2) but then how would the make the calls?

Any examples?
I am not familiar with .Net syntax to deal with Excel Application but it seems from your code that you are initializing the same variable oSheet over an over again and that's why I asked you to declare two variables.
ASKER CERTIFIED SOLUTION
Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial