The specific code needs to be customized according to the actual situation.
The following is a similar code (automatically generating lease contracts in batches according to excel tables), for reference only!
Child SCWJ ()
Dimarr( 1 to 1000, 1 to19)'1-No.2-Category 3-Zone 4-Unit 5-Floor 6-Zone 7-Vacant 8- Lease Term 9- Expiration Date/kloc
16- deposit 17- name 18- phone 19-sfzhm
hh = 3
jls = 0
With table ("electronic information registration document")
When doing it. Cell (hh,1) < & gt""
jls = jls + 1
For j = 1 to 19.
arr(jls，j) =。 Cell (hh, j)
Next J.
hh = hh + 1
ring
End with ...
Mark bh as a string number.
Mark lx as a string type
Mark qy as a string area
Size mold as a string unit
Tune lc to the string "floor"
Mark mj as a string area
Dim qzrq As Date' start date
Dim dqrq As date' expiration date'
Dim zq As String' period
Dim xflb As String' renewal category
Dim zj As long-term lease
Dim yj As Long' deposit
Mark xm as a string
Mark dh as a string
Dim sfzhm as a 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
Cases "Market" and "Discount"
Call replaces text _ market (Bosnia and Herzegovina, LX, QY, Dy, LC, MJ, QZRQ, ZQ, XFLB, ZJ, YJ, XM, DH, SFZHM).
Case "5 years"
Address replaces text _5 years (Bosnia and Herzegovina, LX, QY, Dy, LC, MJ, QZRQ, ZQ, XFLB, ZJ, YJ, XM, DH, SFZHM).
End selection
Next, I
End joint
Private Sub replaces text _ market (Bosnia and Herzegovina, LX, QY, Dy, LC, MJ, QZRQ, ZQ, XFLB, ZJ, YJ, XM, DH, SFZHM).
Dim the current path, export the file name, and export the path file name as a string.
Size Str 1, Str2
Dim tarr( 1 to 27, 1 to 2)
Current path = ThisWorkbook. path
Export file name = BH &；; ".doc "
Export file name 2 = BH &；; ".xlsx "
File copy current path &; "\ template \ house lease contract. Doc ",current path &; "\ WORD document to be printed \"&; Export file name
File copy current path &; "\ template \ lease application. Xlsx ",current path &; "\ WORD document to be printed \"&; Export file name 2
Using worksheets ("keywords")
For i = 1 to 27
tarr(i， 1) =。 Cell (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 9,2) = num2money (yj *12)' Total rent in words.
Tarr (10,2) = yj *12' total rent figure.
Select case xflb
Case "year"
tarr( 1 1，2) = 1
tarr( 12，2) = 12
tarr( 13，2) = Num2Money(zj)
Tarr (15,2) = ""Renewal Date 2
Tarr (16,2) = ""Renewal Date 3
Tarr (17,2) = ""Renewal Date 4
Tarr (18,2) =12' Rent for the first few months.
tarr( 19，2) = "/"
tarr(20，2) = "/"
tarr(2 1，2) = "/"
Tarr (22,2 2) = Num2Money(zj)' capital first rent.
Tarr (23,2) = the second rent in the word "/".
Tarr (24,2) = "/"third rent.
Tarr (25,2) = "/"the fourth rent.
Case "half a year"
tarr( 1 1，2) = 2
tarr( 12，2) = 6
tarr( 13，2) = Num2Money(zj)
tarr( 15，2) = Int(qzrq + 183)
Tarr (16,2) = ""Renewal Date 3
Tarr (17,2) = ""Renewal Date 4
Tarr (18,2) = 6' Rent for the first few months.
tarr( 19，2) = 6
tarr(20，2) = "/"
tarr(2 1，2) = "/"
Tarr (22,2 2) = Num2Money(zj)' capital first rent.
Tarr (23,2 2) = Num2Money(zj)' second rent in words.
Tarr (24,2) = "/"third rent.
Tarr (25,2) = "/"the fourth rent.
Case "Quarter"
tarr( 1 1，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)' renewal date 3.
Tarr (17,2) = qzrq+int (365/4 * 3+0.5)' renewal date 4
Tarr (18,2) = 3' Rent for the first few months.
tarr( 19，2) = 3
tarr(20，2) = 3
tarr(2 1，2) = 3
Tarr (22,2 2) = Num2Money(zj)' capital first rent.
Tarr (23,2 2) = Num2Money(zj)' second rent in words.
Tarr (24,2 2) = Num2Money(zj)' The third rent.
Tarr (25,2 2) = Num2Money(zj)' fourth rent in words.
End selection
tarr( 14，2) = qzrq
tarr(27，2) = dqrq
If mj = 55, then
Tal (26,2) = 3
other
If mj = 75, then
tarr(26，2) = 5
other
Tal (26,2) = 6
If ... it will be over.
If ... it will be over.
Export path file & = current path & amp" \ WORD document to be printed \ "&; Export file name
Export path file & amp2 = current path &; "\ WORD document to be printed \"&; Export file name 2
set wdoc = CreateObject(" word . application ")
wdoc。 Visible = false
Use wdoc
Documents. Open the export path file name
. Visible = true
Fill in text data for i = 1 to 27'.
Str 1 = tarr(i， 1)
Str2 = tarr(i，2)
Bj = true
Do it while bj
. choose HomeKey Unit:=wdStory' cursor is placed at the beginning of the file.
If.selection.find.execute (str1) then' finds the specified string.
. Selection.font.color = wdcolormatic' character is automatic color.
Selection.text = str 2' replacement string
other
Bj = false
If ... it will be over.
ring
Next, I
End with ...
wdoc。 Documents. Save
wdoc。 give up
Set wdoc = none.
Write a lease application
Room number! Area! Unit! ground
Huxing! zone
Lease Description Payment Method:! Cycle! Renewal date! restart
Set myb = Workbooks. Open (export path file name 2)
Dim xfrq As Date' renewal date
Select case xflb
Case "year"
xfrq = dqrq
Case "half a year"
xfrq = qzrq + 182
Case "Quarter"
xfrq = qzrq + 9 1
End selection
With myb. Paper ("paper 1")
. Cell (3,2 2) = xm
. Cell (4,2 2) = dh
. Cell (5,2 2) = sfzhm
. Cell (6,2 2) = qzrq
. Cell (7,2 2) = dqrq
. Cell (3,6) = QY&; Space (1)dy & amp space (1) Landing Craft
. Cell (4,6 6) = mj
. Cell (5,6) = yj *12
. Cell (6,6 6) = yj
. Cell (7,6) = ""
. Cells(9， 1)= zq & amp； ”& ampxfrq & amp”& ampxflb
End with ...
myb。 rescue
myb。 close
End joint
Private submarines have replaced the text for five years (Bosnia and Herzegovina, LX, QY, Dy, LC, MJ, QZRQ, ZQ, XFLB, ZJ, YJ, XM, DH, SFZHM).
Dim the current path, export the file name, and export the path file name as a string.
Size Str 1, Str2
Dim tarr( 1 to 20, 1 to 2)
Current path = ThisWorkbook. path
Export file name = BH &；; ".doc "
Export file name 2 = BH &；; ".xlsx "
File copy current path &; "\ Template \ Self-installed contract template. Doc ",current path &; "\ WORD document to be printed \"&; Export file name
File copy current path &; "\ template \ lease application. Xlsx ",current path &; "\ WORD document to be printed \"&; Export file name 2
Using worksheets ("keywords")
For i = 1 to 20
tarr(i， 1) =。 Cell (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.4333)' rent-free.
tarr(9，2) = mzrq
Tarr (10,2 2) = zj' Total rent in figures.
tarr( 1 1，2) = qzrq
Tarr (12,2 2) = DateSerial (year (mzrq)+1, month (mzrq), day (mzrq))
Tarr (13,2 2) = DateSerial (year (mzrq)+2, month (mzrq), day (mzrq))
Tarr (14,2 2) = DateSerial (year (mzrq)+3, month (mzrq), day (mzrq))
tarr( 15，2) = yj
If mj = 55, then
tarr( 16，2) = 3
other
If mj = 75, then
tarr( 16，2) = 5
other
tarr( 16，2) = 6
If ... it will be over.
If ... it will be over.
tarr( 17，2) = dh
tarr( 18，2) = Num2Money(zj)
tarr( 19，2) = mzrq
tarr(20，2) = Num2Money(zj / 2)
Export path file & = current path & amp" \ WORD document to be printed \ "&; Export file name
Export path file & amp2 = current path &; "\ WORD document to be printed \"&; Export file name 2
set wdoc = CreateObject(" word . application ")
wdoc。 Visible = false
Use wdoc
Documents. Open the export path file name
. Visible = true
Fill in text data for i = 1 to 20'.
Str 1 = tarr(i， 1)
Str2 = tarr(i，2)
Bj = true
Do it while bj
. choose HomeKey Unit:=wdStory' cursor is placed at the beginning of the file.
If.selection.find.execute (str1) then' finds the specified string.
. Selection.font.color = wdcolormatic' character is automatic color.
Selection.text = str 2' replacement string
other
Bj = false
If ... it will be over.
ring
Next, I
End with ...
wdoc。 Documents. Save
wdoc。 give up
Set wdoc = none.
Set myb = Workbooks. Open (export path file name 2)
Dim xfrq As Date' renewal date
Select case xflb
Case "year"
xfrq = dqrq
Case "half a year"
xfrq = qzrq + 182
Case "Quarter"
xfrq = qzrq + 9 1
End selection
With myb. Paper ("paper 1")
. Cell (3,2 2) = xm
. Cell (4,2 2) = dh
. Cell (5,2 2) = sfzhm
. Cell (6,2 2) = qzrq
. Cell (7,2 2) = dqrq
. Cell (3,6) = QY&; Space (1)dy & amp space (1) Landing Craft
. Cell (4,6 6) = mj
. Cell (5,6) = yj *12
. Cell (6,6 6) = yj
. Cell (7,6) = ""
. Cells(9， 1)= zq & amp； ”& ampxfrq & amp”& ampxflb
End with ...
myb。 rescue
myb。 close
End joint