在vb里 读取excel某一列数据-用vb操作excel,vb如何调用excel

Microsoft Excel是微抄软公司的办公软件Microsoft office的组件之一，是由Microsoft为Windows和Apple Macintosh操作袭系统的电脑而编写和运行的一百款试度算表软件。Excel 是微软办公套装软件的一个重要的组成部分，它可以进行知各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多道领域。

'引用：
'添加对该对象的引用 工程菜单-->引用，找到 Microsoft Scripting Runtime
'引用microsoft Excel 14.0 object library
'
Dim elApp As Excel.Application
Dim elBooks As Excel.Workbook
Dim ekSheet As Excel.Worksheet
Dim TblMap_Card '创建一个变量

Private Sub Command1_Click()
Dim i As Integer
openEl
Set dic = CreateObject("Scripting.Dictionary")
'MsgBox ekSheet.Cells(Rows.Count, 1).End(3).Row
For i = 2 To ekSheet.Cells(Rows.Count, 1).End(3).Row
If dic.Exists(ekSheet.Cells(i, 2).Value) Then
dic(ekSheet.Cells(i, 1).Value) = dic(ekSheet.Cells(i, 1).Value) + ekSheet.Cells(i, 2).Value
Else
dic(ekSheet.Cells(i, 1).Value) = ekSheet.Cells(i, 2).Value
End If
Next i

ekSheet.Range("H:J").Clear

'ekSheet.Cells(1, 9).Resize(1, 2) = Array("商品", "售量")
ekSheet.Cells(2, 9).Resize(dic.Count, 1) = Application.Transpose(dic.Keys)
ekSheet.Cells(2, 10).Resize(dic.Count, 1) = Application.Transpose(dic.Items)

End Sub

Private Sub openEl()
Dim myPath As String
myPath = "\week.xlsx"
Set elApp = CreateObject("Excel.Application")
Set elBooks = elApp.Workbooks.Open(App.Path & myPath)
Set ekSheet = elBooks.Worksheets("Sheet1")
'Set ekSheet = elBooks.Worksheets(1)
elApp.Visible = True
End Sub

张志晨：VB6的，测试通e68a84e8a2ade799bee5baa6e997aee7ad94361过。界面上只有一个按钮。
'引用：
'添加对该对象的引用 工程菜单-->引用，找到 Microsoft Scripting Runtime
'引用microsoft Excel 14.0 object library
'
Dim elApp As Excel.Application
Dim elBooks As Excel.Workbook
Dim ekSheet As Excel.Worksheet
Dim TblMap_Card '创建一个变量e69da5e887aa7a686964616f330

Private Sub Command1_Click()
Dim i As Integer
openEl
Set dic = CreateObject("Scripting.Dictionary")
'MsgBox ekSheet.Cells(Rows.Count, 1).End(3).Row
For i = 2 To ekSheet.Cells(Rows.Count, 1).End(3).Row
If dic.Exists(ekSheet.Cells(i, 2).Value) Then
dic(ekSheet.Cells(i, 1).Value) = dic(ekSheet.Cells(i, 1).Value) + ekSheet.Cells(i, 2).Value
Else
dic(ekSheet.Cells(i, 1).Value) = ekSheet.Cells(i, 2).Value
End If
Next i

ekSheet.Range("H:J").Clear

'ekSheet.Cells(1, 9).Resize(1, 2) = Array("商品", "售量")
ekSheet.Cells(2, 9).Resize(dic.Count, 1) = Application.Transpose(dic.Keys)
ekSheet.Cells(2, 10).Resize(dic.Count, 1) = Application.Transpose(dic.Items)

End Sub

Private Sub openEl()
Dim myPath As String
myPath = "\week.xlsx"
Set elApp = CreateObject("Excel.Application")
Set elBooks = elApp.Workbooks.Open(App.Path & myPath)
Set ekSheet = elBooks.Worksheets("Sheet1")
'Set ekSheet = elBooks.Worksheets(1)
elApp.Visible = True
End Sub

以下是在VB6.0中标准代码，你可以改成VBA的形式。

按钮1点击后创建文件，按钮2点击后修改文件达到替换列。你可以手动查看修改前后数据的差异。

'工程-引用-Microsoft Excel Object library(勾选此项)
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
'写文件
Private Sub Command1_Click() '创建文件并写入原始数据
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add '新建EXCEL工件簿文件
    Set xlSheet = xlBook.Worksheets(1)
    For i = 1 To 10 '10行
        For j = 1 To 10 '10列
            xlSheet.Cells(i, j) = i * j
        Next j
    Next i
    xlSheet.SaveAs "d:\test.xls" '按指定文件名存盘
    xlApp.Quit '结束EXCEL对象e5a48de588b67a686964616f330
    Set xlApp = Nothing '释放xlApp对象
End Sub
'替换
Private Sub Command2_Click()
    REP 1, 2
End Sub
'用第m列替换第n列
Private Function REP(m As Integer, n As Integer) As Long
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Application.Workbooks.Open("d:\test.xls")
    Set xlSheet = xlBook.Worksheets(1)
    Dim s As String
    For i = 1 To 10 '读取10行
            xlSheet.Cells(i, n) = xlSheet.Cells(i, m)
    Next i
    Print s
    xlApp.Quit '结束EXCEL对象
    Set xlApp = Nothing '释放xlApp对象
End Function