用于更新 Word 文档中科学名称格式的 VBA 宏

用于更新 Word 中科学名称格式的 VBA 宏

适用于 Word 和 Excel 的 Visual Basic 应用程序 (VBA)

Sub format_scientific_names()
    Dim xlapp As Object
    Dim xlbook As Object
    Dim xlsheet As Object
    Dim myarray As Variant
    Dim FD As FileDialog
    Dim strSource As String
    Dim i As Long, lognum As Long
    Set FD = Application.FileDialog(msoFileDialogFilePicker)
    With FD
        .Title = "Select the workbook that contains the terms to be italicized"
        .Filters.Clear
        .Filters.Add "Excel Workbooks", "*.xlsx"
        .AllowMultiSelect = False
        If .Show = -1 Then
            strSource = .SelectedItems(1)
        Else
            MsgBox "You did not select the workbook that contains the data"
            Exit Sub
        End If
    End With
    On Error Resume Next
    Set xlapp = GetObject("", "Excel.Application")
    If Err Then
        bstartApp = True
        Set xlapp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    Set xlbook = xlapp.Workbooks.Open(strSource)
    Set xlsheet = xlbook.Worksheets(1)
    myarray = xlsheet.Range("A1").CurrentRegion.Value
    If bstartApp = True Then xlapp.Quit
    Set xlapp = Nothing
    Set xlbook = Nothing
    Set xlsheet = Nothing
    For i = LBound(myarray) To UBound(myarray)
        Selection.HomeKey wdStory
        Selection.Find.ClearFormatting
        With Selection.Find
            Do While .Execute(FindText:=myarray(i, 1), Forward:=True, _
            MatchWildcards:=True, Wrap:=wdFindStop, MatchCase:=False) = True
                Set rng = Selection.Range
                Selection.Collapse wdCollapseEnd
                rng.Font.Italic = True
                rng.Font.Bold = True
                rng.Font.Color = RGB(200, 187, 0)
                rng.Font.Name = "Times New Roman"
                rng.Text = StrConv(rng.Text, vbProperCase)
            Loop
        End With
    Next i
End Sub

从 Excel 数据继承大小写的 VBA 脚本

用于 Excel 和 Word 集成的 VBA

Sub format_scientific_names_inherit_case()
    Dim xlapp As Object
    Dim xlbook As Object
    Dim xlsheet As Object
    Dim myarray As Variant
    Dim FD As FileDialog
    Dim strSource As String
    Dim i As Long, lognum As Long
    Set FD = Application.FileDialog(msoFileDialogFilePicker)
    With FD
        .Title = "Select the workbook that contains the terms to be italicized"
        .Filters.Clear
        .Filters.Add "Excel Workbooks", "*.xlsx"
        .AllowMultiSelect = False
        If .Show = -1 Then
            strSource = .SelectedItems(1)
        Else
            MsgBox "You did not select the workbook that contains the data"
            Exit Sub
        End If
    End With
    On Error Resume Next
    Set xlapp = GetObject("", "Excel.Application")
    If Err Then
        bstartApp = True
        Set xlapp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    Set xlbook = xlapp.Workbooks.Open(strSource)
    Set xlsheet = xlbook.Worksheets(1)
    myarray = xlsheet.Range("A1").CurrentRegion.Value
    If bstartApp = True Then xlapp.Quit
    Set xlapp = Nothing
    Set xlbook = Nothing
    Set xlsheet = Nothing
    For i = LBound(myarray) To UBound(myarray)
        Selection.HomeKey wdStory
        Selection.Find.ClearFormatting
        With Selection.Find
            Do While .Execute(FindText:=myarray(i, 1), Forward:=True, _
            MatchWildcards:=True, Wrap:=wdFindStop, MatchCase:=False) = True
                Set rng = Selection.Range
                Selection.Collapse wdCollapseEnd
                rng.Text = myarray(i, 1)
                rng.Font.Italic = True
                rng.Font.Bold = True
                rng.Font.Color = RGB(200, 187, 0)
                rng.Font.Name = "Times New Roman"
            Loop
        End With
    Next i
End Sub

有关用于文本格式设置的 VBA 宏的常见问题

1. 如何在 VBA 中打开文件对话框？
2. 使用 Application.FileDialog(msoFileDialogFilePicker) 打开文件对话框并允许用户选择文件。
3. 如何在 VBA 中获取 Excel 实例？
4. 您可以使用 GetObject("", "Excel.Application") 获取 Excel 的现有实例或 CreateObject("Excel.Application") 创建一个新的。
5. 如何在 VBA 中打开 Excel 工作簿？
6. 使用 xlbook.Workbooks.Open("filePath") 从指定的文件路径打开 Excel 工作簿。
7. 在 VBA 中从 Excel 读取一系列数据的最佳方法是什么？
8. 使用 xlsheet.Range("A1").CurrentRegion.Value 将从单元格 A1 开始的工作表的整个当前区域读取到数组中。
9. 如何在VBA中将光标移动到Word文档的开头？
10. 命令 Selection.HomeKey wdStory 将所选内容移动到文档的开头。
11. Selection.Find.ClearFormatting 在 VBA 中做什么？
12. 它会清除以前应用于查找操作的任何格式设置，确保重新搜索。
13. 如何在 VBA 中将文本更改为正确的大小写？
14. 使用 StrConv(text, vbProperCase) 将文本转换为正确大小写的函数。
15. 如何对 VBA 中的文本应用多个格式选项？
16. 您可以使用不同的格式，例如斜体、粗体和字体颜色 rng.Font.Italic = True, rng.Font.Bold = True， 和 rng.Font.Color = RGB(200, 187, 0)。