è¿ä¸ªéè¦ç¨vbaç¼ç¨æ¥å®æã
å
·ä½ä»£ç éè¦æ ¹æ®å®é
æ
åµæ¥å®å¶ã
ä¸é¢ç»åºä¸æ®µç±»ä¼¼ç代ç ï¼æ ¹æ®excelè¡¨æ ¼èªå¨æ¹éçææ¿ç§ååï¼ï¼ä»
ä¾åèï¼
Sub SCWJ()
Dim arr(1 To 1000, 1 To 19) '1-ç¼å· 2-ç±»å 3-åºå 4-åå
5-æ¥¼å± 6-é¢ç§¯ 7-ç©ºæ¿ 8-èµ·ç§æ¥æ 9-å°ææ¥æ 10-å¨æ 11-ç»è´¹æ¥æ 12-ç»è´¹ç±»å« 13-ç©ºç½ 14-ç©ºç½ 15-ç§é
'16-æ¼é 17-å§å 18-çµè¯ 19-sfzhm
hh = 3
jls = 0
With Sheets("çµåä¿¡æ¯ç»è®°æ¡£æ¡")
Do While .Cells(hh, 1) <> ""
jls = jls + 1
For j = 1 To 19
arr(jls, j) = .Cells(hh, j)
Next j
hh = hh + 1
Loop
End With
Dim bh As String 'ç¼å·
Dim lx As String 'ç±»å
Dim qy As String 'åºå
Dim dy As String 'åå
Dim lc As String '楼å±
Dim mj As String 'é¢ç§¯
Dim qzrq As Date 'èµ·ç§æ¥æ
Dim dqrq As Date 'å°ææ¥æ
Dim zq As String 'å¨æ
Dim xflb As String 'ç»è´¹ç±»å«
Dim zj As Long 'ç§é
Dim yj As Long 'æ¼é
Dim xm As String
Dim dh As String
Dim sfzhm As String
For i = 1 To jls
bh = arr(i, 1)
lx = arr(i, 2)
qy = arr(i, 3)
dy = arr(i, 4)
lc = arr(i, 5)
mj = arr(i, 6)
qzrq = arr(i, 8)
dqrq = arr(i, 9)
zq = arr(i, 10)
xflb = arr(i, 12)
zj = Val(arr(i, 15))
yj = arr(i, 16)
xm = arr(i, 17)
dh = arr(i, 18)
sfzhm = arr(i, 19)
Select Case lx
Case "å¸åº", "ä¼æ "
Call æ¿æ¢ææ¬_å¸åº(bh, lx, qy, dy, lc, mj, qzrq, dqrq, zq, xflb, zj, yj, xm, dh, sfzhm)
Case "5å¹´"
Call æ¿æ¢ææ¬_5å¹´(bh, lx, qy, dy, lc, mj, qzrq, dqrq, zq, xflb, zj, yj, xm, dh, sfzhm)
End Select
Next i
End Sub
Private Sub æ¿æ¢ææ¬_å¸åº(bh, lx, qy, dy, lc, mj, qzrq, dqrq, zq, xflb, zj, yj, xm, dh, sfzhm)
Dim å½åè·¯å¾, 导åºæ件å, 导åºè·¯å¾æ件å As String
Dim Str1, Str2
Dim tarr(1 To 27, 1 To 2)
å½åè·¯å¾ = ThisWorkbook.Path
导åºæ件å = bh & ".doc"
导åºæ件å2 = bh & ".xlsx"
FileCopy å½åè·¯å¾ & "\模æ¿\æ¿å±ç§èµåå.doc", å½åè·¯å¾ & "\å¾
æå°WORDææ¡£\" & 导åºæ件å
FileCopy å½åè·¯å¾ & "\模æ¿\æ¿ç§ç³è¯·.xlsx", å½åè·¯å¾ & "\å¾
æå°WORDææ¡£\" & 导åºæ件å2
With Sheets("å
³é®å")
For i = 1 To 27
tarr(i, 1) = .Cells(i + 1, 1)
Next i
End With
tarr(1, 2) = xm
tarr(2, 2) = qy
tarr(3, 2) = dy
tarr(4, 2) = lc
tarr(5, 2) = dh
tarr(6, 2) = sfzhm
tarr(7, 2) = mj
tarr(8, 2) = yj
tarr(9, 2) = Num2Money(yj * 12) '大åæ»ç§é
tarr(10, 2) = yj * 12 'å°åæ»ç§é
Select Case xflb
Case "å¹´"
tarr(11, 2) = 1
tarr(12, 2) = 12
tarr(13, 2) = Num2Money(zj)
tarr(15, 2) = "" 'ç»è´¹æ¥æ2
tarr(16, 2) = "" 'ç»è´¹æ¥æ3
tarr(17, 2) = "" 'ç»è´¹æ¥æ4
tarr(18, 2) = 12 '第ä¸æ¬¡å 个æç§é
tarr(19, 2) = "/"
tarr(20, 2) = "/"
tarr(21, 2) = "/"
tarr(22, 2) = Num2Money(zj) '大å第ä¸æ¬¡ç§é
tarr(23, 2) = "/" '大å第äºæ¬¡ç§é
tarr(24, 2) = "/" '大å第ä¸æ¬¡ç§é
tarr(25, 2) = "/" '大å第å次ç§é
Case "åå¹´"
tarr(11, 2) = 2
tarr(12, 2) = 6
tarr(13, 2) = Num2Money(zj)
tarr(15, 2) = Int(qzrq + 183)
tarr(16, 2) = "" 'ç»è´¹æ¥æ3
tarr(17, 2) = "" 'ç»è´¹æ¥æ4
tarr(18, 2) = 6 '第ä¸æ¬¡å 个æç§é
tarr(19, 2) = 6
tarr(20, 2) = "/"
tarr(21, 2) = "/"
tarr(22, 2) = Num2Money(zj) '大å第ä¸æ¬¡ç§é
tarr(23, 2) = Num2Money(zj) '大å第äºæ¬¡ç§é
tarr(24, 2) = "/" '大å第ä¸æ¬¡ç§é
tarr(25, 2) = "/" '大å第å次ç§é
Case "å£åº¦"
tarr(11, 2) = 4
tarr(12, 2) = 3
tarr(13, 2) = Num2Money(zj)
tarr(15, 2) = qzrq + Int(365 / 4 + 0.5)
tarr(16, 2) = qzrq + Int(365 / 4 * 2 + 0.5) 'ç»è´¹æ¥æ3
tarr(17, 2) = qzrq + Int(365 / 4 * 3 + 0.5) 'ç»è´¹æ¥æ4
tarr(18, 2) = 3 '第ä¸æ¬¡å 个æç§é
tarr(19, 2) = 3
tarr(20, 2) = 3
tarr(21, 2) = 3
tarr(22, 2) = Num2Money(zj) '大å第ä¸æ¬¡ç§é
tarr(23, 2) = Num2Money(zj) '大å第äºæ¬¡ç§é
tarr(24, 2) = Num2Money(zj) '大å第ä¸æ¬¡ç§é
tarr(25, 2) = Num2Money(zj) '大å第å次ç§é
End Select
tarr(14, 2) = qzrq
tarr(27, 2) = dqrq
If mj = 55 Then
tarr(26, 2) = 3
Else
If mj = 75 Then
tarr(26, 2) = 5
Else
tarr(26, 2) = 6
End If
End If
导åºè·¯å¾æ件å = å½åè·¯å¾ & "\å¾
æå°WORDææ¡£\" & 导åºæ件å
导åºè·¯å¾æ件å2 = å½åè·¯å¾ & "\å¾
æå°WORDææ¡£\" & 导åºæ件å2
Set wdoc = CreateObject("word.application")
wdoc.Visible = False
With wdoc
.Documents.Open 导åºè·¯å¾æ件å
.Visible = True
For i = 1 To 27 'å¡«åæåæ°æ®
Str1 = tarr(i, 1)
Str2 = tarr(i, 2)
bj = True
Do While bj
.Selection.HomeKey Unit:=wdStory 'å
æ ç½®äºæ件é¦
If .Selection.Find.Execute(Str1) Then 'æ¥æ¾å°æå®å符串
'.Selection.Font.Color = wdColorAutomatic 'å符为èªå¨é¢è²
.Selection.Text = Str2 'æ¿æ¢å符串
Else
bj = False
End If
Loop
Next i
End With
wdoc.Documents.Save
wdoc.Quit
Set wdoc = Nothing
'åå
¥æ¿ç§ç³è¯·
'æ¿å· !åºå!åå
!楼å±
'æ·å !é¢ç§¯
'æ¿ç§è¯´æä»æ¬¾æ¹å¼: !å¨æ!ç»è´¹æ¥æ!ç»è´¹
Set myb = Workbooks.Open(导åºè·¯å¾æ件å2)
Dim xfrq As Date 'ç»è´¹æ¥æ
Select Case xflb
Case "å¹´"
xfrq = dqrq
Case "åå¹´"
xfrq = qzrq + 182
Case "å£åº¦"
xfrq = qzrq + 91
End Select
With myb.Sheets("sheet1")
.Cells(3, 2) = xm
.Cells(4, 2) = dh
.Cells(5, 2) = sfzhm
.Cells(6, 2) = qzrq
.Cells(7, 2) = dqrq
.Cells(3, 6) = qy & Space(1) & dy & Space(1) & lc
.Cells(4, 6) = mj
.Cells(5, 6) = yj * 12
.Cells(6, 6) = yj
.Cells(7, 6) = ""
.Cells(9, 1) = zq & " " & xfrq & " " & xflb
End With
myb.Save
myb.Close
End Sub
Private Sub æ¿æ¢ææ¬_5å¹´(bh, lx, qy, dy, lc, mj, qzrq, dqrq, zq, xflb, zj, yj, xm, dh, sfzhm)
Dim å½åè·¯å¾, 导åºæ件å, 导åºè·¯å¾æ件å As String
Dim Str1, Str2
Dim tarr(1 To 20, 1 To 2)
å½åè·¯å¾ = ThisWorkbook.Path
导åºæ件å = bh & ".doc"
导åºæ件å2 = bh & ".xlsx"
FileCopy å½åè·¯å¾ & "\模æ¿\èªè£
åå模æ¿.doc", å½åè·¯å¾ & "\å¾
æå°WORDææ¡£\" & 导åºæ件å
FileCopy å½åè·¯å¾ & "\模æ¿\æ¿ç§ç³è¯·.xlsx", å½åè·¯å¾ & "\å¾
æå°WORDææ¡£\" & 导åºæ件å2
With Sheets("å
³é®å")
For i = 1 To 20
tarr(i, 1) = .Cells(i + 1, 3)
Next i
End With
tarr(1, 2) = xm
tarr(2, 2) = qy
tarr(3, 2) = dy
tarr(4, 2) = lc
tarr(5, 2) = sfzhm
tarr(6, 2) = mj
tarr(7, 2) = qzrq
tarr(8, 2) = dqrq
mzrq = Int(qzrq + 18 * 30.42708333) 'å
ç§æ¥æ
tarr(9, 2) = mzrq
tarr(10, 2) = zj 'å°åæ»ç§é
tarr(11, 2) = qzrq
tarr(12, 2) = DateSerial(Year(mzrq) + 1, Month(mzrq), Day(mzrq))
tarr(13, 2) = DateSerial(Year(mzrq) + 2, Month(mzrq), Day(mzrq))
tarr(14, 2) = DateSerial(Year(mzrq) + 3, Month(mzrq), Day(mzrq))
tarr(15, 2) = yj
If mj = 55 Then
tarr(16, 2) = 3
Else
If mj = 75 Then
tarr(16, 2) = 5
Else
tarr(16, 2) = 6
End If
End If
tarr(17, 2) = dh
tarr(18, 2) = Num2Money(zj)
tarr(19, 2) = mzrq
tarr(20, 2) = Num2Money(zj / 2)
导åºè·¯å¾æ件å = å½åè·¯å¾ & "\å¾
æå°WORDææ¡£\" & 导åºæ件å
导åºè·¯å¾æ件å2 = å½åè·¯å¾ & "\å¾
æå°WORDææ¡£\" & 导åºæ件å2
Set wdoc = CreateObject("word.application")
wdoc.Visible = False
With wdoc
.Documents.Open 导åºè·¯å¾æ件å
.Visible = True
For i = 1 To 20 'å¡«åæåæ°æ®
Str1 = tarr(i, 1)
Str2 = tarr(i, 2)
bj = True
Do While bj
.Selection.HomeKey Unit:=wdStory 'å
æ ç½®äºæ件é¦
If .Selection.Find.Execute(Str1) Then 'æ¥æ¾å°æå®å符串
'.Selection.Font.Color = wdColorAutomatic 'å符为èªå¨é¢è²
.Selection.Text = Str2 'æ¿æ¢å符串
Else
bj = False
End If
Loop
Next i
End With
wdoc.Documents.Save
wdoc.Quit
Set wdoc = Nothing
Set myb = Workbooks.Open(导åºè·¯å¾æ件å2)
Dim xfrq As Date 'ç»è´¹æ¥æ
Select Case xflb
Case "å¹´"
xfrq = dqrq
Case "åå¹´"
xfrq = qzrq + 182
Case "å£åº¦"
xfrq = qzrq + 91
End Select
With myb.Sheets("sheet1")
.Cells(3, 2) = xm
.Cells(4, 2) = dh
.Cells(5, 2) = sfzhm
.Cells(6, 2) = qzrq
.Cells(7, 2) = dqrq
.Cells(3, 6) = qy & Space(1) & dy & Space(1) & lc
.Cells(4, 6) = mj
.Cells(5, 6) = yj * 12
.Cells(6, 6) = yj
.Cells(7, 6) = ""
.Cells(9, 1) = zq & " " & xfrq & " " & xflb
End With
myb.Save
myb.Close
End Sub
温馨提示:答案为网友推荐,仅供参考