Use VBA (Visual Basic for Application) macro in Office office software
Tang Tiexin
Talking about the macro, many people think of a macro virus. In fact, if there is genuine anti-virus software, open real-time protection, set a macro security, and use macro to complete many office automation.
Open Word software, "Tools" from the file main menu -> "Macro" -> "Security", set security, so you can select the execution macro. When installing Office, you should be customized. You can choose "Visual Basic Help" in the Office Tools so that a very complete help documentation on the VBA programming environment, VBA language reference, VBA object, and the like. From "Tools" -> Macro -> "Visual Basic Editor" to open the VBA programming environment - "Microsoft Visual Basic". Open Help -> "Microsoft Visual Basic Help" in the programming environment, you can see it.
Many people think that Visual Basic is useless, in fact, the programming language of the Visual Basic series includes VB, VBA, VBScript, etc., can write web pages, office automation programming, application programming. In some other company applications, it is also possible to use VBA, such as Corel Draw. Here is a few examples to illustrate the use of VBA.
If there are dozens of documents that need to be merged into a document, open one by one, copy is troublesome, can be done by macro.
Example 1: Merged documentation. Create a form, put a button on the form. Toolbox can open from "View" -> Toolbox.
'Button event
Private sub fascistbutton1_click ()
DIM Spath as string 'spath is used to save the path
DIM TEM AS STRING 'Intermediate Variables
Set newdoc = documents.add 'Add a new document with Documents.Add
WITH NewDoc
.Saves filename: = "d: /txtal.doc" 'New Document Saved Full Path
End with
ActiveDocument.close 'Close new documentation
WITH DIALOGS (WDDIALOGFILEFIND) 'Settings Text Find Dialog
.Sortby = 2 'file is arranged by name
.Searchname = "* .txt" 'only lists text files
.Update 'update
End with
If Dialogs (WDDIALOGFILEFIND) .SHOW = -1 Then 'If you press it,
Spath = Dialogs (WDDIALOGFILEFIND) .SearchPath 'The path to the dialog opens the path to Spath
Dialogs (WDDIALOGFILEFIND). EXECUTE
Else
EXIT SUB 'If you cancel or close, you withdraw it.
END IF
WITH Application.fileSearch 'Sets the lookup of the application
.Newsearch 'new lookup
.Lookin = spath 'Sets the path to the new look into the path of the file dialog (Spath)
.SearchSubfolders = true 'Find subdirectory .filetype = msofiletypellfiles' Find all file types, here you can set additional types, see the fileEType property of the FileSearch object
.Filename = "* .txt" 'Find files with complexing TXT
.Execute 'execution file lookup
Documents.Open ("D: /TXTAL.DOC") 'Opens "D: /TXTAL.DOC"
SET RANGE2 = ActiveDocument.content 'Sets the contents of the Range2 object to the active document
For i = 1 to .foundfiles.count 'Open the found file, copy the file content, add to the new D: /TXTAL.DOC file
Documents.open (.foundFiles (i)) 'Opens the I found the file
ActiveDocument.content.select 'content of the full event file
Selection.copy 'Copy the content
ActiveDocument.close 'Close the event document
Range2.collapse direction: = wdcollapsend 'Set the final position to Range2
Range2.paste 'paste the copy content to RANGE2
Next I
ActiveDocument.save 'Save Activity Documentation
ActiveDocument.close 'Close the event document
End with
End Sub
If you have a large number of Word documents that need to modify a terminology or what a fixed statement is turned on one by one, it is troublesome, using macros to complete this task very quickly, and will not hurt. This task can be completed for an example.
Example 2: Full text lookup replacement.
Private sub fascistbutton1_click ()
DIM Spath As String
WITH DIALOGS (WDDIALOGFILEFIND) 'Settings Text Find Dialog
.Sortby = 2 'file is arranged by name
.Searchname = "* .doc" 'only lists * .doc files
.Update 'update
End with
If Dialogs (WDDIALOGFILEFIND) .SHOW = -1 Then 'If you press it,
Spath = Dialogs (WDDIALOGFILEFIND) .SearchPath 'The path to the dialog opens the path to Spath
Dialogs (WDDIALOGFILEFIND). EXECUTE
Else
EXIT SUB 'If you cancel or close, you withdraw it.
END IF
WITH Application.FileSearch
.Newsearch
.Lookin = spath
.SearchSubfolders = true
.Filetype = msofiletypeworddocuments' Find file types for Word documents
.Executefortor i = 1 to .foundfiles.count
Documents.Open (.foundFiles (i))
ActiveDocument.select.select
With Dialogs (WDDIALDITREPLACE)
ON Error Goto ERR
.Find = textbox1.text
.Replace = TextBox2.text
.ReplaceAll = TRUE
.Execute
End with
ActiveDocument.save
ActiveDocument.Close
Next I
End with
Err:
If TextBox1.text = "" "
ActiveDocument.save
ActiveDocument.Close
END IF
Msgbox "Please enter the string of replacement"
End Sub
I didn't find the function of changing the row, the column varied, so I wrote a macro.
Example 3: The row becomes column in EXEL. Open the programming environment from the "Tool" -> "Macro" -> "Visual Basic Editor" in EXEL, create a macro by "Insert" -> Module.
Sub change () 'Change Macro
DIM ROW (0 to 20, 0 to 20) AS String 'Defines a 21 * 21 two-dimensional array
Range ("a1"). SELECT 'Selected A1 cell
For i = 0 TO 20
For j = 0 to 20
Row (i, j) = activecell.offset (i, j). Value 'The content of the cell of the A1-Displacement (I, J) cells is added to the array ROW (I, J)
NEXT J
Next I
Range ("a1"). SELECT
ON Error Goto ERR
For i = 0 TO 20
For j = 0 to 20
Activecell.offset (i, j) .Value = ROW (J, I) '' Putting the contents of the array ROW (J, I) to the cell of the A1 displacement (I, J) cells
NEXT J
Next I
EXIT SUB
Err:
MsgBox Error
End Sub
Netscaner@sina.com.cn
