Excel VBA.doc_第1页
Excel VBA.doc_第2页
Excel VBA.doc_第3页
Excel VBA.doc_第4页
Excel VBA.doc_第5页
已阅读5页,还剩18页未读, 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

Dim conn As New ADODB.Connection 创建一个 Connection 实例,在这里使用New等于将Dim和Set合并为一段代码执行 Dim rs As ADODB.Recordset 创建一个 Recordset 实例,不使用New 是因为,经常需要重复使用Set,因此没必要在这里使用 Dim CnStr As String, Sql As String 创建两个字符串变量分别存放两个集合的SQL语句代码段 1、装载数据库(不属于Recordset集合) = Dim FileNamw$, DbIp$, DbName$, DbUser$, DbPw$ 以上5个字符串变量分别表示文件路径和文件名、数据库地址、数据库名、数据操作员用户名、操作员密码 FileName = App.Path & 数据库名 DbIp = 数据库地址 DbName = 数据库名 DbUser = 数据操作员用户名 DbPw = 操作员密码 以上变量根据数据库类型的不同而不同,有可能只需要1至两个变量 1)连接Access数据库: - CnStr = PROVIDER=microsoft.jet.oledb.3.51;persist security info =false;data source= & FileName & ;Jet OLEDB:Database Password= & DbPw 2)连接Oracle数据库: - CnStr = PROVIDER=MSDAORA.1;Password= & DbPw & ;User ID= & DbUser & ;Data Source= & FileName & ;Persist Security Info=True 其中: PASSWORD: 密码 User ID: 用户号 Data Source: 数据库名 Persist Security Info: Provider: 3)连接VF的DBF库: - CnStr = PROVIDER=MSDASQL.1;Persist Security Info=False;Driver=Microsoft Visual FoxPro Driver;UID= & DbUser & ;SourceDB= & FileName & ;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine; 4)连接SQL的数据库 - CnStr = PROVIDER=MSDataShape;Data PROVIDER=MSDASQL;uid= & DbUser & ;pwd= & DbPw & ;DRIVER=SQL Server;DATABASE= & DbName & ;WSID=GQSOFT;SERVER= & DbIP 也可以使用这段简易代码 CnStr = Provider=SQLOLEDB;Data Source= & DbIp & ;DATABASE= & DbName & ;UID= & DbUser & ;pwd= & DbPw Conn.Open cnstr 使用 Connection 集合的 Open 方法 与数据库建立连接 2、Recordset集合的常用方法 = 1)打开一个表 - Sql = select * from 表名 SQL查询语句 Set rs = New ADODB.Recordset 新建一个实例 rs.Open Sql, conn 使用 Open 方法打开数据库中的一个表 注意,这种打开方式只能使用 rs.MoveNext (即,向后移动行坐标)而不能像其他方向,并且不能修改数据内容 rs.Open Sql, conn,1 虽然只加了个“1”,但这种方法可以向任何方向移动行坐标。 以下参数代表了这个可选值的含义 0 = adOpenForwardOnly (默认值)打开仅向前类型游标。 1 = adOpenKeyset 打开键集类型游标。 2 = adOpenDynamic 打开动态类型游标。 3 = adOpenStatic 打开静态类型游标。 虽然使用以上方法可以可以实现行坐标(游标)的任意移动,但是仍然无法写入数据。因此需要进一步的对Open 方法进行完善 rs.Open Sql, conn, 1, 3 后面的3是确定读写权限的 以下参数代表了这个可选值的含义 1 = adLockReadOnly (默认值)只读 不能改变数据。 2 = adLockPessimistic 保守式锁定(逐个) 在编辑时立即锁定数据源的记录。 3 = adLockOptimistic 开放式锁定(逐个) 只在调用 Update 方法时才锁定记录。 4 = adLockBatchOptimistic 开放式批更新 用于批更新模式(与立即更新模式相对)。 2)读写数据 - 增加一行记录并对新记录的内容进行修改并保存可以如下写法 rs.AddNew 增加一行记录 rs(.)=. 数据读写操作 . rs.UpDate 保存写入资料,如果使用只读权限,则不能使用这个方法rs.Close 这个方法用来关闭你所代开的表,如果不使用这个方法也可以,但是数据库仍然认为你在对标进行锁定,可造成数据库负担过重 2、对数据进行筛选和排序 = Sql = select * from 表名 SQL查询语句 以上为打开一个表的所有内容 Sql = select top 50 * from 表名 SQL查询语句 以上为只打开前50行的记录 Sql = select top 50 列名1,列名2,列名5 from 表名 SQL查询语句 以上为只打开前50行的记录,并且只打开第1、2、5列数据 Sql = select * from 表名 where 列名1= & 字段 & SQL查询语句 以上为一个简单的筛选,表示打开的内容必须符合列名1= & 字段 & 的内容 需要注意的是,数据库列的类型必须匹配,比如字符串类型需要以单引号括起 而数字类型则不能用单引号括起 另外SQL语句还支持通配符,例如 列名1 like % & 字段 & % 表示包含字段 在表达式中的匹配 ? _(下划线) 任何单一字符 * or % 零个或多个字符 # 任何单一数字(0 9) charlist 任何在字符表中的单一字符 !charlist 任何不在字符表中的单一字符 注:根据数据库的不同?、_和*、%的应用有所差别,比如SQL只使用% 和 _ 分别代表多个字符和单一字符 Sql = select * from 表名 where 列名1= & 字段 & ORDER BY 字段1 ASC 对查询结果进行升序排列 Sql = select * from 表名 where 列名1= & 字段 & ORDER BY 字段1 DESC 对查询结果进行降序排列 Sql = select * from 表名 where 列名1= & 字段 & ORDER BY 字段1 DESC, ORDER BY 字段2 DESC 对查询结果进行多维降序排列(速度奇慢,不推荐使用) 3、Recordset集合的常用属性方法 = rs.BOF 是否游标在最上边 rs.EOF 是否游标在最下边 rs.RecordCount 记录集的行数(如果使用像前类型的游标,可能返回不准确) rs.AddNew 新建一行记录 rs.Update 保存当前行被修改的记录 rs.Delete 删除当前行 rs.Close 关闭记录集 4、Fields集合的一些属性的解释 = 以下为简写,正常情况需要rs.Fileds.。但是Fields属于Recordset的一个默认集合,因此可以省略 rs() 括号内可以是列名也可以是列的序号例如:rs(姓名)、rs(3) 都是是可以的 rs(3).Name 返回列名 rs(3).Type 返回列的类型 rs(3).Value 返回当前行的值 rs.Fields.Count 返回列数 Excel VBAExcel数据库连接 Public Conn As ADODB.Connection 声明ADODB.Connection对象变量Public Rdset As ADODB.RecordsetPublic sCode, sArea, sClerk, sRecorder, sStatus, sCust, sSatis As StringPublic dDate, dKD, dPD, dDD, dWC As DatePublic Flg, ConnFlg, ErrConn As BooleanFunction Open_Conn(SqlDatabaseName, SqlPassword, SqlUsername)Dim sConnStr As String 声明存放连接串的字符串变量Dim sSQL As StringDim TempC As String打开数据库连接Set Conn = New ADODB.ConnectionsConnStr = Provider=sqloledb;server=ewaysun;Uid=sa;Pwd=;Database=helpdeskConn.Open sConnStrTempC = Repeat_Check(sCode, Conn)If Conn Is Nothing ThenMsgBox 数据连接错误!ErrConn = TrueExit FunctionElseConnFlg = TrueEnd IfEnd Function插入数据库记录Function Open_Recorder(dDate, sCode, sArea, dKD, dPD, dDD, dWC, sClerk, sRecorder, sStatus, sCust, sSatis)添加记录Rdset.AddNewRdset!currdate = dDateRdset!Code = sCodeRdset!area = sAreaRdset!kdtime = dKDRdset!pdtime = dPDRdset!ddtime = dDDRdset!wctime = Date & & dWCRdset!clerk = sClerkRdset!Recorder = sRecorderRdset!Status = sStatusRdset!customer = sCustRdset!satis = sSatis Rdset.UpdateEnd Function判断重复插入Function Repeat_Check(sCode, Conn)Dim sCheck, TmpO As String 打开表Set Rdset = New ADODB.RecordsetRdset.CursorType = adOpenKeysetRdset.LockType = adLockOptimisticRdset.Open work, ConnRdset.Close sCheck = select * from work where code= & sCodeRdset.Open sCheck, ConnIf Rdset.EOF ThenTmpO = Open_Recorder(dDate, sCode, sArea, dKD, dPD, dDD, dWC, sClerk, sRecorder, sStatus, sCust, sSatis)ElseMsgBox 插入记录重复,请检查输入是否有误!, vbOKOnly + vbCritical, 提醒Flg = TrueExit FunctionEnd IfEnd Function-从数据库取数存放到excel表格-Function Open_Conn(SqlDatabaseName, SqlPassword, SqlUsername)Dim Conn As ADODB.Connection 声明ADODB.Connection对象变量Dim Rdset As ADODB.RecordsetDim TempC As StringDim sSQL As StringDim Rng As StringDim I As Integer sSQL = select Code,Area, KDtime, PDtime, DDtime, WCtime, Clerk, Status from work 打开数据库连接Set Conn = New ADODB.ConnectionsConnStr = Provider=sqloledb;server=ewaysun;Uid=sa;Pwd=;Database=helpdeskConn.Open sConnStrRng = a65535.End(xlUp).Row 判断有记录的最后一行If Rng 1 Then 判断清空的起始行Range(Cells(2, 1), Cells(Rng, 8).ClearContents 清空数据Cells(2, 1).CopyFromRecordset Conn.Execute(sSQL) 查询后插入单元格Columns(C:E).SelectSelection.NumberFormatLocal = yyyy-mm-dd hh:mmColumns(F:F).SelectSelection.NumberFormatLocal = $-F400hh:mm:ss AM/PMRng = a65535.End(xlUp).RowRange(Cells(2, 1), Cells(Rng, 8).Select按人名排序Selection.Sort Key1:=Range(G2), Order1:=xlAscending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin, DataOption1:=xlSortNormalRange(Cells(2, 1), Cells(Rng, 8).Select设置表格底纹Selection.Borders(xlDiagonalDown).LineStyle = xlNoneSelection.Borders(xlDiagonalUp).LineStyle = xlNoneWith Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous.Weight = xlThin.ColorIndex = xlAutomaticEnd WithWith Selection.Borders(xlEdgeTop).LineStyle = xlContinuous.Weight = xlThin.ColorIndex = xlAutomaticEnd WithWith Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous.Weight = xlThin.ColorIndex = xlAutomaticEnd WithWith Selection.Borders(xlEdgeRight).LineStyle = xlContinuous.Weight = xlThin.ColorIndex = xlAutomaticEnd WithWith Selection.Borders(xlInsideVertical).LineStyle = xlContinuous.Weight = xlThin.ColorIndex = xlAutomaticEnd WithWith Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous.Weight = xlThin.ColorIndex = xlAutomaticEnd With位置居中With Selection.HorizontalAlignment = xlCenter.VerticalAlignment = xlCenter.WrapText = False.Orientation = 0.AddIndent = False.IndentLevel = 0.ShrinkToFit = False.ReadingOrder = xlContext.MergeCells = FalseEnd WithElseCells(2, 1).CopyFromRecordset Conn.Execute(sSQL) 查询后插入单元格Rng = a65535.End(xlUp).Row 判断有记录的最后一行Range(Cells(2, 1), Cells(Rng, 8).Select按人名排序Selection.Sort Key1:=Range(G2), Order1:=xlAscending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _:=xlPinYin, DataOption1:=xlSortNormalColumns(C:E).SelectSelection.NumberFormatLocal = yyyy-mm-dd hh:mmColumns(F:F).SelectSelection.NumberFormatLocal = $-F400hh:mm:ss AM/PM End IfCells(2, 1).SelectIf Conn Is Nothing ThenMsgBox 数据连接错误!End IfEnd Function因为有许多Excel表格要把中文翻成英文,每个文件中要翻译的词汇基本上比较固定,因此写了这段代码,所需词汇的中英文对照先保存为一个词典文件,词典文件每行为一词语,中英文以Tab分隔;程序会将工作表中每个单元格中的中文内容截取出来,中英文夹杂的单元格内容会分割成多条中文词语处理,然后根据词典文件查到的英文解释,进行替换。代码如下 - By:忽又一天 /suddenday/Public Sub TranslateAll() + On Error Resume Next Dim i As Integer, j As Integer For i = 0 To 200 For j = 0 To 20 ReplaceCellTxt i, j Next Next End Sub Private Sub ReplaceCellTxt(ByVal RowIndex As Double, ByVal ColIndex As Double) 替换指定单元格中的中文字符 On Error Resume Next Dim i As Integer Dim PreText As String, CnText As Variant PreText = Cells(RowIndex, ColIndex) Debug.Print PreText If GetChn(PreText) Then CnText = Split(GetChn(PreText), ,) End If For i = 0 To UBound(CnText) CurText = TranslateWord(CnText(i) Debug.Print CurText If (Not CurText = NotFound) And (Not Trim(CurText) = ) Then PreText = Replace(PreText, CnText(i), CurText) Cells(RowIndex, ColIndex) = PreText End If Next End Sub Private Function TranslateWord(ByVal StrWord As String) 查找指定字符串在词典中的解释 On Error Resume Next Dim StrTemp As String Dim StrWords As Variant Open D:Dic.txt For Input As #1 TranslateWord = NotFound Do While Not EOF(1) Line Input #1, StrTemp Debug.Print StrTemp StrWords = Split(StrTemp, vbTab) If Trim(StrWords(0) = Trim(StrWord) And UBound(StrWords) 0 Then TranslateWord = StrWords(1) Exit Do End If Loop Close #1 End Function Private Function GetChn(ByVal Str As String) As String截取字符串中的中文 Dim i As Integer, j As Integer Dim ChnStr As String Dim IsPreCn As Boolean IsPreCn = False For i = 1 To Len(Str) If Asc(Mid(Str, i, 1) 1 ThenMsgBox 有数据返回End IfEnd IfConn.Close 关闭连接Set Conn = Nothing 释放连接三种SQL直接连接方式Conn.Open Driver=SQL Server;SERVER=erptest;Database=db_demo;uid=sa;pwd=Conn.Open Provider=sqloledb;SERVER=erptest;Database=db_demo;uid=sa;pwd=Conn.Open driver=SQL Server;SERVER=erptest;Database=db_demo;uid=sa;pwd=用Excel+VBA+SQL Server进行数据处理subtest()定义过程名称DimiAsInteger,jAsInteger,shtAsWorksheeti,j为整数变量;sht为excel工作表对象变量,指向某一工作表DimcnAsNewADODB.Connection定义数据链接对象,保存连接数据库信息;请先添加ADO引用DimrsAsNewADODB.Recordset定义记录集对象,保存数据表DimstrCnAsString,strSQLasString字符串变量strCn=Provider=sqloledb;Server=服务器名称或IP地址;Database=数据库名称;Uid=用户登录名;Pwd=密码;定义数据库链接字符串下面的语句将读取数据表数据,并将它保存到excel工作表中:画两张表想像一下,工作表为一张两维表,记录集也是一张两维表strSQL=select字段1,字段2from表名称定义SQL查询命令字符串cn.OpenstrCn与数据库建立连接,如果成功,返回连接对象cnrs.OpenstrSQL,cn执行strSQL所含的SQL命令,结果保存在rs记录集对象中i=1Setsht=ThisWorkbook.Worksheets(sheet1)把sht指向当前工作簿的sheet1工作表DoWhileNotrs.EOF当数据指针未移到记录集末尾时,循环下列操作sht.Cells(i,1)=rs(字段1)把当前记录的字段1的值保存到sheet1工作表的第i行第1列sht.Cells(i,2)=rs(字段2)把当前字段2的值保存到sheet1工作表的第i行第2列rs.MoveNext把指针移向下一条记录i=i+1i加1,准备把下一记录相关字段的值保存到工作表的下一行Loop循环rs.Close关闭记录集,至此,程序将把某数据表的字段1和字段2保存在excel工作表sheet1的第1、2列,行数等于数据表的记录数下面的语句将读取excel工作表数据,并将之简单计算后存入数据库,这里使用上面程序中的一些变量假设分别读取工作表sheet1第5行至第500行的第8列和第9列已存在的数据,然后将它们相乘,并将积存入数据库的某个表strSQL=清空上面定义的变量fori=5to500循环开始,i从5到500strSQL=strSQL&insertinto表名(字段)values(&sht.cells(i,8)*sht.cells(i,9)&);构造SQL命令串next至此生成一串SQL命令串,保存的内容大概为:insertinto表名(字段)values(数值1);insertinto表名(字段)values(数值2); cn.executestrSQL执行该SQL命令串,如果SQL命令没有错误,将在数据库中添加501个记录;也可以用rs.openstrSQL,cn执行cn.close关闭数据库链接,释放资源endsubmssql和accessexcel之间数据互导2008-06-03 17:13常规的数据导入导出:使用DTS向导迁移你的Access数据到SQL Server,你可以使用这些步骤: 1)在SQL SERVER企业管理器中的Tools(工具)菜单上,选择Data Transformation 2)Services(数据转换服务),然后选择 czdImport Data(导入数据)。 3)在Choose a Data Source(选择数据源)对话框中选择Microsoft Access as the Source,然后键入你的.mdb数据库(.mdb文件扩展名)的文件名或通过浏览寻找该文件。 4)在Choose a Destination(选择目标)对话框中,选择Microsoft OLEDB Prov ider for SQLServer,选择数据库服务器,然后单击必要的验证方式。 5)在Specify Table Copy(指定表格复制)或Query(查询)对话框中,单击Copy tables(复制表格)。 6)在Select Source Tables(选择源表格)对话框中,单击Select All(全部选定)。下一步,完成。Transact-SQL语句进行导入导出:1.在SQL SERVER里查询access数据:Select * FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0,Data Source=c:DB.mdb;User ID=Admin;Password=).表名2.将access导入SQL server在SQL SERVER 里运行:Select * INTO newtable FROM OPENDATASOURCE (Microsoft.Jet.OLEDB.4.0,Data Source=c:DB.mdb;User ID=Admin;Password= ).表名3.将SQL SERVER表里的数据插入到Access表中在SQL SERVER 里运行:inse

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论