根据msdn推测,有些项目实际上不在vb中。我发现这种情况有好几次。有趣的是,这些属性或方法在vba中可用,但在vb.net中不可用。为了解决这个问题,我实际上在正在编辑的Excel文件中创建了一个临时宏,其中包含了一些代码来处理我无法处理的事情,然后运行它。所以,它实际上是在vb.net中以字符串形式编写并在Excel中执行的vb a代码。
这是那个项目的代码。也许会有帮助:
'---------------------------------------------------------Nitrate Graph
'
'
'Build the Nitrate graph
Dim objExcel As New Excel.Application 'create an Excel application object
Dim objWrkBk As Excel.Workbook 'create a workbook
Dim objSheet As Excel.Worksheet 'create a worksheet
Dim Range As Excel.Range 'create a range
Dim Chart As Excel.Chart 'create a chart
Dim chartObjects As Excel.ChartObjects = Nothing 'create a chartObjects instance
Dim existingChartObject As Excel.ChartObject = Nothing 'create a chartObject instance
objExcel = New Excel.Application 'start the Excel application
objWrkBk = objExcel.Workbooks.Add 'add the workbook to this excel application
objSheet = objWrkBk.Sheets.Add 'add the worksheet to the workbook object
objSheet = objWrkBk.Sheets(1) 'objSheet is the first sheet in the workbook
Chart = objExcel.Charts.Add 'add a chart to the Excel application
'objExcel.Visible = True
'''''''''''''''''''''''''''''''''''''''''''''
'Create a macro to be run within the excel '
'application to delete a series that is '
'otherwise undeletable. '
'''''''''''''''''''''''''''''''''''''''''''''
Dim macro As VBIDE.VBComponent 'create a macro object
Dim sCode As String 'create a string to hold the macro programming
retry: 'A point at which the macro creation is retried if unsuccessful
'Add in a macro so that Excel will delete the day as the series.
Try
macro = objWrkBk.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule) 'place the macro into the workbook
Catch ex As Exception 'if an error occurs, catch it and do the following:
'If the user has not enabled access to Visual Basic a Run-time error will occur.
'Details on this error can be found here: http://support.microsoft.com/kb/282830/en-us
'Run-time error '6068': Programmatic Access to Visual Basic Project is not trusted
Dim Response As Integer
'if the error is caught, a message box will display with the following text instructions on how to fix the problem.
Response = MessageBox.Show("Access is denied from Microsoft Excel. You need to do the following:" & vbCr & vbCr & _
" 1. Open Microsoft Office Excel 2007. Click the Microsoft Office" & vbCr & _
" button, and then click Excel Options." & vbCr & vbCr & _
" 2. Click the Trust Center tab, and then click Trust Center Settings." & vbCr & vbCr & _
" 3. Click the Macro Settings tab, click to select the Trust access to " & vbCr & _
" the VBA project object model check box, and then click OK." & vbCr & vbCr & _
" 4. Click OK. " & vbCr & vbCr & _
"Restart IX Report Gen.", "Security Access Denied", MessageBoxButtons.RetryCancel, MessageBoxIcon.Exclamation)
If Response = 4 Then 'If the user selected to retry they will be taken to the retry point above
GoTo retry
Else
End 'If the user cancels the program simply ends.
End If
End Try
'The macro code is put into the sCode string.
'Line by line, the code below is described:
'Sub Macro2()
'ActiveSheet.ChartObjects(""Chart 1"").Activate 'Activate the chart in the sheet
'ActiveChart.SeriesCollection(1).Select 'Select the chart's 1st data series
'Selection.Delete 'Delete the chart's 1st data series
'End Sub 'End the macro
sCode = "Sub Macro2()" & vbCr & "ActiveSheet.ChartObjects(""Chart 1"").Activate" _
& vbCr & "ActiveChart.SeriesCollection(1).Select" _
& vbCr & "Selection.Delete" _
& vbCr & "End Sub"
'The sCode string is placed into the empty macro
macro.CodeModule.AddFromString(sCode)
'Populate the Nitrate Avegare Array
Dim HeadingArray(1, 1) As String 'A HeadingArray is created to place headings into the Excel sheet
HeadingArray(0, 0) = "Day" 'Heading 1 is Day
HeadingArray(0, 1) = "Nitrate Avg" 'Heading 2 is Nitrate Avg
Range = objSheet.Range("A1", "B1") 'Range is assigned to the cells A1 and B1
Range.Value = HeadingArray 'The value of the range is set to the value of the HeadingArray. A1=Day and B1=Nitrate Avg
'Range is now assigned to the cells from A2 to the B column and 'as many rows as are in the reporting month plus 1
'to accomodate the heading row.
Dim BNum As Integer = ArrayRows(DailyNitrateAverageArray)
Dim NumArray(BNum - 1, 1) As Double
'Transfer the string array's values into a double array
For i As Integer = 0 To BNum - 1
NumArray(i, 0) = DailyNitrateAverageArray(i, 0)
NumArray(i, 1) = DailyNitrateAverageArray(i, 1)
Next
Range = objSheet.Range("A2", "B" & BNum + 2)
Range.Value = NumArray 'The value of Range is now assigned the values of DailyAverageArray
Range = objSheet.Range("A1", "B" & BNum + 2) 'Range is reassigned to select the whole edited area from A1 to B#
'This is the title later used for the chart. An example of what the title would
'say if it were April is, Daily Nitrate Averages for April
Dim Title As String = "Daily Nitrate Averages for " & Month & " - Train " & GraphNumber + 1
Chart.Location(Excel.XlChartLocation.xlLocationAsObject, objSheet.Name) 'The chart is placed into the worksheet
' Get the ChartObjects collection for the sheet.
chartObjects = objSheet.ChartObjects() 'chartObjects is assigned to this sheet
' Get the chart to modify. This is the first item in chartObjects
existingChartObject = chartObjects.Item(1)
'Custom settings are assigned to the chart using the With keyword
With existingChartObject
.Chart.ChartType = Excel.XlChartType.xlLine 'This is a line graph
.Chart.SetSourceData(Range, PlotBy:=Excel.XlRowCol.xlColumns) 'we plot by the columns selected--this gives us our headings as names
.Chart.HasLegend = True 'give the chart a legend
.Chart.HasTitle = True 'tell the chart it has a title
.Chart.ChartTitle.Text = Title 'give the chart its title (declared and assigned earlier)
.Shadow = True 'Give the chart a shadow effect
.Chart.ChartArea.Shadow = True 'Give the chart area a shadow effect
.Chart.ChartArea.Format.Shadow.Style = MsoShadowStyle.msoShadowStyleOuterShadow 'assign an outer shadow style
.Chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop 'position the legend at the top of the chart
.Chart.ChartArea.Format.Glow.Radius = 10 'add a glow with a radius of 10
.Chart.ChartArea.Format.Glow.Color.RGB = RGB(90, 90, 90) 'set the color of the glow to a gray color
.Chart.ChartArea.Format.Fill.Visible = True 'make the fill on the chart visible
'give the chart major gridlines on it's x axis
.Chart.Axes(Excel.XlAxisGroup.xlPrimary).HasMajorGridlines = True
End With 'Finish working with existingChartObject
'from the perspective of the sheet, set the Width and Height to 500 X 300
objSheet.ChartObjects(1).Width = 500
objSheet.ChartObjects(1).Height = 300
'Format the Chart's 2nd data series
With existingChartObject.Chart.SeriesCollection(2)
.Shadow = True 'give it a shadow
.Format.Shadow.Style = MsoShadowStyle.msoShadowStyleOuterShadow 'set the shadow to be an outer shadow
.Format.Shadow.Transparency = 0.5 'make the shadow 50% transparent
End With 'stop working with the existingChartObject.Chart.SeriesCollection(2)
objExcel.Run("Macro2") 'Run Macro2 to delete the unecessary series
