用VB程序如何导入Excel文件,我们公司前一段一直用的导入Txt,现在让导入Excel,请教大家了,多谢
解决方案 »
- PaintPicture 后的保存问题 急请教
- 大家好 请问关于textbox 的问题
- 关闭窗体的问题
- 控件的放置Z顺序问题?急!求高手解决。
- 我现在想用VB编写一只演示光碟,客户要求大概是给我企业的LOGO和具体资料和图片介绍,光碟可以自动播放,先播放一段AVI,然后进入主界面
- WINDOWS 98 的奇怪问题
- Form窗体上有很8个picture控件,每个picture控件开始有一张图,当鼠标移入或移出每一个picture控件时,变换成另一张图。
- 我想在程序中增加一块数据库备份的功能请问如何去做即简单又实用
- 小女子问大家一个关于打印的问题?
- 关于“sin()”函数
- 300分继续:关于activeX控件调用msword9.olb里对象发生“Dll加载错误“的问题!
- 将DataGrid中的内容导入到Excel中出现的问题
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=jtaf;Password=6089280;Initial Catalog=jtaf;Data Source=202.115.166.32"
cn.CursorLocation = adUseClient
cn.Open
cn.Execute ("insert into OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from dict_content') select * from dict_content")
cn.Close
Set cn = Nothing
End Sub
Private Sub cmdPrintExcel_Click()
Dim sFileName As String
Dim xlapp As Object
Dim xlBook As Object
Dim xlsheet As Object
Dim sWIPDailyTemp As String
On Error GoTo errorHandle:
sWIPDailyTemp = App.Path + "\report\CycleTimeReport.xls" 'RCFile
frmSYSWait.Show
frmSYSWait.proBar.Value = 5
sFileName = App.Path + "\temp\CycleTimeReport.xls" Set xlapp = CreateObject("Excel.Application")
Set xlBook = xlapp.Workbooks.Open(sWIPDailyTemp)
Set xlsheet = xlBook.Worksheets(1)
frmSYSWait.proBar.Value = 25
Call subPrintCycleTimeSummary(xlapp, xlsheet) '列印
xlapp.DisplayAlerts = False If MsgBox(" 是否要列印?", vbYesNo) = vbYes Then
xlsheet.PrintOut 'To:=iPage, Copies:=1, Collate:=True
End If
If MsgBox(" 是否要存档?", vbYesNo) = vbYes Then
xlBook.SaveAs sFileName
End If
frmSYSWait.proBar.Value = 85
xlapp.Visible = True
Set xlapp = Nothing ' 再释放该引用
Set xlBook = Nothing
Set xlsheet = Nothing
DoEvents
frmSYSWait.proBar.Value = 95
Unload frmSYSWait
Exit Sub
errorHandle: Unload frmSYSWait
Set xlapp = Nothing ' 再释放该引用
Set xlBook = Nothing
Set xlsheet = Nothing
MsgBox Err.Number & " " & Err.Description
End Sub
Private Sub subPrintCycleTimeSummary(xlapp As Object, xlsheet As Object)
Dim iRow As Integer
Dim iCol As Integer
Dim strRuncardType As String
Dim strMark As String
'列印表头
xlsheet.Range("A2").Value = "Print Time : " & CStr(Now)
xlsheet.Range("A3").Value = "Facility:TCP"
xlsheet.Range("A4").Value = "Query Interval:" & Format(DTPStartDate.Value, "YYYY/MM/DD") & " " & Format(DTPStartTime.Value, "hh:mm") & " ~ " & Format(DTPEndDate.Value, "YYYY/MM/DD") & " " & Format(DTPEndTime.Value, "hh:mm")
If OptEx.Value = True Then
xlsheet.Range("A5").Value = "Hold Time:Exclude"
Else
xlsheet.Range("A5").Value = "Hold Time:Include"
End If
' xlsheet.PageSetup.RightFooter = "制表人:" & ShowUserName
frmSYSWait.proBar.Value = 50 xlsheet.Range("A8").Select
xlapp.ActiveSheet.Paste
xlsheet.Range("A1").Select frmSYSWait.proBar.Value = 75
End Sub
能不能把你的思路在说的详细一点,我还是不太明白,多谢了
-------------------------------------------------------------
不好意思,上面的写错了,改一下:
cn.Execute ("insert into OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\Test.xls;','select * from [Sheet1$]')(id,name)
select id,name from serv_user")
Dim cnSql As New ADODB.Connection, cnExcel As New ADODB.Connection, rsSql As New ADODB.Recordset, rsExcel As New ADODB.Recordset, i%
'打开SQL数据库的连接,具体的需要改一下
cnSql.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=数据库;Data Source=SQL服务器别名/IP"
rsSql.CursorLocation = adUseClient
'获取SQL里的Table1的所有记录,准备导出入Excel
rsSql.Open "select * from table1", cnSql, adOpenDynamic, adLockReadOnly
'连接C:\Test.xls
cnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties=Excel 8.0"
rsExcel.CursorLocation = adUseClient
'打开Excel的Sheet1表,准备导入数据
rsExcel.Open "select * from [Sheet1$]", cnExcel, adOpenDynamic, adLockPessimistic
rsSql.MoveFirst
While Not rsSql.EOF
rsExcel.AddNew
For i = 0 To rsSql.Fields.Count - 1
rsExcel(i) = rsSql(i) '给Excel的记录集赋值
Next
rsSql.MoveNext
Wend
rsExcel.UpdateBatch '批量更新记录集 Set rsSql = Nothing
Set rsExcel = Nothing
cnSql.Close
Set cnSql = Nothing
cnExcel.Close
Set cnExcel = Nothing
End Sub
直接调用他的方法啊。技术支持:[email protected]
z支持你的,这个东西我试过,绝对通过,但是导入的是整个EXCEL。
Dim xlBook As Variant
Dim xlSheet As VariantCommonDialog1.FileName = "电子表文件名.xls"
CommonDialog1.Filter = "Excel文件 (*.xls)|*.xlt|"
CommonDialog1.ShowSaveSet xlApp = CreateObject("Excel.Application")
xlApp.displayalerts = False
Set xlBook = xlApp.Workbooks.Open(App.Path + "\表格模板.xlt")
xlBook.SaveCopyAs (CommonDialog1.FileName)
xlBook.Close
Set xlBook = xlApp.Workbooks.Open(CommonDialog1.FileName)
Set xlSheet = xlBook.Worksheets(1)xlApp.Visible = False
For i = 1 To Adodc.Recordset.RecordCount
xlSheet.cells(i, 1) = adodc3.Recordset.Fields("字段名1").Value
xlSheet.cells(i, 2) = adodc3.Recordset.Fields("字段名2").Value
.
.
.
xlSheet.cells(i, n) = adodc3.Recordset.Fields("字段名n").Value
If Not Adodc.Recordset.EOF Then Adodc.Recordset.MoveNext
Next i(注:加入CommonDialog对象)
Dim xlBook As Variant
Dim xlSheet As VariantCommonDialog1.FileName = "电子表文件名.xls"
CommonDialog1.Filter = "Excel文件 (*.xls)|*.xlt|"
CommonDialog1.ShowSaveSet xlApp = CreateObject("Excel.Application")
xlApp.displayalerts = False
Set xlBook = xlApp.Workbooks.Open(App.Path + "\表格模板.xlt")
xlBook.SaveCopyAs (CommonDialog1.FileName)
xlBook.Close
Set xlBook = xlApp.Workbooks.Open(CommonDialog1.FileName)
Set xlSheet = xlBook.Worksheets(1)xlApp.Visible = False
For i = 1 To Adodc.Recordset.RecordCount
xlSheet.cells(i, 1) = adodc3.Recordset.Fields("字段名1").Value
xlSheet.cells(i, 2) = adodc3.Recordset.Fields("字段名2").Value
.
.
.
xlSheet.cells(i, n) = adodc3.Recordset.Fields("字段名n").Value
If Not Adodc.Recordset.EOF Then Adodc.Recordset.MoveNext
Next i(注:加入CommonDialog对象)
在导出过程中,有一个选项(保存DTS包[存为Visual Basic 文件]),选上,在数据导出完成后,它会生成一个类模块,里面有导出的源代码,你使用来完成数据导出
免费的学习、交流、源码、工具下载网站,欢迎大家访问!
http://www.j2soft.cn/
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=jtaf;Password=登陆密码;Initial Catalog=sql里的数据库;Data Source=sql服务器别名或IP"
cn.CursorLocation = adUseClient
cn.Open '导入Excel用下面这句:
cn.Execute("insert into OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\Test.xls;','select * from [Sheet1$]') select top 25000 * from table1") '导入Access用下面这句:
'cn.Execute ("insert into OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\Test.mdb','select * from 表1') select top 25000 * from table1")
cn.Close
Set cn = Nothing
End Sub cn.Close
Set cn = Nothing
End Sub