VB操作Excel信息录入与查询

为了理解VB具体如何操作Excel，包括创建，打开，写入，读取等基本操作，闲暇之余编写测试，在此留以备用。

 

I.VB创建Excel表:
'定义Excel操作变量 Dim xlApp As New Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet '创建Excel进程，并打开目标Excel文件 Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.DisplayAlerts = False Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1) xlBook.SaveAs "路径/文件名"

 

II.VB打开Excel表:
'定义Excel操作变量 Dim xlApp As New Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet '打开目标Excel文件 Set xlApp = New Excel.Application xlApp.Visible = True xlApp.DisplayAlerts = False Set xlBook = xlApp.Workbooks.Open("路径/文件名") Set xlSheet = xlBook.Sheets(1) xlBook.Save

 

III.VB关闭Excel表:
'定义Excel操作变量 Dim xlApp As New Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet '已经打开目标Excel文件 Set xlApp = New Excel.Application xlApp.Visible = True xlApp.DisplayAlerts = False Set xlBook = xlApp.Workbooks.Open("路径/文件名") Set xlSheet = xlBook.Sheets(1) xlBook.Save '关闭目标Excel文件 xlApp.Quit Set xlBook = Nothing Set xlSheet = Nothing Set xlApp = Nothing

 

IV.VB写入Excel表:
'定义Excel操作变量 Dim xlApp As New Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet '打开目标Excel文件并且写入 Set xlApp = New Excel.Application xlApp.Visible = True xlApp.DisplayAlerts = False Set xlBook = xlApp.Workbooks.Open(“路径/文件名") Set xlSheet = xlBook.Sheets(1) With xlSheet .Cells(1, 1) = "A" .Cells(1, 2) = "B" .Cells(1, 3) = "C" End With xlBook.Save

 

V.VB读取Excel表:
'定义Excel操作变量 Dim xlApp As New Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet '定义用于保存Excel单元格内容的字符串 Dim xlString As String '定义整形变量用于保存Excel表的行数与列数 Dim xlNum, xlCol As Integer '打开目标Excel文件并且读取保存Excel内容 Set xlApp = New Excel.Application xlApp.Visible = True xlApp.DisplayAlerts = False Set xlBook = xlApp.Workbooks.Open("路径/文件名") Set xlSheet = xlBook.Sheets(1) '读取Excel表中记录行数 xlNum = xlSheet.UsedRange.Rows.Count '读取Excel表中记录列数 xlCol = xlSheet.UsedRange.Columns.Count '读取Excel表中i行j列单元格内容 xlString = Trim$(xlSheet.Cells(i,j)) xlBook.Save

 

 

VB操作Excel足彩信息录入与查询
创建VB应用程序，文件->新建工程->标准EXE，确认。

 

应用程序源码
Private Declare Function SetWindowPos& Lib "user32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) '定义Excel类 Dim xlApp As New Excel.Application '定义工作簿类 Dim xlBook As Excel.Workbook '定义工作表类 Dim xlSheet As Excel.Worksheet '定义文件名 Dim xlFile As String '定义行列数量名 Dim xlNum As Integer Dim xlCol As Integer '定义计数器 Dim i As Integer Dim j As Integer Dim m As Integer Dim n As Integer '定义判断符 Dim IsRepeat As Boolean '定义字符串 Dim strTemp(9999) As String '--------------------------------程序加载------------------------------------------ Private Sub Form_Load() xlFile = App.Path + "/football.xls" '判断是否打开并建立 If Dir(xlFile) <> "" Then Set xlApp = New Excel.Application xlApp.Visible = True xlApp.DisplayAlerts = False Set xlBook = xlApp.Workbooks.Open(xlFile) Set xlSheet = xlBook.Sheets(1) xlBook.Save '读取记录行数 xlNum = xlSheet.UsedRange.Rows.Count '读取记录列数 xlCol = xlSheet.UsedRange.Columns.Count '读取xlSheet中的行数 lblInfo.Caption = "目标表中共有" + Str(xlNum - 1) + " 条信息" '添加所有数据信息到显示列表 For i = 0 To xlNum - 2 For j = 0 To xlCol - 1 strTemp(i) = strTemp(i) + Trim$(xlSheet.Cells(i + 2, j + 1)) + "," Next j Next i With lstInfo For n = 0 To xlNum - 2 .AddItem strTemp(n) Next n End With '生成按日期查询候选目录 With comboDate .AddItem "" '依次添加日期候选目录 For i = 2 To xlNum If Trim$(xlSheet.Cells(i, 1)) <> "" Then IsRepeat = False '依次与同列前面单元格内容进行对比 For j = 1 To i - 1 If Trim$(xlSheet.Cells(i, 1)) = Trim$(xlSheet.Cells(j, 1)) Then '若与同列前面单元格内容重复,则重复判断符置为True IsRepeat = True End If Nex