vb数据写入excel,请问如何用vb提取excel表格中的数据有一ex 请教请问如何用vb提取excel表格中的数据
\u600e\u4e48\u901a\u8fc7VB\u76f4\u63a5\u4eceexcel\u8868\u683c\u4e2d\u8bfb\u53d6\u6570\u636e\u518d\u5199\u5165\u53e6\u4e00\u4e2aexcel\u4e2d\u53bb\uff1f\u7b49\u5f85\u56de\u7b54\u8005
\u9996\u5148\u9700\u8981\u77e5\u9053\u4f60\u8981\u5f15\u7528\u7684\u6570\u636e\u5728Excel\u4e2d\u7684\u4f4d\u7f6e\uff0c\u7136\u540e\u5728VB\u91cc\u9762\u5f15\u7528Excel\uff0c
\u7136\u540e\u6253\u5f00Excel\u6587\u4ef6\u8bfb\u6570\u636e
Dim scxls As Excel.Application
Dim scbook As Excel.Workbook
Dim scsheet As Excel.Worksheet
\u2018Set scxls = CreateObject("excel.application")
\u2018Set scbook = scxls.Workbooks.Add
Set scbook = scxls.Workbooks.open("c:\1.xls")
Set scsheet = scbook.Worksheets(1)
\u2018scsheet.Cells(1, 2) = "1111" \u2019\u5199\u5165\u6570\u636e
a=scsheet.Cells(1, 2) '\u8bfb\u53d6\u6570\u636e
Set scbook = Nothing
Set scxls = Nothing
\u2018scxls.Visible = True \u2019\u663e\u793aExcle
\u6570\u636e\u8f83\u591a\uff0c\u4e14\u6709\u89c4\u5f8b\u53ef\u5faa\u65f6\uff0c\u8bf7\u4f7f\u7528\u5faa\u73af\u8bed\u53e5
sub main()
if cells(1,"A").value="中国" then cells(1,"B")="111"
if cells(1,"A").value="美国" then cells(1,"B")="1123"
end sub
以此为例,在Excel中读取VBA程序的操作步骤如下(Excel2007为例):
1.选择”开发工具“选项卡,点击”Visual Basic“(或者用快捷键Alt+F11)
2.出现VBA编辑界面,注意看左侧,选择需要运行代码的工作表,此例为Sheet1;双击进入Sheet1相关程序编辑界面
3.在程序编辑区域,复制上上述代码
4.点击上面的三角号,运行程序
5.关闭VBA界面,返回工作表,就可以看到程序运行结果,如下图:
6.如果再次运行程序,可以用快捷键Alt+F8(或者步骤1中的”宏“),出现如下图界面,选择要运行的宏,点击执行即可。
方法一:可以试着用VB书写一段调用SQL Server数据库DTS的代码,然后就可以像直接使用DTS进行数据导入一样就行Excel与SQL Server数据之间的迁移了。
方法二:可以用VB来操作EXCEL,然后一条数据一条数据的插入到数据库中,这种方法不推荐使用,因为VB操作EXCEL是非常慢的,如果数据量太大,很有可能会造成机。
绛旓細浠嬬粛涓嬮潰閫氳繃涓姝ヤ竴姝ョ殑浠嬬粛锛屽浣曢氳繃VB.NET鏉ヨ鍙鏁版嵁锛骞朵笖灏嗘暟鎹鍏ュ埌Excel涓銆傜涓姝ワ細鎵撳紑VS寮鍙戝伐鍏凤紝骞朵笖娣诲姞寮曠敤銆傜劧鍚庨夋嫨銆侻icrosoft Excel 12.0 object library and銆侻icrosoft Excel 14.0 object library銆傜浜屾锛氬垱寤轰竴涓狤xcle鍦ㄤ綘鐨勭數鑴戜腑銆傜涓夋锛氬湪VS涓鍐欏叆濡備笅浠g爜锛欼mports System....
绛旓細鍏ㄩ潰鎺у埗 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) 娣诲姞鏂板伐浣滅翱锛欵xcelID.WorkBooks.Add;4) 鎵撳紑...
绛旓細Excel閲屾寜Alt+F11鎵撳紑Vb缂栬緫鍣紝鍦ㄩ噷闈㈠彲浠ュ缓涓涓獥浣擄紝鏀惧叆鏂囨湰妗嗭紝閫氳繃绋嬪簭鏉ヨ祴鍊锛屽sheets("sheet1").range("a1").value =11銆
绛旓細"sheet1")ex1.Visible = True '浠ヤ笂浠g爜鏄繍琛宔xcel骞舵柊寤轰竴涓枃妗 ex1.Range("a" & ii).Value=textbox.text '鎶妕extbox涓殑鏁版嵁閫愭潯璁板綍鍒EXCEL涓鐨凙鍒,涓鏉¤褰曚竴琛 ii=val(ii)+1'姣忚褰曚竴鏉¤褰,excel琛屾暟+1 褰撶劧浣犵洿鎺ユ妸涓婇潰鐨勪唬鐮佹斁鍒皌imer鎺т欢閲屾槸涓嶈鐨,瑕佺伒娲昏繍鐢ㄥ晩 ...
绛旓細Set xlSheet = xlBook.Worksheets(1) '鎵撳紑EXCEL宸ヤ綔琛 Dim i As Integer xlSheet.Cells(1, Rcount) = Ds(1, Rcount) '鎶婃暟缁刣s(1,rcount)杩欎釜鏁版嵁鍐欏叆Excel涓紝鍐欏叆鐨勫唴瀹瑰叾瀹炲氨鏄爣棰樷滆绠楃粨鏋溾 For i = 2 To Hcount - 1 '鍐嶆妸鏁扮粍ds(i,...
绛旓細浣犵洿鎺ラ変腑鎸囧畾鍗曞厓鏍煎啀杈撳叆鍐呭鍗冲彲锛岃緭鍏ュ畬鎴愬悗锛岀偣鍑绘枃浠惰彍鍗-鍙﹀瓨涓-鍦ㄤ繚瀛樼被鍨嬪閫夋嫨鈥滄枃鏈枃浠讹紙鍒惰〃绗﹀垎闅旓級(*.txt)鈥濇垨鈥淯nicode鏂囨湰(*.txt)鈥濋兘鍙互淇濆瓨涓烘枃鏈枃浠躲
绛旓細鎴戠畝鍗曠殑鍐浜嗕竴涓兘瀹炵幇浣犱笂杩板姛鑳界殑浠g爜锛佺敤Excel鍦–鐩樻牴鐩綍涓嬬敓鎴愪竴涓悕涓 Test.xls 鐨勭┖鏂囦欢,鐒跺悗鍚姩VB6 棣栧厛鍦ㄥ伐绋嬩腑寮曠敤 Microsoft Excel 11.0 Object Library 鍔涙眰绠鍗曪紝鏄剧ず鏁版嵁鎴戜粎浠呯敤浜3涓猅extbox鎺т欢鏉ュ畬鎴愶紒浠g爜濡備笅锛欴im objExcelFile As Excel.Application Dim objWorkBook As Excel....
绛旓細Dim i, j As Integer Dim arr() As Integer = {1, 2, 3, 4, 5, 6, 7, 8, 9} '瑕佸~鍐欑殑鏁版嵁 Dim a As Integer = 3 '瑕佸~鍐欏叡a琛宎鍒 xlapp = CreateObject("Excel.Application")xlbook = xlapp.Workbooks.Open("C:\Users\Administrator\Desktop\vb.xlsx") '妗岄潰宸叉湁涓...
绛旓細浠ヤ笅绋嬪簭杩愯鏃剁偣鍑籧ommand1灏唗ext1鍐呭淇濆瓨鍒版闈1.xls涓璼heet1鐨凙1涓紝鍏跺畠鐨勪綘鑷繁鍔 Dim xlApp As Excel.application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Private Sub command1_click()Set xlApp = CreateObject("Excel.Application") '鍒涘缓EXCEL瀵硅薄 Set xlBook = xl...
绛旓細棣栧厛瑕佹坊鍔燤icros Excel 11.0 Object Library鐨勫紩鐢紝鐒跺悗閫氳繃Dim exlapp As New Excel.Application Dim exlbook As Excel.Workbook Dim exlsheet As Excel.Worksheet Set exlapp = New Excel.Application 瀹氫箟Excel瀵硅薄锛岀劧鍚庨氳繃exlsheet.Range("琛ㄦ牸琛屽垪鍙") =鈥滆缃枃鏈鍐欏叆鍒excel涓紝鏈鍚庨氳繃 ...