需求:
要完成同一件事情,通常有多种方式可供选择。如果局限于一种方法,不去探索其他方法,往往会错失一些机会。想想看,如果你能用一半的时间完成你手头的工作,节省下来的时间可以做很多有意义的事情的。通过自动化,让人们从一些简单重复的劳动中解放出来,投入到未知领域的探索中去,这是很有意义的一件事情。
下面这段看上去可能不起眼的代码,实际上大有作为!
代码:
Sub generateScript()
'本程序利用Excel中的相关字段,自动生成向ORACLE数据库中插入新记录的SQL脚本(DML语句)
'这样,就可以用增量更新的方式维护ManuallyCollated数据表
'目前,这部分工作是通过全量导入的方式进行的(需要先drop再创建,然后使用Toad从Excel中导入文件的方式,手动操作过程繁杂,耗时半小时以上)
'增量方式可以节省至少20分钟的工作量
'开发者:Hpd,开发日期:2018年6月13日
'Update Manually Collated data table in an incremental update way in several seconds,
'Rather than using "drop and import" way, which takes 20 mins
Dim Rows As Integer '工作表“ManuallyCollated_new_”中的行数
Dim scriptRows As Integer '工作表“Script”中的行数,用于清空现有数据
Dim newSht As Worksheet '工作表“ManuallyCollated_new”
Dim ScriptSht As Worksheet '工作表“Script”
Dim maxDate As Date '最大日期,用于筛选最新的FSR记录
Const XLMAXROW As Long = 1048576 'Excel 2010中单张数据表的最大行数为1048576行
Set newSht = ThisWorkbook.Worksheets("ManuallyCollated_new")
Set ScriptSht = ThisWorkbook.Worksheets("Script")
Rows = newSht.UsedRange.rows.Count
scriptRows = ScriptSht.UsedRange.rows.Count
ScriptSht.Range("A3:H" & scriptRows).ClearContents '复制新内容前先清空现有内容
maxDate = Application.WorksheetFunction.Max(newSht.Columns("G")) 'LUD列的列名为"G"
newSht.Range("A1:G" & Rows).AutoFilter Field:=7, Criteria1:=">=" & maxDate - 1 '如果直接用等于maxDate作为条件,筛选出的结果为空
newSht.AutoFilter.Range.Offset(2).SpecialCells(xlCellTypeVisible).Copy ScriptSht.Range("A3")
scriptRows = ScriptSht.Range("A" & XLMAXROW).End(xlUp).Row '如果使用UsedRange.rows.Count的方式,发现会多出两行无效数据
'自动生成SQL脚本,用于向数据库中存放ManuallyCollated数据的表中插入新记录
For i = 3 To scriptRows
'公式较长,使用续行符(_)进行换行
ScriptSht.Range("H" & i).Formula = _
"INSERT INTO USCHEMA.UTABLE (SID, CTY,SID, RD, SRC ) VALUES ('" & _
ScriptSht.Range("A" & i) & "', '" & ScriptSht.Range("B" & i) & "', " & ScriptSht.Range("C" & i) & ", " & _
"TO_DATE('" & WorksheetFunction.Text(ScriptSht.Range("D" & i), "YYYY-MM-DD") & "', 'YYYY-MM-DD'), '" & _
ScriptSht.Range("E" & i) & "');"
Next
ScriptSht.Activate '显示Script工作表
ScriptSht.Range("H3:H" & scriptRows).Copy '将自动生成的SQL脚本复制到粘贴板
End Sub