When I try to change data in Series object I got next error
Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
Please Help!
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim oldCI As System.Globalization.CultureInfo = _
System.Threading.Thread.CurrentThread.CurrentCulture
System.Threading.Thread.CurrentThread.CurrentCulture = _
New System.Globalization.CultureInfo("en-US")
'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
'Add data to cells of the first worksheet in the new workbook.
oSheet = oBook.Worksheets(1)
Dim i As Integer
For i = 1 To 5
oSheet.Range("A" & CStr(i)).Value = i
oSheet.Range("B" & CStr(i)).Value = i * i
Next i
Dim oChart As Object
oChart = oBook.Sheets.Add(Type:=-4109)
oChart.ChartType = 74
oChart.Location(Where:=1, Name:="function")
oChart.PlotArea.Interior.ColorIndex = 2
Dim s1 As Object 'Excel.Series
s1 = oChart.SeriesCollection.NewSeries()
s1.XValues = oSheet.Range("A1:A5") ' THIS LINE PRODUCE ERROR
s1.Values = oSheet.Range("B1:B5")
'Save the Workbook and quit Excel.
oBook.SaveAs("c:\Book1.xls")
If Not oBook Is Nothing Then oBook.Close(False)
oExcel.Quit()
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
Error on export to Excel
CantStopGamin
mnaples12
BarrySumpter
Look at the code above. There are no text in the cells.
Dim i As Integer
For i = 1 To 5
oSheet.Range("A" & CStr(i)).Value = i
oSheet.Range("B" & CStr(i)).Value = i * i
Next i
s1.XValues = oSheet.Range("A1:A5")
s1.Values = oSheet.Range("B1:B5")
The error is a "Type Mismatch" because XValues is type of System.Array and oSheet.Range("A1:A5") is type of System.___ComObject.
The expression oSheet.Range("A1:A5").Value has System.Array type, so
s1.XValues = oSheet.Range("A1:A5").Value
works, but only with not large arrays
MKBender
Unfortunately this doesn't work with large arrays:
s1.XValues = oSheet.Range("A1:A500").Value
s1.Values = oSheet.Range("B1:B500").Value
Michael Morton
Add .value to the end of your range references
These return range objects:
s1.XValues = oSheet.Range("A1:A5") ' THIS LINE PRODUCE ERROR
s1.Values = oSheet.Range("B1:B5")
These return arrays of contents:
s1.XValues = oSheet.Range("A1:A5").Value
s1.Values = oSheet.Range("B1:B5").Value
DJ
Ricardo Francisco
Try the following line
s1.XValues = "='Sheet1'!R1C2:R5C2"
This formula sets the worksheet and the rows and columns to use for the XValues. 'R' stands for row and 'C' stands for column.
If you record a macro while creating a chart from your data the resulting code will yield a formula similar to this.