cmdolcet
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.MissingMemberExcep tion: Public member osheet on type Workbook not found
What is wrong with that line of code?
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
ASKER
Andy Ainscow...... Yes correct but I need to pull data from "sheet1" to populate my chart on "Sheet2"
How can I do this?
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.
ASKER
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
should probably be the following:
chartRange = oSheet.Range("A1", "A136")