Office常用函数

Excel常用函数

一、检查第一个字符是否是A，如果是，它返回“Yes”，否则返回”No“。

=IF(LEFT(A2, 1)="A", "Yes", "No")

二、Excel 拆分工作表

Private Sub 分拆工作表()
    Dim sht As Worksheet
    Dim MyBook As Workbook
    Set MyBook = ActiveWorkbook
    For Each sht In MyBook.Sheets
    sht.Copy
    ActiveWorkbook.SaveAs Filename:=MyBook.Path & "\" & sht.Name, FileFormat:=xlNormal '将工作簿另存为EXCEL默认格式
    ActiveWorkbook.Close
    Next
    MsgBox "文件已经被分拆完毕!"
    End Sub

Word常用函数

一、选择文档中所有表格

Sub SelectAllTables()
     Dim mytable As Table
      Application.ScreenUpdating = False
      For Each mytable In ActiveDocument.Tables
         mytable.Range.Editors.Add wdEditorEveryone
     Next
    ActiveDocument.SelectAllEditableRanges (wdEditorEveryone)
    ActiveDocument.DeleteAllEditableRanges (wdEditorEveryone)
     Application.ScreenUpdating = True
End Sub

二、删除文档中所有超链接

Sub RemoveHyperlinks()
    Dim hyperlink As Hyperlink
    For Each hyperlink In ActiveDocument.Hyperlinks
        hyperlink.Delete
    Next hyperlink
End Sub

三、按页拆分word文件
1、完整脚本

Option Explicit
Sub DynamicSplitPagesAsDocuments()
Dim oSrcDoc As Document, oNewDoc As Document
Dim strSrcName As String, strNewName As String
Dim oRange As Range
Dim nIndex As Integer, nSubIndex As Integer, nTotalPages As Integer, nBound As Integer
Dim fso As Object
Dim rng As Range
Const nSteps = 2
Set fso = CreateObject("Scripting.FileSystemObject")
Set oSrcDoc = ActiveDocument
Set oRange = oSrcDoc.Content
nTotalPages = ActiveDocument.Content.Information(wdNumberOfPagesInDocument)
oRange.Collapse wdCollapseStart
oRange.Select
For nIndex = 1 To nTotalPages Step nSteps
Set oNewDoc = Documents.Add
If nIndex + nSteps > nTotalPages Then
nBound = nTotalPages
Else
nBound = nIndex + nSteps - 1
End If
For nSubIndex = nIndex To nBound
oSrcDoc.Activate
oSrcDoc.Bookmarks("\page").Range.Copy
oSrcDoc.Windows(1).Activate
Application.Browser.Target = wdBrowsePage
Application.Browser.Next
oNewDoc.Activate
oNewDoc.Windows(1).Selection.Paste
With oNewDoc.Sections(1).Footers(wdHeaderFooterPrimary)
ActiveDocument.Fields.Add .Range, wdFieldPage, "Page"
Set rng = .Range
rng.Collapse wdCollapseEnd
rng.Text = " / "
rng.Collapse wdCollapseEnd
ActiveDocument.Fields.Add rng, wdFieldNumPages, "Pages"
.Range.Collapse wdCollapseEnd
.Range.Fields.Update
.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
End With
Next nSubIndex
strSrcName = oSrcDoc.FullName
strNewName = fso.BuildPath(fso.GetParentFolderName(strSrcName), _
fso.GetBaseName(strSrcName) & "_20240" & (nIndex \ nSteps) + 1 & "." & fso.GetExtensionName(strSrcName))
oNewDoc.SaveAs strNewName
oNewDoc.Close False
Next nIndex
Set oNewDoc = Nothing
Set oRange = Nothing
Set oSrcDoc = Nothing
Set fso = Nothing
MsgBox "结束!"
End Sub

2、 脚本说明

Const nSteps = 2 表示每2页生成一个新word文件
strNewName 表示新生成word文件的文件名