VB操作Excel
- 格式：doc
- 大小：28.50 KB
- 文档页数：3
如何截取excel A1到A100里的数据
假设文件是D:\book1.xls，在VB中建立Command1,Text1,代码如下。
==========================
Private Sub Command1_Click()
Dim Ap As Object, Sht As Object, i As Integer, s As Single
Set Ap = CreateObject("Excel.Application")
Set Sht = Ap.Workbooks.Open("d:\book1.xls").Worksheets("Sheet1")
For i = 1 To 100
s = s + Val(Sht.Cells(i, 1))
Next
Text1.Text = s
Ap.Quit
Set Sht = Nothing
Set Ap = Nothing
End Sub
--------简单的读取-------------------
Public xlApp As Excel.Application
Public xlBook As Excel.Workbook
Public xlSheet As Excel.Worksheet
Public Sub OpenExcel()
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Calender.xls")
Set xlSheet = xlBook.Worksheets(1)
End Sub
Public Sub OpenExcel()
ReadedString =xlSheet.Cells(x, y)
End Sub
Public Sub CloseExcel()
xlBook.Close (False)
xlApp.Application.Quit
Set xlApp = Nothing
End Sub
--------以45555下为导出(包括各种判断)------------------
g_ExcelSavePath ' 保存路径
g_ExcelFileName '文件名 eg:xx.csv xxx.xls
"判断是否存在，覆盖？
If Dir(g_ExcelSavePath & g_ExcelFileName) <> "" Then
If MsgBox("文件:" & " " & g_ExcelSavePath & g_ExcelFileName & " " & "既在。覆
盖？", vbQuestion + vbOKCancel + vbDefaultButton2, "XXXシステム") = vbCancel Then
Exit Sub
Else
Kill (g_ExcelSavePath & g_ExcelFileName)
End If
End If
"判断是否打开
If ExcelIsOpen(g_ExcelFileName) = True Then 'Excel is Open
modGlobal.MsgBoxShow.ShowReturnFalse "MakerPrint", "6009"
Exit Sub
End If
'定义
Dim CurrentApp As Excel.Application
Dim CurrentBook As Excel.Workbook
Dim CurrentSheet As Excel.Worksheet
Dim CurrentQuery As Excel.QueryTable
strExcelFielName = g_ExcelSavePath & g_ExcelFileName
If CurrentApp Is Nothing Then
Set CurrentApp = CreateObject("Excel.Application")
End If
If Dir(strExcelFielName) = "" Then
Set CurrentBook = CurrentApp.Workbooks.Add
CurrentBook.SaveAs strExcelFielName
Else
Kill (strExcelFielName)
Set CurrentBook = CurrentApp.Workbooks.Add
CurrentBook.SaveAs strExcelFielName
End If
CurrentApp.Visible = False
CurrentApp.DisplayAlerts = False '保存，退出时候不弹出警告
CurrentSheet.Name = strMakerName
CurrentBook.Close True
' CurrentBook.Save
CurrentApp.DisplayAlerts = True
CurrentApp.Quit
Set CurrentQuery = Nothing
Set CurrentSheet = Nothing
Set CurrentBook = Nothing
Set CurrentApp = Nothing
、
