Dim xlApp As Excel.Application '定义EXCEL类 Dim xlBook As Excel.Workbook '定义工件簿类 Dim xlsheet As Excel.Worksheet '定义工作表类 Private Sub Command1_Click() '打开EXCEL过程 If Dir("D:\temp\excel.bz") = "" Then '判断EXCEL是否打开 Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类 xlApp.Visible = True '设置EXCEL可见 Set xlBook = xlApp.Workbooks.Open("D:\vb6\lianxie\a1\book1.xls") '打开EXCEL工作簿 Set xlsheet = xlBook.Worksheets(2) '打开EXCEL工作表 xlsheet.Activate '激活工作表 xlsheet.Cells(1, 1) = "abc" '给单元格1行驶列赋值 xlBook.RunAutoMacros (xlAutoOpen) '运行EXCEL中的启动宏 Else MsgBox ("EXCEL已打开") End If End SubPrivate Sub Command2_Click() If Dir("D:\temp\excel.bz") <> "" Then '由VB关闭EXCEL xlBook.RunAutoMacros (xlAutoClose) '执行EXCEL关闭宏 xlBook.Close (True) '关闭EXCEL工作簿 xlApp.Quit '关闭EXCEL End If Set xlApp = Nothing '释放EXCEL对象 End End Sub
//借助ADO访问Excel //引用Microsoft Activex Data Object 2.X 然后用如下代码访问数据库 dim cn as new ADODB.Connection dim rs as new ADODB.RecordSet dim strSql as string strSql="SELECT * FROM [Sheet1$]" cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""" rs.Open strSql,cn,1,3msgbox rs("ColumnsName")
Dim xlBook As Excel.Workbook '定义工件簿类
Dim xlsheet As Excel.Worksheet '定义工作表类
Private Sub Command1_Click() '打开EXCEL过程
If Dir("D:\temp\excel.bz") = "" Then '判断EXCEL是否打开
Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
xlApp.Visible = True '设置EXCEL可见
Set xlBook = xlApp.Workbooks.Open("D:\vb6\lianxie\a1\book1.xls") '打开EXCEL工作簿
Set xlsheet = xlBook.Worksheets(2) '打开EXCEL工作表
xlsheet.Activate '激活工作表
xlsheet.Cells(1, 1) = "abc" '给单元格1行驶列赋值
xlBook.RunAutoMacros (xlAutoOpen) '运行EXCEL中的启动宏
Else
MsgBox ("EXCEL已打开")
End If
End SubPrivate Sub Command2_Click()
If Dir("D:\temp\excel.bz") <> "" Then '由VB关闭EXCEL
xlBook.RunAutoMacros (xlAutoClose) '执行EXCEL关闭宏
xlBook.Close (True) '关闭EXCEL工作簿
xlApp.Quit '关闭EXCEL
End If
Set xlApp = Nothing '释放EXCEL对象
End
End Sub
//引用Microsoft Activex Data Object 2.X
然后用如下代码访问数据库
dim cn as new ADODB.Connection
dim rs as new ADODB.RecordSet
dim strSql as string
strSql="SELECT * FROM [Sheet1$]"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""" rs.Open strSql,cn,1,3msgbox rs("ColumnsName")