首先要引用Microsoft Excel Object Library.
然后,在vbScript中这样写:
Dim ExcelSheet
Set ExcelSheet = CreateObject("Excel.Sheet")
创建了excel的object后,就可以对其进行操作了。
比如,
' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True
' Place some text in the first cell of the sheet.
ExcelSheet.ActiveSheet.Cells(1,1).Value = "This is column A, row 1"
' Save the sheet.
ExcelSheet.SaveAs "C:\DOCS\TEST.XLS"
' Close Excel with the Quit method on the Application object.
ExcelSheet.Application.Quit
' Release the object variable.
Set ExcelSheet = Nothing
具体可以参照msdn。
然后,在vbScript中这样写:
Dim ExcelSheet
Set ExcelSheet = CreateObject("Excel.Sheet")
创建了excel的object后,就可以对其进行操作了。
比如,
' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True
' Place some text in the first cell of the sheet.
ExcelSheet.ActiveSheet.Cells(1,1).Value = "This is column A, row 1"
' Save the sheet.
ExcelSheet.SaveAs "C:\DOCS\TEST.XLS"
' Close Excel with the Quit method on the Application object.
ExcelSheet.Application.Quit
' Release the object variable.
Set ExcelSheet = Nothing
具体可以参照msdn。
解决方案 »
- 那位牛人愿意帮帮忙??我这个作业明天交,临时学来不及了。。帮忙的万分感谢哈。。。。
- 用javascript制作VB/Java代码高亮工具。。
- jquery取json文件
- jquery中如何控制动画同时进行不同时结束?
- 怎样计算两个时间之间的年数、月数
- 大家来pk啦,数组组合算法,你最快的浏览器+你最快的程序能跑到多少
- 为什么我的框架页(是弹出窗口)按f5刷新时,页面自动关闭了???
- 关于<textarea>标签中限制长度,高手帮帮忙谢谢了!
- 求助:EXTJS分页问题
- 如何确定弹出框的位置
- 怎么实现outlook菜单的效果啊?急啊~~~~
- 如何用javascript控制 iframe或frame的宽度 高度
<HTML>
<BODY>
Press the button to start Excel and display quarterly data.
<SCRIPT LANGUAGE="VBScript">
Function CreateNamesArray()
' Create an array to set multiple values at once.
Dim saNames(5, 2)
saNames(0, 0) = "John"
saNames(0, 1) = "Smith"
saNames(1, 0) = "Tom"
saNames(1, 1) = "Brown"
saNames(2, 0) = "Sue"
saNames(2, 1) = "Thomas"
saNames(3, 0) = "Jane"
saNames(3, 1) = "Jones"
saNames(4, 0) = "Adam"
saNames(4, 1) = "Johnson"
CreateNamesArray = saNames
End Function
</SCRIPT><SCRIPT LANGUAGE="JScript">
function AutomateExcel()
{
// Start Excel and get Application object.
var oXL = new ActiveXObject("Excel.Application");
oXL.Visible = true;
// Get a new workbook.
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
// Add table headers going cell by cell.
oSheet.Cells(1, 1).Value = "First Name";
oSheet.Cells(1, 2).Value = "Last Name";
oSheet.Cells(1, 3).Value = "Full Name";
oSheet.Cells(1, 4).Value = "Salary";
// Format A1:D1 as bold, vertical alignment = center.
oSheet.Range("A1", "D1").Font.Bold = true;
oSheet.Range("A1", "D1").VerticalAlignment = -4108; //xlVAlignCenter
// Create an array to set multiple values at once. // Fill A2:B6 with an array of values (from VBScript).
oSheet.Range("A2", "B6").Value = CreateNamesArray();
// Fill C2:C6 with a relative formula (=A2 & " " & B2).
var oRng = oSheet.Range("C2", "C6");
oRng.Formula = "=A2 & \" \" & B2";
// Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = oSheet.Range("D2", "D6");
oRng.Formula = "=RAND()*100000";
oRng.NumberFormat = "$0.00";
// AutoFit columns A:D.
oRng = oSheet.Range("A1", "D1");
oRng.EntireColumn.AutoFit();
// Manipulate a variable number of columns for Quarterly Sales Data.
DispalyQuarterlySales(oSheet);
// Make sure Excel is visible and give the user control
// of Excel's lifetime.
oXL.Visible = true;
oXL.UserControl = true;
}
function DispalyQuarterlySales(oWS)
{
var iNumQtrs, sMsg, iRet; // Number of quarters to display data for.
iNumQtrs = 4;
// Starting at E1, fill headers for the number of columns selected.
var oResizeRange = oWS.Range("E1", "E1").Resize(1,iNumQtrs);
oResizeRange.Formula = "=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\"";
// Change the Orientation and WrapText properties for the headers.
oResizeRange.Orientation = 38;
oResizeRange.WrapText = true;
// Fill the interior color of the headers.
oResizeRange.Interior.ColorIndex = 36;
// Fill the columns with a formula and apply a number format.
oResizeRange = oWS.Range("E2", "E6").Resize(5,iNumQtrs);
oResizeRange.Formula = "=RAND()*100";
oResizeRange.NumberFormat = "$0.00";
// Apply borders to the Sales data and headers.
oResizeRange = oWS.Range("E1", "E6").Resize(6,iNumQtrs);
oResizeRange.Borders.Weight = 2; // xlThin
// Add a Totals formula for the sales data and apply a border.
oResizeRange = oWS.Range("E8", "E8").Resize(1,iNumQtrs);
oResizeRange.Formula = "=SUM(E2:E6)";
// 9 = xlEdgeBottom
oResizeRange.Borders(9).LineStyle = -4119; //xlDouble
oResizeRange.Borders(9).Weight = 4; //xlThick
// Add a Chart for the selected data.
oResizeRange = oWS.Range("E2:E6").Resize(5,iNumQtrs);
var oChart = oWS.Parent.Charts.Add();
oChart.ChartWizard(oResizeRange, -4100, null, 2); // -4100 = xl3dColumn
oChart.SeriesCollection(1).XValues = oWS.Range("A2", "A6");
for (iRet = 1; iRet <= iNumQtrs; iRet++) {
oChart.SeriesCollection(iRet).Name = "=\"Q" + iRet + "\"";
}
oChart.Location(2, oWS.Name); // 2 = xlLocationAsObject
// Move the chart so as not to cover your data.
oWS.Shapes("Chart 1").Top = oWS.Rows(10).Top;
oWS.Shapes("Chart 1").Left = oWS.Columns(2).Left;
}
</SCRIPT>
<P><INPUT id=button1 type=button value="Start Excel"
onclick="AutomateExcel"></P>
</BODY>
</HTML>
2,Add a CommandButton to Form1.
3,Click References from the Project menu. Add a reference to the Microsoft ActiveX Data Objects 2.1 Library.
Paste the following code into the code section of Form1:Private Sub Command1_Click()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
' Set the string to the path of your Northwind database
strDB = "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
' Open recordset based on Orders table
rst.Open "Select * From Orders", cnt
' Create an instance of Excel and add a workbook
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")
' Display Excel and give user control of Excel's lifetime
xlApp.Visible = True
xlApp.UserControl = True
' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next
' Check version of Excel
If Val(Left$(xlApp.Version, 1)) > 8 Then
'EXCEL 2000: Use CopyFromRecordset
' Copy the recordset to the worksheet, starting in cell A2
xlWs.Cells(2, 1).CopyFromRecordset rst
'Note: CopyFromRecordset will fail if the recordset
'contains an OLE object field or array data such
'as hierarchical recordsets
Else
'EXCEL 97 or earlier: Use GetRows then copy array to Excel
' Copy recordset to an array
recArray = rst.GetRows
'Note: GetRows returns a 0-based array where the first
'dimension contains fields and the second dimension
'contains records. We will transpose this array so that
'the first dimension contains records, allowing the
'data to appears properly when copied to Excel
' Determine number of records
recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array
' Check the array for contents that are not valid when
' copying the array to an Excel worksheet
For iCol = 0 To fldCount - 1
For iRow = 0 To recCount - 1
' Take care of Date fields
If IsDate(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = Format(recArray(iCol, iRow))
' Take care of OLE object fields or array fields
ElseIf IsArray(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = "Array Field"
End If
Next iRow 'next record
Next iCol 'next field
' Transpose and Copy the array to the worksheet,
' starting in cell A2
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
TransposeDim(recArray)
End If ' Auto-fit the column widths and row heights
xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit ' Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
' Release Excel references
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = NothingEnd SubFunction TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)
Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
Dim tempArray As Variant
Xupper = UBound(v, 2)
Yupper = UBound(v, 1)
ReDim tempArray(Xupper, Yupper)
For X = 0 To Xupper
For Y = 0 To Yupper
tempArray(X, Y) = v(Y, X)
Next Y
Next X
TransposeDim = tempArrayEnd Function
Press the F5 key to run the project. Form1 appears.
Click the CommandButton on Form1, and note that the contents of the Orders table is displayed in a new workbook in Excel.