Application of VB in bidding agency business
In the business of government procurement and bidding agency services, many documents need to use the model and reference format documents formulated by relevant departments. For example, government procurement projects must edit and publish announcements with reference to the specification for the format of government procurement announcement and publicity information (2020 Edition).
1, Find and ask questions
Is there any way to avoid editing and making files in the same format and improve work efficiency?
2, Thinking problem
Each project has a corresponding standard format in the procurement process (including single source, inquiry, competitive negotiation, competitive negotiation and public bidding). The only difference is the specific information of the project, including project name, project department number, procurement unit, budget amount, announcement date, bid opening time, etc. Idea: write a program to find and replace the specific information of the project through the program, turn it into the required project information, and save the replaced file.
For example: project name“ re_xmmc"Indicates that the item number is“ re_xmbh"Indicates that the agent uses“ re_dljg"Means wait.
3, Formulate plans and Countermeasures
1)Choose a programming language you are familiar with, Visual Basic 6.0，abbreviation VB.
2)Define keywords. Set the specific information of the items to be searched and replaced as keywords and add a fixed prefix re_As identification.
3)Editing and production word,excel Template file.
4)Interface design, code development.
5)Program function test and code improvement.
4, Program trial and inspection effect
5, Feedback, perfect function.
Bidding agency-1
Bidding agency-2
Find and replace function codes:
Sub chazhaoth() 'Find replacement calling module
If ysje <> "" Then
ysw = Round(ysje / 10000, 2)
End If
wrdApp.Selection.Find.Replacement.ClearFormatting 'Item No
With wrdApp.Selection.Find
.Text = "re_xmbh"
.Replacement.Text = xmbh
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchByte = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'entry name
With wrdApp.Selection.Find
.Text = "re_xmmc"
.Replacement.Text = xmmc
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Purchasing unit
With wrdApp.Selection.Find
.Text = "re_cgdw"
.Replacement.Text = cgdw
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Agency
With wrdApp.Selection.Find
.Text = "re_dljg"
.Replacement.Text = dljg
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Agency address
With wrdApp.Selection.Find
.Text = "re_dldz"
.Replacement.Text = dldz
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Procurement method
With wrdApp.Selection.Find
.Text = "re_cgfs"
.Replacement.Text = cgfs
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Abbreviation of purchase method
With wrdApp.Selection.Find
.Text = "re_fsjc"
.Replacement.Text = fsjc
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Project category
With wrdApp.Selection.Find
.Text = "re_xiangmulb"
.Replacement.Text = xiangmulb
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
'-------------------------------------------------------------------
wrdApp.Selection.Find.Replacement.ClearFormatting
With wrdApp.Selection.Find
.Text = "re_riqi_f1"
.Replacement.Text = riqi_f1
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting
With wrdApp.Selection.Find
.Text = "re_riqi_f2"
.Replacement.Text = riqi_f2
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting
With wrdApp.Selection.Find
.Text = "re_riqi_f3"
.Replacement.Text = riqi_f3
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting
With wrdApp.Selection.Find
.Text = "re_shi"
.Replacement.Text = shi
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting
With wrdApp.Selection.Find
.Text = "re_fen"
.Replacement.Text = fen
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
'---------------------------------------------------------------------
wrdApp.Selection.Find.Replacement.ClearFormatting 'Budget amount
With wrdApp.Selection.Find
.Text = "re_ysje"
.Replacement.Text = ysje
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Budget amount 10000
With wrdApp.Selection.Find
.Text = "re_ysw"
.Replacement.Text = ysw
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'bond
With wrdApp.Selection.Find
.Text = "re_bzj"
.Replacement.Text = bzj
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Project Leader
With wrdApp.Selection.Find
.Text = "re_xmfzr"
.Replacement.Text = xmfzr
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Purchaser contact
With wrdApp.Selection.Find
.Text = "re_cgrlxr"
.Replacement.Text = cgrlxr
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Contact number of purchaser
With wrdApp.Selection.Find
.Text = "re_cgrlxdh"
.Replacement.Text = cgrlxdh
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Purchaser's address
With wrdApp.Selection.Find
.Text = "re_cgrdz"
.Replacement.Text = cgrdz
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'contact number
With wrdApp.Selection.Find
.Text = "re_dianhua"
.Replacement.Text = dianhua
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Supervision department
With wrdApp.Selection.Find
.Text = "re_jiandubm"
.Replacement.Text = jiandubm
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Service& Support Hotline
With wrdApp.Selection.Find
.Text = "re_jiandudh"
.Replacement.Text = jiandudh
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'administrative division
With wrdApp.Selection.Find
.Text = "re_xzqy"
.Replacement.Text = xzqy
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Transaction amount
With wrdApp.Selection.Find
.Text = "re_cjje"
.Replacement.Text = cjje
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Transaction person
With wrdApp.Selection.Find
.Text = "re_cjr"
.Replacement.Text = cjr
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'service charge
With wrdApp.Selection.Find
.Text = "re_fwf"
.Replacement.Text = fwf
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
'--------------------------------------------------------
Set xlapp = CreateObject("Excel.Application")
ysda = xlapp.Text(ysje, "[dbnum2]")
bzda = xlapp.Text(bzj, "[dbnum2]")
cjda = xlapp.Text(cjje, "[dbnum2]")
wrdApp.Selection.Find.Replacement.ClearFormatting 'Capital budget
With wrdApp.Selection.Find
.Text = "re_ysda"
.Replacement.Text = ysda
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Cash deposit in words
With wrdApp.Selection.Find
.Text = "re_bzda"
.Replacement.Text = bzda
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdApp.Selection.Find.Replacement.ClearFormatting 'Transaction amount in words
With wrdApp.Selection.Find
.Text = "re_cjda"
.Replacement.Text = cjda
.Forward = True
.Wrap = wdFindContinue
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
End Sub
Function introduction
1. Calculation period
According to the user's choice of purchase method and announcement release date, the corresponding end date and opening date of the purchase document are calculated. Note: when calculating the period of this procedure, the date of sale is not calculated, and the bid opening time is calculated as "ending" (not the announcement date and the bid opening date).
Measures for the administration of tendering and bidding of government purchased goods and services (order 87) article 85 Where the Measures stipulate that the period shall be calculated on a daily basis, the starting day shall not be included and shall be calculated from the next day. If the last day of the period is a national legal holiday, it shall be postponed to the next day after the holiday as the last day of the period.
24 hours a day, full 24 hours is a day, and the calculation period of this procedure is calculated according to full days.
2. Generate documents required in procurement (bidding)
There are various files required for filing, pre bid, mid bid and post bid in the document library. After the user enters the project information, the template file in the document library is automatically called for replacement and stored in the new project folder on the desktop. The folder naming method is: purchasing unit - purchasing method - project name.
3. Save and import project information
After the program inputs the project information, you can save the project information (TXT format text) to the project folder. The next time you run the program, you can import the previously saved project information text without repeatedly entering the project information.
4. Calculate the procurement (bidding) agency service fee
Select the agent category and enter the transaction amount to calculate the agent service.
5. Common website links
Click the website button to automatically open the corresponding website.
6. View laws and regulations
The laws and regulations commonly used in bidding and procurement are collected to facilitate learning and reference.
7. The bid opening form integrates data transmission and calculation functions
1) After entering the name of the unit submitting the response (bid) document, click the button to automatically transfer the supplier list to other forms.
2) One click to transfer the quotation in the bid opening table to the price score calculation table and calculate the price score.
3) In the score summary table, the price score is transferred with one key to calculate the total score and automatic ranking of each bidder.
