如何用vb提取excel表格中的数据如何用vb提取excel表格中的数据和文字
首先需要知道你要引用的数据在Excel中的位置，然后在VB里面引用Excel，然后打开Excel文件读数据
Dim scxls As Excel.Application
Dim scbook As Excel.Workbook
Dim scsheet As Excel.Worksheet
‘Set scxls = CreateObject(\"excel.application\")
‘Set scbook = scxls.Workbooks.Add
Set scbook = scxls.Workbooks.open(\"c:\\1.xls\")
Set scsheet = scbook.Worksheets(1)
‘scsheet.Cells(1, 2) = \"1111\" ’写入数据
a=scsheet.Cells(1, 2) '读取数据
Set scbook= Nothing
Set scxls = Nothing
‘scxls.Visible = True’显示Excle
数据较多，且有规律可循时，请使用循环语句
用vb提取excel表格中的数据方法：
1、在汇总表中的A列，将多个工作表的表名依次排列。
2、在B1单元格输入以下公式，然后向右向下填充公式=OFFSET(INDIRECT($A1&"!A1"),,COLUMN(A1)-1)&""
公式表示：以A1单元格的内容作为表名引用，通过OFFSET函数将表名引用的A1单元格作为基准单元格，每向右移动一列，基准单元格向右偏移一列。从而得到A列表名工作表的所有首行数据。如果数据为空，就返回空值。
绛旓細if cells(1,"A").value="缇庡浗" then cells(1,"B")="1123"end sub 浠ユ涓轰緥锛屽湪Excel涓璇诲彇VBA绋嬪簭鐨勬搷浣滄楠ゅ涓嬶紙Excel2007涓轰緥锛夛細1.閫夋嫨鈥濆紑鍙戝伐鍏封滈夐」鍗★紝鐐瑰嚮鈥漋isual Basic鈥滐紙鎴栬呯敤蹇嵎閿瓵lt+F11锛2.鍑虹幇VBA缂栬緫鐣岄潰锛屾敞鎰忕湅宸︿晶锛岄夋嫨闇瑕佽繍琛屼唬鐮佺殑宸ヤ綔琛锛屾渚嬩负Sheet1锛涘弻鍑昏繘鍏...
绛旓細vb璇诲彇琛ㄦ牸涓鐨勬暟鎹湁涓嬮潰鍑犱釜鏂规硶 1锛宎 = range("c5")2锛宎=cells(3,5)3锛宎=cells("c",5)4锛宎=[c5]杩樻湁鍏朵粬鏁扮粍璧嬪肩殑鏂规硶 绁濅綘鎴愬姛
绛旓細璇诲彇鏂囦欢鏁版嵁锛屽瓨鍏ユ暟缁勶紱鎸夋寚瀹氭潯浠跺鏁扮粍鎺掑簭锛涙妸鏁扮粍鍏冪礌杈撳叆Excel鍚庝繚瀛樸備笅闈㈡槸VB鎿嶄綔Excel绀轰緥 '宸ョ▼-閮ㄤ欢-寮曠敤Microsoft Excel 11.0 Object Library Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet '鍐欐枃浠 Private Sub Command1_Click()Set xlApp...
绛旓細杩欎釜瀹规槗锛屾垜鍋氳繃銆備綘宸茬粡鍋氳繃涓鐐逛簡锛岄偅灏辫〃绀哄凡缁忓湪娲诲姩鐨宸ヤ綔琛ㄤ腑浜嗭紝涓嬮潰灏卞湪杩欎釜宸ヤ綔琛ㄤ腑鎿嶄綔銆侱im a() As String Function Getvalue(ByVal Colu As String, ByVal Rowstart As Integer) As String()'colu 涓轰綘瑕鎻愬彇鐨勫垪鍚嶏紙A B C 鈥︹︼級;rowstart 涓鸿鐨勫紑濮 Dim i As Integer: i...
绛旓細鍦宸ヤ綔琛鏍囩涓婂嚮鍙抽敭锛屾煡鐪嬩唬鐮併傚湪VBE绐楀彛涓紝鐐光滄彃鍏モ濓紝妯″潡銆傚皢浠ヤ笅浠ｇ爜绮樿创鍒版ā鍧椾唬鐮佺獥鏍间腑锛孉LT+F8杩愯璇ュ畯锛屽氨琛屼簡銆傞檮浠朵腑鏈夋牱鏈係ub 鏁版嵁鎻愬彇()For i = 2 To Worksheets.CountSheets(1).Cells(Sheets(1).[a65536].End(3).Row + 1, 1) = Sheets(i).[D3]Sheets(1).Cells(...
绛旓細Set xlApp = CreateObject("Excel.Application") '鍒涘缓EXCEL搴旂敤绫 xlApp.Visible = True '璁剧疆EXCEL鍙 Set xlBook = xlApp.Workbooks.Open(DestinationFile) '鎵撳紑EXCEL宸ヤ綔绨 Set xlsheet = xlBook.Worksheets(1) '鎵撳紑EXCEL宸ヤ綔琛 xlsheet.Activate '婵娲诲伐浣滆〃 xlsheet.Cells(1, 1) = Combo...
绛旓細For i = 1 To x If Application.CountIf(Sheet1.Range("A1:A" & x), Sheet1.Cells(i, 1)) > 1 Then m = m + 1 Sheet1.Cells(m, 2) = Sheet1.Cells(i, 1)End If Next End Sub 鍏抽棴缂栬緫鐢婚潰鍥炲埌宸ヤ綔琛锛岀偣鍑昏彍鍗曪細宸ュ叿--瀹忥紝鎴栫洿鎺ユ寜ALT+F8缁勫悎閿紝鍦ㄦ涓夋嫨瀹忓悕瀛椻鎻愬彇...
绛旓細Set xlApp = CreateObject("Excel.Application") '鍒涘缓EXCEL瀵硅薄 Set xlBook = xlApp.Workbooks.Open(App.Path + "filename.xls") '鎵撳紑宸茬粡瀛樺湪鐨凟XCEL宸ヤ欢绨挎枃浠 Set xlSheet = xlBook.Worksheets("Sheet1") '璁剧疆娲诲姩宸ヤ綔琛 x=xlSheet.Cells(x,y) '璇诲彇鍗曞厓鏍鍐呭 Set xlApp = Nothing '...
绛旓細鐪嬬湅鏈夊府鍔╂病锝VB鎿嶄綔EXCEL 鍏ㄩ潰鎺у埗 Excel 棣栧厛鍒涘缓 Excel 瀵硅薄锛屼娇鐢–omObj:Dim ExcelID as Excel.Application Set ExcelID as new Excel.Application 1) 鏄剧ず褰撳墠绐楀彛锛欵xcelID.Visible := True;2) 鏇存敼 Excel 鏍囬鏍忥細ExcelID.Caption := \'搴旂敤绋嬪簭璋冪敤 Microsoft Excel\';3) 娣诲姞鏂板伐浣滅翱锛欵...
绛旓細鐢ˋDO鍙互鎻愬彇,鍏堝紩鐢ˋDO (Microsoft ActiveX Data Object)鍋囧畾浣犵殑excel琛鍚嶄负Book1.xls锛屽瓨鏀惧湪C鐩 鐒跺悗 Dim Rs As New ADODB.Recordset Dim sql AS String Rs.CursorLocation=adUseClient StrData = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Book1.xls;Extended Properties='Excel 8.0...
