Excel VBA 读书上彀笔记.doc

2007-12-3 1，ADO-SQL 1，数据表 表头在第一行； 2，Where子句中，各条件要用括号( )分开； 字符串变量用法：客户=’” aa “’ ； 数值用变量 ：编码=” bb “ 日期变量：开票日期 between #” ks “# And #” js “# 日期 ：#2007-1-1 # ; 7，From 子句中，表格名用变量 ：From [“ MysheetName “$] ; _ 2008-3-14 Select FirstName + ‘ ‘ +LastName,Date,Hours From … 将多个列合并为一个结果列 当只须执行单个查询且无须ADO Connection对象的高级属性时，可不创建ADO 的Connection对象。 摘自“Excel专业开发” P.352 2011-5-27 T是时间的字符串格式，用了CDtae函数成时间格式 打开有密码的Access文件： stpath = ThisWorkbook.Path Application.PathSeparator DataBase.md CNN.Open Provider=Microsoft.Jet.Oledb.4.0;data Source= stpath ;Jet OLEDB:Database Password= woaivba 2，‘狼版主的代码 Function shijian(ByVal r As Range) As StringDim s, t, i, vs = Split(r.Value)For i = 0 To UBound(s)t = Split(s(i), -)v = v + 24 * 60 * (CDate(t(1)) - CDate(t(0))) ‘转换成分钟数Nextshijian = Format(TimeSerial(0, v, 0), h小时m分钟)‘Timevalue函数转换成时间格式End Function 已知年月，求当月天数 rq = CDate(aa) dd = Day(DateSerial(Year(rq), Month(rq) + 1, 0)) 2011-5-28 ‘/thread-724176-1-1.html### 3，‘数组的一部分赋值给另一个数组 应用一念老师的代码 Sub GetSum()? ? Application.ScreenUpdating = False? ? Dim Arr, Ary, k%, x$? ? Dim Dic As Object, Itm? ? Sheet2.Rows(2:65536).Delete? ? Arr = Sheets(1).[A1].CurrentRegion? ? Set Dic = CreateObject(Scripting.Dictionary)? ? For k = 2 To UBound(Arr)? ?? ???x = Arr(k, 1) | Arr(k, 2)? ?? ???If Not Dic.exists(x) Then? ?? ?? ?? ?Ary = Application.Index(Arr, k) ‘数组的一部分赋值给另一个数组? ?? ???Else? ?? ?? ?? ?Ary = Dic(x)? ?? ?? ?? ?Ary(3) = Ary(3) , Arr(k, 3)? ?? ?? ?? ?Ary(6) = Ary(6) + Arr(k, 6)? ?? ???End If? ?? ???Dic(x) = Ary ‘把数组赋给字典关键字的项? ? Next? ? ? ? For Each Itm In Dic? ?? ???[A65536].End(3)(2).Resize(1, 7) = Dic(Itm) ‘把字典项的值赋给单元格区域? ? Next? ? Set Dic = Nothing? ? Application.ScreenUpdating = TrueEnd Sub ‘2012-2-8 /thread-823801-1-1.html 2011-6-11 4，Union的用法 If rng2 Is Nothing Then Set rng2 = Cells(i, i) Else Set rng2 = Union(rng2, Cells(i, i)) If Not rng2 Is Nothing Then rng2.EntireRow.Delete 2011-6-13 5，转xls文件为.csv文件 ‘/t