vb資料庫excel

㈠ 如何用vb把資料庫中的數據，寫到 excel並導出

Private Sub Command3_Click()
On Error Resume Next
Dim irow, icol, count, i As Integer
Dim irowcount, icolcount As Integer
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim bl As Boolean
Dim key As Integer
Dim RsUserTemp As Recordset
Dim RsOrderTemp As Recordset
Dim a, b
Dim aa As String
aa = Trim(Now)
Set xlApp = CreateObject("excel.application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
'If rs.RecordCount < 1 Then
'MsgBox ("Error 沒有記錄")
'Exit Sub
'End If
irowcount = rs.RecordCount
icolcount = 18
count = 0
rs.MoveFirst
For icol = 0 To 2
'xlSheet.Cells(1, 1).Value = "查詢數據" '加標頭；
Next icol
xlSheet.Cells(1, 1).Value = "時間" '加標頭；
xlSheet.Cells(1, 2).Value = "葯開度" '加標頭；
xlSheet.Cells(1, 3).Value = "葯瞬時流量" '加標頭；
xlSheet.Cells(1, 4).Value = "葯累計流量" '加標頭；
xlSheet.Cells(1, 5).Value = "礦漿濃度" '加標頭；
xlSheet.Cells(1, 6).Value = "礦漿流量" '加標頭
xlSheet.Cells(1, 7).Value = "酸1開度" '加標頭；
xlSheet.Cells(1, 8).Value = "酸1瞬時流量" '加標頭；
xlSheet.Cells(1, 9).Value = "酸1累計流量" '加標頭
xlSheet.Cells(1, 10).Value = "酸2開度" '加標頭；
xlSheet.Cells(1, 11).Value = "酸2瞬時流量" '加標頭；
xlSheet.Cells(1, 12).Value = "酸2累計流量" '加標頭
xlSheet.Cells(1, 13).Value = "酸3開度" '加標頭；
xlSheet.Cells(1, 14).Value = "酸3瞬時流量" '加標頭；
xlSheet.Cells(1, 15).Value = "酸3累計流量" '加標頭
xlSheet.Cells(1, 16).Value = "酸4開度" '加標頭；
xlSheet.Cells(1, 17).Value = "酸4瞬時流量" '加標頭；
xlSheet.Cells(1, 18).Value = "酸4累計流量"
xlSheet.Cells(1, 19).Value = "酸5開度"
xlSheet.Cells(1, 20).Value = "酸5瞬時流量"
xlSheet.Cells(1, 21).Value = "酸5累計流量"
Adodc1.Recordset.MoveFirst
For a = 2 To 200
b = 1
If Not Adodc1.Recordset.EOF Then
xlSheet.Cells(a, b) = Adodc1.Recordset("時間")
xlSheet.Cells(a, b + 1) = Adodc1.Recordset("葯開度")
xlSheet.Cells(a, b + 2) = Adodc1.Recordset("葯瞬時流量")
xlSheet.Cells(a, b + 3) = Adodc1.Recordset("葯累計流量")
xlSheet.Cells(a, b + 4) = Adodc1.Recordset("酸1開度")
xlSheet.Cells(a, b + 5) = Adodc1.Recordset("酸1瞬時流量")
xlSheet.Cells(a, b + 6) = Adodc1.Recordset("酸1累計流量")
xlSheet.Cells(a, b + 7) = Adodc1.Recordset("酸2開度")
xlSheet.Cells(a, b + 8) = Adodc1.Recordset("酸2瞬時流量")
xlSheet.Cells(a, b + 9) = Adodc1.Recordset("酸2累計流量")
xlSheet.Cells(a, b + 10) = Adodc1.Recordset("酸3開度")
xlSheet.Cells(a, b + 11) = Adodc1.Recordset("酸3瞬時流量")
xlSheet.Cells(a, b + 12) = Adodc1.Recordset("酸3累計流量")
xlSheet.Cells(a, b + 13) = Adodc1.Recordset("酸4開度")
xlSheet.Cells(a, b + 14) = Adodc1.Recordset("酸4瞬時流量")
xlSheet.Cells(a, b + 15) = Adodc1.Recordset("酸4累計流量")
xlSheet.Cells(a, b + 16) = Adodc1.Recordset("酸5開度")
xlSheet.Cells(a, b + 17) = Adodc1.Recordset("酸5瞬時流量")
xlSheet.Cells(a, b + 18) = Adodc1.Recordset("酸5累計流量")
Else
Exit For
End If
Adodc1.Recordset.Move 1
Next
rs.MoveFirst
xlSheet.Cells(2, 2).Value = Trim(Text1.Text) & Trim(Text2.Text)
For irow = 0 To irowcount - 1
Set RsUserTemp = New Recordset
RsUserTemp.CursorLocation = adUseClient
RsUserTemp.Open "select * from 狀態數據 " _
& "where user0_id=" & rs!user0_id, Cn, adOpenStatic, adLockReadOnly
xlSheet.Cells(irow + 4, 1).Value = count + 1
xlSheet.Cells(irow + 4, 2).Value = RsUserTemp!user0_id
xlSheet.Cells(irow + 4, 3).Value = RsUserTemp!user0_name
xlSheet.Cells(irow + 4, 4).Value = RsUserTemp!Address
xlSheet.Cells(irow + 4, 5).Value = RsUserTemp!callno1
Set RsUserTemp = Nothing
Set RsOrderTemp = New Recordset
RsOrderTemp.CursorLocation = adUseClient
RsOrderTemp.Open "select * from 狀態數據 where user0_id = " _

If RsOrderTemp.RecordCount = 0 Then
Else

RsOrderTemp.MoveFirst
Do While (Not RsOrderTemp.EOF)
key = 0
key = Val(Mid(str(RsOrderTemp!Order_Time), 6, 2))
Select Case key
Case 0
Exit Do
Case 1
xlSheet.Cells(irow + 4, 6).Value = RsOrderTemp!Order_Amount
Case 2
xlSheet.Cells(irow + 4, 7).Value = RsOrderTemp!Order_Amount
Case 3
xlSheet.Cells(irow + 4, 8).Value = RsOrderTemp!Order_Amount
Case 4
xlSheet.Cells(irow + 4, 9).Value = RsOrderTemp!Order_Amount
Case 5
xlSheet.Cells(irow + 4, 10).Value = RsOrderTemp!Order_Amount
Case 6
xlSheet.Cells(irow + 4, 11).Value = RsOrderTemp!Order_Amount
Case 7
xlSheet.Cells(irow + 4, 12).Value = RsOrderTemp!Order_Amount
Case 8
xlSheet.Cells(irow + 4, 13).Value = RsOrderTemp!Order_Amount
Case 9
xlSheet.Cells(irow + 4, 14).Value = RsOrderTemp!Order_Amount
Case 10
xlSheet.Cells(irow + 4, 15).Value = RsOrderTemp!Order_Amount
Case 11
xlSheet.Cells(irow + 4, 16).Value = RsOrderTemp!Order_Amount
Case 12
xlSheet.Cells(irow + 4, 17).Value = RsOrderTemp!Order_Amount
End Select
RsOrderTemp.MoveNext
Loop
End If
Set RsOrderTemp = Nothing
count = count + 1
rs.MoveNext
If bl Then '因為第一條記錄還未導出所以讓指針回滾；
rs.MovePrevious
End If
Next
xlApp.Visible = True
xlBook.Save
Set xlApp = Nothing
End Sub
這是我的一個代碼，參考一下吧。。。導出到EXCEL的 '網路Hi群&飛度編程學社 1195277

㈡ vb鎬庝箞鏌ユ壘鏁版嵁搴撲腑鐨勬暟鎹瀛樺叆鍒癳xcel閲寁b鎬庝箞鏌ユ壘鏁版嵁搴撲腑鐨勬暟鎹瀛樺叆鍒癳xcel閲岄潰

鎮ㄥ彲浠ヤ嬌鐢╒B璇璦緙栧啓涓涓紼嬪簭鏉ヨ繛鎺ユ暟鎹搴擄紝鎵ц屾煡璇㈣鍙ュ苟灝嗙粨鏋滃瓨鍏Excel銆
棣栧厛錛屾偍闇瑕佷嬌鐢ˋDODB瀵硅薄寤虹珛涓庢暟鎹搴撶殑榪炴帴錛岀劧鍚庝嬌鐢≧ecordset瀵硅薄鎵ц屾煡璇㈠苟鑾峰彇緇撴灉闆嗐
鎺ョ潃錛屾偍鍙浠ヤ嬌鐢‥xcel瀵硅薄鍒涘緩涓涓宸ヤ綔綈匡紝騫跺皢鏌ヨ㈢粨鏋滈愯屽啓鍏ュ埌Excel涓銆傛渶鍚庯紝淇濆瓨騫跺叧闂宸ヤ綔綈垮嵆鍙銆傝繖鏍峰氨鑳藉疄鐜板皢鏁版嵁搴撲腑鐨勬暟鎹瀛樺叆鍒癊xcel閲屻傝繖涓榪囩▼闇瑕佺粏鑷寸殑澶勭悊榪炴帴銆佹煡璇㈠拰Excel鎿嶄綔鐨勫紓甯革紝紜淇濇暟鎹鑳藉熷噯紜鍦頒繚瀛樺埌Excel涓銆
鐢⊿QL璇鍙ワ紝灝嗚〃瀵煎嚭涓篍XCEL銆
SELECT
瀛楁佃〃
INTO
[Excel
8.0;database=D:\ emp\\200803.XLS].Data
FROM 琛

㈢ VB 從sql資料庫中把數據導出到Excel表格中，怎麼寫

SubdataToExcel()
DimconnAsString
DimrsAsNewADODB.Recordset
DimexcelAsObject
DimworkbookAsObject
DimsheetAsObject
Dimi&,j&
'SQL資料庫的連接字串
conn="Provider=SQLOLEDB.1;Datasource=伺服器地址;initialcatalog=資料庫名稱;UserId=資料庫登錄賬號;Password=資料庫登錄賬號"
'打開到sql的連接，讀取xxx表數據到rs
rs.Open"select*fromxxx",conn,adOpenKeyset,adLockReadOnly
'excel對象、工作簿、工作表
Setexcel=CreateObject("Excel.Application")
Setworkbook=excel.workbooks().Add()
Setsheet=workbook.sheets(1)
excel.Visible=True
'循環讀取rs並寫入到excel
Fori=1Tors.Fields.Count
j=1
rs.MoveFirst
DoWhileNotrs.EOF
sheet.cells(j,i)=rs.Fields(i-1).Value
rs.MoveNext
j=j+1
Loop
Next
rs.Close
Setrs=Nothing

EndSub

㈣ vb,連接EXCEl資料庫

Sub ReadExcel()'讀取Excel文件內容
Dim x As Integer
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim AppExcel As New Excel.Application
Set AppExcel = CreateObject("Excel.Application")'創建Excel對象
Set xlBook = AppExcel.Workbooks.Open("D:\數據表.xls") '打開工作簿
If AppExcel Is Nothing Then Exit Sub'錯誤則退出
For x = 1 To AppExcel.Sheets.Count '找到對應的工作表
If AppExcel.Sheets(x).Name ="工作表名" Then
Set xlSheet = AppExcel.Sheets(x)
Exit For
End if

Next
'下面為單元格的定義
xlSheet.Cells([RowIndex],[ColumnIndex]).Value'單元格(行 , 列)
xlSheet.Range("B2:B2").Value'單元格選擇

Set xlSheet = Nothing
Set xlBook = Nothing
Set AppExcel = Nothing
End Sub
'VB6.0必需先引用Microsoft Excel 11.0 Object Library

㈤ VB已讀取到資料庫數據，如何將數據寫入Excel表（OLE控制項）的某一單元格中，謝謝！

可對Ole控制項關聯對象的Excel表格的單元格的Value 屬性賦值來寫入VB已讀到的數據。

Value 屬性，返回或設置一個Variant型數，它指定該屬性之值，此屬性可讀/寫。

以下是實現代碼：

OptionExplicit
DimoBokAsObject'定義oBok為Object數據類型

PrivateSubCommand1_Click()
OLE1.CreateLink"C:zhang2012.xls"'打開要寫入的Excel表
SetoBok=OLE1.object'將對象引用賦給變數或屬性。
oBok.Sheets(1).Range("A1").Value="VB已讀取到資料庫數據"'將數據寫入Excel表的A列第一行
SetoBok=Nothing'使用Nothing關鍵字被將對象變數從實際對象中分離開來。
EndSub

Object 變數存儲為 32 位（4 個位元組）的地址形式，其為對象的引用。利用 Set 語句，聲明為 Object 的變數可以賦值為任何對象的引用。

注意 雖然以 Object 類型聲明的變數足以適應包含對各種對象的引用，但是綁定到變數引用的對象總是在晚期（運行時）綁定。要強迫在早期（編譯時間）綁定的話，須將對象的引用賦值給用特定類名稱聲明的變數。

使用 Nothing 關鍵字被將對象變數從實際對象中分離開來。要使用
Set 語句將 Nothing 賦值給對象變數。例如：

Set MyObject = Nothing

Set 語句，將對象引用賦給變數或屬性。