數據庫從VB導入到Excel '請你自己加個CommonDialog控件Private Sub Command3_Click() Dim objFileSystem As Object Dim objExcelText As Object Dim strTableString As String, i As Integer, strFileName As String Dim pubConn As New ADODB.Connection Dim rsTable As New ADODB.Recordset Dim strConn As String Dim strSQL As String strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=develop; password=12345;Data Source=ServerNmae" pubConn.Open strConn rsTable.CursorLocation = adUseClient strSQL = "select top 10 * from gate_register" rsTable.Open strSQL, pubConn, adOpenDynamic, adLockOptimistic
For i = 0 To rsTable.Fields.Count - 1 strTableString = strTableString & rsTable.Fields(i).Name & Chr(9) '獲取字段名 Next strTableString = strTableString & rsTable.GetString '字段名+數據庫的記錄
Set objFileSystem = CreateObject("Scripting.FileSystemObject") Set objExcelText = objFileSystem.createtextfile(strFileName, True) objExcelText.writeline (strTableString)
objExcelText.Close Set objFileSystem = Nothing End Sub
還有一種方法是先設計好Excel文件模塊,通過程序代碼把數據表裡的內容發送到Excel文件裡On Error Resume Next Dim Re As ADODB.Recordset '¨C­¶¼Æ¾Ú¨Ó·½ Dim RePath As ADODB.Recordset '¸ô®|¡A±q¼Æ¾Ú®w±o¨Ó Dim Cn As ADODB.Connection Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim strPath As String Dim lngReNum As Long Dim strExcelFilePath As String Dim lngPages As Long '­¶¼Æ Dim i As Long '¥Î¤_´`Àô Dim n As Long '¥Î¤_´`Àô Dim strSql As String Dim strWhere As String Set Cn = New ADODB.Connection Set Re = New ADODB.Recordset Cn.Open strPubConnect Cn.CommandTimeout = 20000Set RePath = New ADODB.Recordset 'Excel¤å¥ó¸ô®| RePath.Open "select isnull(mean,'') from parameter_tab where parameter_name='mode_execl_path' ", Cn, adOpenStatic, adLockReadOnly, adCmdText If RePath.RecordCount <> 0 Then strExcelFilePath = Trim(RePath(0).Value & "") End If RePath.Close Set RePath = NothingstrExcelFilePath = strExcelFilePath & "StuffAnalyse.xls" dialog_Excel.DefaultExt = "*.xls" dialog_Excel.Filter = "Excel(*.xls)|*.xls" dialog_Excel.ShowSave strPath = dialog_Excel.FileName If strPath = "" Then Exit Sub Set xlApp = New Excel.Application Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open(strExcelFilePath) Set xlSheet = xlBook.Worksheets(1) xlApp.DataEntryMode = xlOff
If Trim(CQuery.arrSqlWhere(0)) <> "" Then strWhere = " WHERE " & CQuery.arrSqlWhere(0) End IfstrSql = " SELECT A.class_no,A.kind_no,A.item_no,A.character_no,A.cn_name,ISNULL(A.num,0) num," & _ " ISNULL(C.allot_num,0) AS allot_num,ISNULL(A.num,0)-ISNULL(C.allot_num,0) AS can_num" & _ " FROM" & _ " (SELECT tabStuffStorage.class_no,tabStuffStorage.kind_no,tabStuffStorage.item_no,tabStuffStorage.character_no,f_product_name.cn_name,SUM(tabStuffStorage.num) num" & _ " FROM tabStuffStorage" & _ " LEFT JOIN f_product_name ON tabStuffStorage.class_no=f_product_name.class_no" & _ " AND tabStuffStorage.kind_no=f_product_name.kind_no AND tabStuffStorage.item_no=f_product_name.item_no" & _ " AND tabStuffStorage.character_no=f_product_name.character_no AND f_product_name.enable='1'" & _ " LEFT JOIN a_product_name ON f_product_name.a_no=a_product_name.a_no " & _ " AND f_product_name.class_no=a_product_name.class_no AND a_product_name.enable='1'" & strWhere & _ " GROUP BY tabStuffStorage.class_no,tabStuffStorage.kind_no,tabStuffStorage.item_no,tabStuffStorage.character_no,f_product_name.cn_name" & _ " )A" strSql = strSql & _
" LEFT JOIN" & _ " (SELECT B.class_no,B.kind_no,B.item_no,B.character_no,sum(ISNULL(B.require_num,0))-sum(ISNULL(B.give_out_num,0)) AS allot_num" & _ " FROM" & _ " (SELECT tabProduceMaterial.class_no,tabProduceMaterial.kind_no,tabProduceMaterial.item_no,tabProduceMaterial.character_no,tabProduceMaterial.order_id," & _ " tabProduceMaterial.product_no,ISNULL(tabProduceMaterial.require_num,0) AS require_num,ISNULL(tabProduceMaterial.give_out_num,0) as give_out_num" & _ " FROM tabProduceMaterial" & _ " LEFT JOIN tabShipmentChild ON tabProduceMaterial.order_id=tabShipmentChild.order_id" & _ " AND tabProduceMaterial.product_no=tabShipmentChild.product_no AND tabShipmentChild.enable='1'" & _ " WHERE ISNULL(tabProduceMaterial.end_sign,'')<>'O' AND tabProduceMaterial.class_no='A' AND tabProduceMaterial.enable='1'" & _ " AND ISNULL(tabShipmentChild.goout_tenor,'')<>'B'" & _ " ) B" & _ " GROUP BY B.class_no,B.kind_no,B.item_no,B.character_no" & _ " )C" & _ " ON A.class_no= C.class_no AND A.kind_no= C.kind_no AND A.item_no= C.item_no AND A.character_no= C.character_no" & _ " ORDER BY A.class_no,A.kind_no,A.item_no,A.character_no"
Screen.MousePointer = 11 Re.Open strSql, Cn, adOpenStatic, adLockReadOnly, adCmdTextIf Re.RecordCount = 0 Then MsgBox "¨S¦³°O¿ý!" Re.Close Cn.Close Screen.MousePointer = 0 Exit Sub End IfIf (Re.RecordCount / 45) - Int(Re.RecordCount / 45) > 0 Then lngPages = Int(Re.RecordCount / 45) + 1 Else lngPages = Int(Re.RecordCount / 45) End If If lngPages = 0 Then lngPages = 1 End If For i = 1 To lngPages - 1 xlSheet.Range("A3:I47").Copy Destination:=xlSheet.Range("A" & Trim(Str(45 * i + 1))) Next Re.MoveFirst i = 0 For n = 1 To Re.RecordCount If n = 45 * (i + 1) + 1 Then i = i + 1 End If xlSheet.Cells(45 * i + 2 + n - (45 * i), 1) = Re("class_no") & "" xlSheet.Cells(45 * i + 2 + n - (45 * i), 2) = Re("kind_no") & "" xlSheet.Cells(45 * i + 2 + n - (45 * i), 3) = Re("item_no") & "" xlSheet.Cells(45 * i + 2 + n - (45 * i), 4) = Re("character_no") & "" xlSheet.Cells(45 * i + 2 + n - (45 * i), 5) = Re("cn_name") & "" xlSheet.Cells(45 * i + 2 + n - (45 * i), 6) = Re("num") & "" xlSheet.Cells(45 * i + 2 + n - (45 * i), 7) = Re("allot_num") & "" xlSheet.Cells(45 * i + 2 + n - (45 * i), 9) = Re("can_num") & "" Re.MoveNext Next nxlBook.SaveAs strPath xlApp.Visible = TrueSet Re = Nothing Set Cn = Nothing Set xlApp = Nothing Re.Close Screen.MousePointer = 0
各位大哥鼎力相助,小弟深为感动! 小弟还有一个问题就是我看各位大哥采用的数据导入方法都为单个cell赋值的方法, 这个方法我没有采用,因为小弟一次处理的数据量少则5,6万多则10几万,采用上述方法,效率让顾客很不满意,小弟采用的是创建动态链接服务器的方法,即对于要导入数据的Excel表,在导入数据前,我先创建到该excel表的动态链接服务器,比如创建的服务器名称为:ExcelSource 导入数据的程序代码为: insert into ExcelSource...[DOWNLOAD$](负担部门编号,负担部门名称,发行NO,品番,品名,验收年月日,单位,数量,基准单价) select FDepid,Fdepname,fno,Fdm,Fname,Fydate,Funit,Fnumber,FJprice from Trans_GPSck这种方法效率很高。这种方法如何解决呢??我将要导入数据的Excel表创建为模版,并预先设定某个cell为数字格式,但导出的数据仍未字符!!!晕死了! 各位大哥再帮帮小弟吧!小弟快撑不住了!
'請你自己加個CommonDialog控件Private Sub Command3_Click()
Dim objFileSystem As Object
Dim objExcelText As Object
Dim strTableString As String, i As Integer, strFileName As String
Dim pubConn As New ADODB.Connection
Dim rsTable As New ADODB.Recordset
Dim strConn As String
Dim strSQL As String strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=develop; password=12345;Data Source=ServerNmae"
pubConn.Open strConn
rsTable.CursorLocation = adUseClient
strSQL = "select top 10 * from gate_register"
rsTable.Open strSQL, pubConn, adOpenDynamic, adLockOptimistic
For i = 0 To rsTable.Fields.Count - 1
strTableString = strTableString & rsTable.Fields(i).Name & Chr(9) '獲取字段名
Next
strTableString = strTableString & rsTable.GetString '字段名+數據庫的記錄
cmDialog.CancelError = False
cmDialog.FileName = "FileName" '默認生成的文件名
cmDialog.DialogTitle = "Save Export File"
cmDialog.Filter = "Excel (*.xls)|*.xls|文本文件(*.DBF)|*.DBF|檔案文件(*.doc)|*.doc|所有文件(*.*)|*.*"
cmDialog.DefaultExt = "*.xls"
cmDialog.ShowSave
strFileName = cmDialog.FileName
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objExcelText = objFileSystem.createtextfile(strFileName, True)
objExcelText.writeline (strTableString)
objExcelText.Close
Set objFileSystem = Nothing
End Sub
Dim Re As ADODB.Recordset '¨C­¶¼Æ¾Ú¨Ó·½
Dim RePath As ADODB.Recordset '¸ô®|¡A±q¼Æ¾Ú®w±o¨Ó
Dim Cn As ADODB.Connection
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim strPath As String
Dim lngReNum As Long
Dim strExcelFilePath As String
Dim lngPages As Long '­¶¼Æ
Dim i As Long '¥Î¤_´`Àô
Dim n As Long '¥Î¤_´`Àô
Dim strSql As String
Dim strWhere As String
Set Cn = New ADODB.Connection
Set Re = New ADODB.Recordset
Cn.Open strPubConnect
Cn.CommandTimeout = 20000Set RePath = New ADODB.Recordset
'Excel¤å¥ó¸ô®|
RePath.Open "select isnull(mean,'') from parameter_tab where parameter_name='mode_execl_path' ", Cn, adOpenStatic, adLockReadOnly, adCmdText
If RePath.RecordCount <> 0 Then
strExcelFilePath = Trim(RePath(0).Value & "")
End If
RePath.Close
Set RePath = NothingstrExcelFilePath = strExcelFilePath & "StuffAnalyse.xls"
dialog_Excel.DefaultExt = "*.xls"
dialog_Excel.Filter = "Excel(*.xls)|*.xls"
dialog_Excel.ShowSave
strPath = dialog_Excel.FileName
If strPath = "" Then Exit Sub
Set xlApp = New Excel.Application
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(strExcelFilePath)
Set xlSheet = xlBook.Worksheets(1)
xlApp.DataEntryMode = xlOff
If Trim(CQuery.arrSqlWhere(0)) <> "" Then
strWhere = " WHERE " & CQuery.arrSqlWhere(0)
End IfstrSql = " SELECT A.class_no,A.kind_no,A.item_no,A.character_no,A.cn_name,ISNULL(A.num,0) num," & _
" ISNULL(C.allot_num,0) AS allot_num,ISNULL(A.num,0)-ISNULL(C.allot_num,0) AS can_num" & _
" FROM" & _
" (SELECT tabStuffStorage.class_no,tabStuffStorage.kind_no,tabStuffStorage.item_no,tabStuffStorage.character_no,f_product_name.cn_name,SUM(tabStuffStorage.num) num" & _
" FROM tabStuffStorage" & _
" LEFT JOIN f_product_name ON tabStuffStorage.class_no=f_product_name.class_no" & _
" AND tabStuffStorage.kind_no=f_product_name.kind_no AND tabStuffStorage.item_no=f_product_name.item_no" & _
" AND tabStuffStorage.character_no=f_product_name.character_no AND f_product_name.enable='1'" & _
" LEFT JOIN a_product_name ON f_product_name.a_no=a_product_name.a_no " & _
" AND f_product_name.class_no=a_product_name.class_no AND a_product_name.enable='1'" & strWhere & _
" GROUP BY tabStuffStorage.class_no,tabStuffStorage.kind_no,tabStuffStorage.item_no,tabStuffStorage.character_no,f_product_name.cn_name" & _
" )A"
strSql = strSql & _
" (SELECT B.class_no,B.kind_no,B.item_no,B.character_no,sum(ISNULL(B.require_num,0))-sum(ISNULL(B.give_out_num,0)) AS allot_num" & _
" FROM" & _
" (SELECT tabProduceMaterial.class_no,tabProduceMaterial.kind_no,tabProduceMaterial.item_no,tabProduceMaterial.character_no,tabProduceMaterial.order_id," & _
" tabProduceMaterial.product_no,ISNULL(tabProduceMaterial.require_num,0) AS require_num,ISNULL(tabProduceMaterial.give_out_num,0) as give_out_num" & _
" FROM tabProduceMaterial" & _
" LEFT JOIN tabShipmentChild ON tabProduceMaterial.order_id=tabShipmentChild.order_id" & _
" AND tabProduceMaterial.product_no=tabShipmentChild.product_no AND tabShipmentChild.enable='1'" & _
" WHERE ISNULL(tabProduceMaterial.end_sign,'')<>'O' AND tabProduceMaterial.class_no='A' AND tabProduceMaterial.enable='1'" & _
" AND ISNULL(tabShipmentChild.goout_tenor,'')<>'B'" & _
" ) B" & _
" GROUP BY B.class_no,B.kind_no,B.item_no,B.character_no" & _
" )C" & _
" ON A.class_no= C.class_no AND A.kind_no= C.kind_no AND A.item_no= C.item_no AND A.character_no= C.character_no" & _
" ORDER BY A.class_no,A.kind_no,A.item_no,A.character_no"
Screen.MousePointer = 11
Re.Open strSql, Cn, adOpenStatic, adLockReadOnly, adCmdTextIf Re.RecordCount = 0 Then
MsgBox "¨S¦³°O¿ý!"
Re.Close
Cn.Close
Screen.MousePointer = 0
Exit Sub
End IfIf (Re.RecordCount / 45) - Int(Re.RecordCount / 45) > 0 Then
lngPages = Int(Re.RecordCount / 45) + 1
Else
lngPages = Int(Re.RecordCount / 45)
End If
If lngPages = 0 Then
lngPages = 1
End If
For i = 1 To lngPages - 1
xlSheet.Range("A3:I47").Copy Destination:=xlSheet.Range("A" & Trim(Str(45 * i + 1)))
Next
Re.MoveFirst
i = 0
For n = 1 To Re.RecordCount
If n = 45 * (i + 1) + 1 Then
i = i + 1
End If
xlSheet.Cells(45 * i + 2 + n - (45 * i), 1) = Re("class_no") & ""
xlSheet.Cells(45 * i + 2 + n - (45 * i), 2) = Re("kind_no") & ""
xlSheet.Cells(45 * i + 2 + n - (45 * i), 3) = Re("item_no") & ""
xlSheet.Cells(45 * i + 2 + n - (45 * i), 4) = Re("character_no") & ""
xlSheet.Cells(45 * i + 2 + n - (45 * i), 5) = Re("cn_name") & ""
xlSheet.Cells(45 * i + 2 + n - (45 * i), 6) = Re("num") & ""
xlSheet.Cells(45 * i + 2 + n - (45 * i), 7) = Re("allot_num") & ""
xlSheet.Cells(45 * i + 2 + n - (45 * i), 9) = Re("can_num") & ""
Re.MoveNext
Next nxlBook.SaveAs strPath
xlApp.Visible = TrueSet Re = Nothing
Set Cn = Nothing
Set xlApp = Nothing
Re.Close
Screen.MousePointer = 0
小弟还有一个问题就是我看各位大哥采用的数据导入方法都为单个cell赋值的方法,
这个方法我没有采用,因为小弟一次处理的数据量少则5,6万多则10几万,采用上述方法,效率让顾客很不满意,小弟采用的是创建动态链接服务器的方法,即对于要导入数据的Excel表,在导入数据前,我先创建到该excel表的动态链接服务器,比如创建的服务器名称为:ExcelSource 导入数据的程序代码为:
insert into ExcelSource...[DOWNLOAD$](负担部门编号,负担部门名称,发行NO,品番,品名,验收年月日,单位,数量,基准单价) select FDepid,Fdepname,fno,Fdm,Fname,Fydate,Funit,Fnumber,FJprice from Trans_GPSck这种方法效率很高。这种方法如何解决呢??我将要导入数据的Excel表创建为模版,并预先设定某个cell为数字格式,但导出的数据仍未字符!!!晕死了!
各位大哥再帮帮小弟吧!小弟快撑不住了!