我想要使用VB来查找excel中的资料的代码，最好是全代码，越详细越好。最好也能告诉 我，如何在VB中打开Excel文件夹？我想要做一个vb程序，但可以打开三个不同的Excel文件。代码有谁知道嘛？第一个Excel中有编号、名称、数量、单位、单价、收进、发出、金额、备注这些资料，第二个Excel中有编号、客户、报进产量、出货数量、金额、页数、备注这些资料。第三个Excel中有编号、日期、客户、画料、做帮、件数、钳帮、件数、金额、备注。越详细越好，我把全部积分都给你吧。急。。。
VBæä½EXCEL
å ¨é¢æ§å¶ Excel
é¦å å建 Excel 对象ï¼ä½¿ç¨ComObj:
Dim ExcelID as Excel.Application
Set ExcelID as new Excel.Application
1) æ¾ç¤ºå½åçªå£ï¼
ExcelID.Visible := True;
2) æ´æ¹ Excel æ é¢æ ï¼
ExcelID.Caption := \'åºç¨ç¨åºè°ç¨ Microsoft Excel\';
3) æ·»å æ°å·¥ä½ç°¿ï¼
ExcelID.WorkBooks.Add;
4) æå¼å·²åå¨çå·¥ä½ç°¿ï¼
ExcelID.WorkBooks.Open( \'C:\\Excel\\Demo.xls\' );
5) 设置第2个工ä½è¡¨ä¸ºæ´»å¨å·¥ä½è¡¨ï¼
ExcelID.WorkSheets[2].Activate;
æ ExcelID.WorkSheets[ \'Sheet2\' ].Activate;
6) ç»åå æ ¼èµå¼ï¼
ExcelID.Cells[1,4].Value := \'第ä¸è¡ç¬¬åå\';
7) 设置æå®åç宽度ï¼åä½ï¼å符个æ°ï¼ï¼ä»¥ç¬¬ä¸å为ä¾ï¼
ExcelID.ActiveSheet.Columns[1].ColumnsWidth := 5;
8) 设置æå®è¡çé«åº¦ï¼åä½ï¼ç£ ï¼ï¼1ç£ ï¼0.035åç±³ï¼ï¼ä»¥ç¬¬äºè¡ä¸ºä¾ï¼
ExcelID.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1åç±³
9) å¨ç¬¬8è¡ä¹åæå ¥å页符ï¼
ExcelID.WorkSheets[1].Rows[8].PageBreak := 1;
åè代ç ï¼ActiveSheet.HPageBreaks(1).Location = Range("A22")
10) å¨ç¬¬8åä¹åå é¤å页符ï¼
ExcelID.ActiveSheet.Columns[4].PageBreak := 0;
11) æå®è¾¹æ¡çº¿å®½åº¦ï¼
ExcelID.ActiveSheet.Range[ \'B3:D4\' ].Borders[2].Weight := 3;
1-å·¦ 2-å³ 3-顶 4-åº 5-æ( \\ ) 6-æ( / )
12) æ¸ é¤ç¬¬ä¸è¡ç¬¬åååå æ ¼å ¬å¼ï¼
ExcelID.ActiveSheet.Cells[1,4].ClearContents;
13) 设置第ä¸è¡åä½å±æ§ï¼
ExcelID.ActiveSheet.Rows[1].Font.Name := \'é¶ä¹¦\';
ExcelID.ActiveSheet.Rows[1].Font.Color := clBlue;
ExcelID.ActiveSheet.Rows[1].Font.Bold := True;
ExcelID.ActiveSheet.Rows[1].Font.UnderLine := True;
14) è¿è¡é¡µé¢è®¾ç½®ï¼
å ¨é¢æ§å¶ Excel
é¦å å建 Excel 对象ï¼ä½¿ç¨ComObj:
Dim ExcelID as Excel.Application
Set ExcelID as new Excel.Application
1) æ¾ç¤ºå½åçªå£ï¼
ExcelID.Visible := True;
2) æ´æ¹ Excel æ é¢æ ï¼
ExcelID.Caption := \'åºç¨ç¨åºè°ç¨ Microsoft Excel\';
3) æ·»å æ°å·¥ä½ç°¿ï¼
ExcelID.WorkBooks.Add;
4) æå¼å·²åå¨çå·¥ä½ç°¿ï¼
ExcelID.WorkBooks.Open( \'C:\\Excel\\Demo.xls\' );
5) 设置第2个工ä½è¡¨ä¸ºæ´»å¨å·¥ä½è¡¨ï¼
ExcelID.WorkSheets[2].Activate;
æ ExcelID.WorkSheets[ \'Sheet2\' ].Activate;
6) ç»åå æ ¼èµå¼ï¼
ExcelID.Cells[1,4].Value := \'第ä¸è¡ç¬¬åå\';
7) 设置æå®åç宽度ï¼åä½ï¼å符个æ°ï¼ï¼ä»¥ç¬¬ä¸å为ä¾ï¼
ExcelID.ActiveSheet.Columns[1].ColumnsWidth := 5;
8) 设置æå®è¡çé«åº¦ï¼åä½ï¼ç£ ï¼ï¼1ç£ ï¼0.035åç±³ï¼ï¼ä»¥ç¬¬äºè¡ä¸ºä¾ï¼
ExcelID.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1åç±³
9) å¨ç¬¬8è¡ä¹åæå ¥å页符ï¼
ExcelID.WorkSheets[1].Rows[8].PageBreak := 1;
åè代ç ï¼ActiveSheet.HPageBreaks(1).Location = Range("A22")
10) å¨ç¬¬8åä¹åå é¤å页符ï¼
ExcelID.ActiveSheet.Columns[4].PageBreak := 0;
11) æå®è¾¹æ¡çº¿å®½åº¦ï¼
ExcelID.ActiveSheet.Range[ \'B3:D4\' ].Borders[2].Weight := 3;
1-å·¦ 2-å³ 3-顶 4-åº 5-æ( \\ ) 6-æ( / )
12) æ¸ é¤ç¬¬ä¸è¡ç¬¬åååå æ ¼å ¬å¼ï¼
ExcelID.ActiveSheet.Cells[1,4].ClearContents;
13) 设置第ä¸è¡åä½å±æ§ï¼
ExcelID.ActiveSheet.Rows[1].Font.Name := \'é¶ä¹¦\';
ExcelID.ActiveSheet.Rows[1].Font.Color := clBlue;
ExcelID.ActiveSheet.Rows[1].Font.Bold := True;
ExcelID.ActiveSheet.Rows[1].Font.UnderLine := True;
14) è¿è¡é¡µé¢è®¾ç½®ï¼
温馨提示：答案为网友推荐，仅供参考
第1个回答 2013-10-21
不知道你到底要求查找什么，但是给你一段代码用来打开，提取EXCEL中的数据，数据提出来估计剩下的你自己就可以完成了
Private Sub Command1_Click()
Dim row As Integer
Dim col As Integer
Dim xlApp As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
Dim rcd
Set xlBook = xlApp.Workbooks.Open("H:\t.xls")
Set xlSheet = xlBook.Worksheets("Sheet1")
row = 1
col = 1
rd:
Do While xlSheet.Cells(row, col) <> ""
rcd = rcd & " " & xlSheet.Cells(row, col)
col = col + 1
Loop
Print rcd
col = 1
rcd = ""
If xlSheet.Cells(row + 1, col) <> "" Then
row = row + 1
GoTo rd
End If
xlApp.Quit
End Sub
Private Sub Command1_Click()
Dim row As Integer
Dim col As Integer
Dim xlApp As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
Dim rcd
Set xlBook = xlApp.Workbooks.Open("H:\t.xls")
Set xlSheet = xlBook.Worksheets("Sheet1")
row = 1
col = 1
rd:
Do While xlSheet.Cells(row, col) <> ""
rcd = rcd & " " & xlSheet.Cells(row, col)
col = col + 1
Loop
Print rcd
col = 1
rcd = ""
If xlSheet.Cells(row + 1, col) <> "" Then
row = row + 1
GoTo rd
End If
xlApp.Quit
End Sub
第2个回答 2013-10-21
用VB操作EXCEL
Private Sub Command3_Click()
On Error GoTo err1
Dim i As Long
Dim j As Long
Dim objExl As Excel.Application \'声明对象变量
Me.MousePointer = 11 \'改变鼠标样式
Set objExl = New Excel.Application \'初始化对象变量
objExl.SheetsInNewWorkbook = 1 \'将新建的工作薄数量设为1
objExl.Workbooks.Add \'增加一个工作薄
objExl.Sheets(objExl.Sheets.Count).Name = "book1" \'修改工作薄名称
objExl.Sheets.Add , objExl.Sheets("book1") ‘增加第二个工作薄在第一个之后
objExl.Sheets(objExl.Sheets.Count).Name = "book2"
objExl.Selection.NumberFormatLocal = "@" \'设置格式为文本
objExl.Cells(i, j) = " E " & i & j
Else
objExl.Cells(i, j) = i & j
End If
Next
Next
objExl.Rows("1:1").Select \'选中第一行
objExl.Selection.Font.Bold = True \'设为粗体
objExl.Selection.Font.Size = 24 \'设置字体大小
objExl.Cells.EntireColumn.AutoFit \'自动调整列宽
objExl.ActiveWindow.SplitRow = 1 \'拆分第一行
objExl.ActiveWindow. SplitColumn = 0 \'拆分列
objExl.ActiveWindow.FreezePanes = True \'固定拆分 objExl.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1" \'设置打印固定行
objExl.ActiveSheet.PageSetup.PrintTitleColumns = "" \'打印标题 objExl.ActiveSheet.PageSetup.RightFooter = "打印时间: " & _
Format(Now, "yyyy年mm月dd日 hh:MM:ss")
objExl.ActiveWindow.View = xlPageBreakPreview \'设置显示方式
objExl.ActiveWindow.Zoom = 100 \'设置显示大小
objExl.ActiveSheet.PageSetup.Orientation = xlLandscape ‘设置打印方向(横向)
\'给工作表加密码
objExl.ActiveSheet.Protect "123", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
objExl.Application.IgnoreRemoteRequests = False
objExl.Visible = True \'使EXCEL可见
objExl.Application.WindowState = xlMaximized \'EXCEL的显示方式为最大化
objExl.ActiveWindow.WindowState = xlMaximized \'工作薄显示方式为最大化
objExl.SheetsInNewWorkbook = 3 \'将默认新工作薄数量改回3个
Set objExl = Nothing \'清除对象
Me.MousePointer = 0 \'修改鼠标
Exit Sub
err1:
objExl.SheetsInNewWorkbook = 3
objExl.DisplayAlerts = False \'关闭时不提示保存
objExl.Quit \'关闭EXCEL
objExl.DisplayAlerts = True \'关闭时提示保存
Set objExl = Nothing
Me.MousePointer = 0
End Sub
Private Sub Command3_Click()
On Error GoTo err1
Dim i As Long
Dim j As Long
Dim objExl As Excel.Application \'声明对象变量
Me.MousePointer = 11 \'改变鼠标样式
Set objExl = New Excel.Application \'初始化对象变量
objExl.SheetsInNewWorkbook = 1 \'将新建的工作薄数量设为1
objExl.Workbooks.Add \'增加一个工作薄
objExl.Sheets(objExl.Sheets.Count).Name = "book1" \'修改工作薄名称
objExl.Sheets.Add , objExl.Sheets("book1") ‘增加第二个工作薄在第一个之后
objExl.Sheets(objExl.Sheets.Count).Name = "book2"
objExl.Selection.NumberFormatLocal = "@" \'设置格式为文本
objExl.Cells(i, j) = " E " & i & j
Else
objExl.Cells(i, j) = i & j
End If
Next
Next
objExl.Rows("1:1").Select \'选中第一行
objExl.Selection.Font.Bold = True \'设为粗体
objExl.Selection.Font.Size = 24 \'设置字体大小
objExl.Cells.EntireColumn.AutoFit \'自动调整列宽
objExl.ActiveWindow.SplitRow = 1 \'拆分第一行
objExl.ActiveWindow. SplitColumn = 0 \'拆分列
objExl.ActiveWindow.FreezePanes = True \'固定拆分 objExl.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1" \'设置打印固定行
objExl.ActiveSheet.PageSetup.PrintTitleColumns = "" \'打印标题 objExl.ActiveSheet.PageSetup.RightFooter = "打印时间: " & _
Format(Now, "yyyy年mm月dd日 hh:MM:ss")
objExl.ActiveWindow.View = xlPageBreakPreview \'设置显示方式
objExl.ActiveWindow.Zoom = 100 \'设置显示大小
objExl.ActiveSheet.PageSetup.Orientation = xlLandscape ‘设置打印方向(横向)
\'给工作表加密码
objExl.ActiveSheet.Protect "123", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
objExl.Application.IgnoreRemoteRequests = False
objExl.Visible = True \'使EXCEL可见
objExl.Application.WindowState = xlMaximized \'EXCEL的显示方式为最大化
objExl.ActiveWindow.WindowState = xlMaximized \'工作薄显示方式为最大化
objExl.SheetsInNewWorkbook = 3 \'将默认新工作薄数量改回3个
Set objExl = Nothing \'清除对象
Me.MousePointer = 0 \'修改鼠标
Exit Sub
err1:
objExl.SheetsInNewWorkbook = 3
objExl.DisplayAlerts = False \'关闭时不提示保存
objExl.Quit \'关闭EXCEL
objExl.DisplayAlerts = True \'关闭时提示保存
Set objExl = Nothing
Me.MousePointer = 0
End Sub
