程序简介:
我有一个excel表,字段为:姓名,身份证号,专业,级别,证书号
想通过vb实现,导入sql,表已经存在,字段有:id,comname,username,user_identity,user_speciality,user_grade,certificate_id,drsj,其中id为自加,drsj默认为当前时间,comname是通过commondialog.filename获得的
我写的程序如下:
Public a, b As StringPrivate Sub Command1_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=lw;password=12345;Initial Catalog=yljg;Data Source=SQL1"
conn.Open
strSQL = "insert into labor (comname,username,user_identity,user_speciality,user_grade,certificate_id) values('" & b & "',select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE='" & Label3.Caption & "',sheet1$)"
rs.Open strSQL, conn, 3, 3
End SubPrivate Sub Command2_Click()
CommonDialog1.DialogTitle = "打开数据源"
CommonDialog1.Filter = "Excel Files(*.xls)|*.xls"
CommonDialog1.ShowOpen
Label3.Caption = CommonDialog1.FileName
a = Label3.Caption
End SubPrivate Sub Form_Load()
Label2.Caption = newcomfrm.result
b = Label2.Caption
End Sub
问题一:
strSQL = "insert into labor (comname,username,user_identity,user_speciality,user_grade,certificate_id) values('" & b & "',select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE='" & Label3.Caption & "',sheet1$)"
上面这句,语法错在哪了?问题二:
如何实现往excel添加一列数据,不改变其他列?
我有一个excel表,字段为:姓名,身份证号,专业,级别,证书号
想通过vb实现,导入sql,表已经存在,字段有:id,comname,username,user_identity,user_speciality,user_grade,certificate_id,drsj,其中id为自加,drsj默认为当前时间,comname是通过commondialog.filename获得的
我写的程序如下:
Public a, b As StringPrivate Sub Command1_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=lw;password=12345;Initial Catalog=yljg;Data Source=SQL1"
conn.Open
strSQL = "insert into labor (comname,username,user_identity,user_speciality,user_grade,certificate_id) values('" & b & "',select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE='" & Label3.Caption & "',sheet1$)"
rs.Open strSQL, conn, 3, 3
End SubPrivate Sub Command2_Click()
CommonDialog1.DialogTitle = "打开数据源"
CommonDialog1.Filter = "Excel Files(*.xls)|*.xls"
CommonDialog1.ShowOpen
Label3.Caption = CommonDialog1.FileName
a = Label3.Caption
End SubPrivate Sub Form_Load()
Label2.Caption = newcomfrm.result
b = Label2.Caption
End Sub
问题一:
strSQL = "insert into labor (comname,username,user_identity,user_speciality,user_grade,certificate_id) values('" & b & "',select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE='" & Label3.Caption & "',sheet1$)"
上面这句,语法错在哪了?问题二:
如何实现往excel添加一列数据,不改变其他列?
解决方案 »
- 为什么我的程序里kill t总是删除不了呢?
- text问题,text输出问题
- vb 如何发布dll?
- 紧急寻找:各位推荐一款画流程图的控件!!!
- Beyond,一次迟到的告别
- 请问用什么函数?
- 我窗口中用到了一个控件,调试时是正常的,可编译成exe文件后窗口打不开,何故?
- 怎么样多次使用form1.printform方法呀?100分送上!
- 怎样通过函数地址调用函数?
- ◆◆◆如何实现像在vb窗口中放入控件时,控件的周边有几个小方块,拖动方快可以改变控件大小。我如何编程才可以在程序运行时也可以达到这样的效果?比如任意改动一幅图象的大小!
- 通过api修改用户的属性:选取密码永不过期?
- vb操作IIS,如何判断网站是否启动
工程->引用->Microsoft Excel 9.0 Object Library (后面为版本号)
具体的对EXCEL的操作,你可以到EXCEL中录制宏看看吧~~~~~~`
定义函数如下:
Public Function Read_Excel(ByVal sFile As String) As ADODB.Recordset On Error GoTo fix_err
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sconn As String rs.CursorLocation = adUseClient
rs.CursorType = adOpenKeyset
rs.LockType = adLockBatchOptimistic sconn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & sFile
rs.Open "SELECT * FROM [sheet1$]", sconn
Set Read_Excel = rs
Set rs = Nothing
Exit Function
fix_err:
Debug.Print Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear
End Function只要调用此含数即可:
Private Sub Command5_Click()
Set rsyl = Read_Excel("d:\a.xls")
Set TDBGrid2.DataSource = rsyl
End Sub
能不能把你说的东东说具体点,或者弄一个来看看啊
程序简介:有几百家的企业,每家企业的员工资料在excel里面,我要通过vb写一个程序,让excel的数据可以导入到sql
Dim Book As New Excel.Workbook
Dim Sheet As New Excel.Worksheet
Dim s As String
Set Book = exl.Workbooks.Open(App.Path & "文件名称")
Set Sheet = Book.Sheets(1)
With Sheet
'利用循环读出excel中的数据,然后利用rs的addnew增加进表
For r = 1 To .Rows
For i = 1 To .Columns
'这里可以利用rs addnew方法,然后把数据读入相应的字段
s = .Cells(r, i)
Next
Next
End With
经过反复学习,修改程序如下,可是还是不能执行!Public m, n As StringPrivate Sub Command1_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=lw;password=12345;Initial Catalog=yljg;Data Source=SQL1"
conn.Open
Dim exl As New Excel.Application
Dim Book As New Excel.Workbook
Dim Sheet As New Excel.Worksheet
Dim a, b, c, d, e As String
Set Book = exl.Workbooks.Open(m)
Set Sheet = Book.Sheets(1)
With Sheet
'利用循环读出excel中的数据,然后利用rs的addnew增加进表
For r = 1 To .Rows
For i = 1 To 5
'这里可以利用rs addnew方法,然后把数据读入相应的字段
a = .Cells(r, 1)
b = .Cells(r, 2)
c = .Cells(r, 3)
d = .Cells(r, 4)
e = .Cells(r, 5)
Sql = "Select * from labor"
rs.Open Sql, conn, 3, 3
rs.AddNew
rs.Fields(1) = n
rs.Fields(2) = a
rs.Fields(3) = b
rs.Fields(4) = c
rs.Fields(5) = d
rs.Fields(6) = e
rs.Update
Next
Next
End With
End SubPrivate Sub Command2_Click()
CommonDialog1.DialogTitle = "打开数据源"
CommonDialog1.Filter = "Excel Files(*.xls)|*.xls"
CommonDialog1.ShowOpen
Label3.Caption = CommonDialog1.FileName
m = Label3.Caption
End SubPrivate Sub Form_Load()
Label2.Caption = newcomfrm.result
n = Label2.Caption
End Sub
Dim exlrs As New ADODB.Recordset
Dim I As Integer, J As Integer
'用ADO打开Excel的连接串
exldb.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;Dbq=" & m & ";"
exldb.Open
exlrs.Open "select * from [Sheet1$]", exldb, adOpenStatic, adLockOptimistic
exlrs.MoveFirst For I = 1 To exlrs.RecordCount
rs.AddNew
rs.Fields(1).Value = Label2.Caption
rs.Fields(7).Value = Now()
For J = 2 To 6
rs.Fields(J).Value = exlrs.Fields(J - 1)
Next
rs.Update
exlrs.MoveNext
Next