test.vbs文件中vb代码如下:
dim args
set args = wscript.arguments
Dim objXL
Set objXL = CreateObject("Excel.Application")
With objXL
.Workbooks.Open (args(0))
.Application.Run "Bar"
.ActiveWorkbook.Save
.Workbooks.Close
.Application.Quit
End With
Set objXL = Nothing
解释: args(0)是接收下表为0的参数,也就是第一个参数
.Application.Run "Bar"的 bar 是excel文件中宏的名称
java调用vb
Runtime.getRuntime().exec("cmd /c start f:/test.vbs f:/test.xls")
解释: 运行f盘下test.vbs文件,参数为f盘test.xls,在vb文件中.Workbooks.Open (args(0))读取的args(0)则是test.xls。
运行后发现有个问题,图表上会多出几个系列,如本来应该显示6个系列,但是图上却显示12个,翻倍的。由于对vb不熟,花了1天时间才搞明白,先看下边代码吧,这是excel中的宏,生成的图为柱状图:
Sub Bar(chartype As String, title As String)
ActiveSheet.Shapes.AddChart.Select
If chartype = "3D" Or chartype = "3d" Then
ActiveChart.ChartType = xl3DColumn
End If
If IsNull(title) Or IsEmpty(title) Then
Else
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = title
End If
Dim columnNum
columnNum = Range("A1").End(xlToRight).Columns.Column
‘下边 这个循环加上去以后就不会出现系列翻倍的问题了
For a = 1 To ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(1).Select
Selection.Delete
Next a
For i = 2 To columnNum
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i - 1).Name = "=" & ActiveSheet.Name & "!$" & Chr(i + 64) & "$" & 1
ActiveChart.SeriesCollection(i - 1).Values = Range(Cells(2, i), Cells(Range(Chr(i + 64) & "2").End(xlDown).Row, i))
Next i
ActiveChart.SeriesCollection(columnNum - 1).XValues = Range(Cells(2, 1), Cells(Range("A2").End(xlDown).Row, 1))
End Sub
可能比较啰嗦,但是本人研究了一段时间才搞出来,网上比较难找