VBA makro zinātnisko nosaukumu formatējuma atjaunināšanai Word dokumentos

VBA makro, lai atjauninātu zinātnisko nosaukumu formatējumu programmā Word

Visual Basic for Applications (VBA) programmai Word un Excel

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

VBA skripts, lai mantotu gadījumu no Excel datiem

VBA Excel un Word integrācijai

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

Bieži uzdotie jautājumi par VBA makro teksta formatēšanai

1. Kā atvērt faila dialoglodziņu VBA?
2. Izmantot Application.FileDialog(msoFileDialogFilePicker) lai atvērtu faila dialoglodziņu un ļautu lietotājiem izvēlēties failu.
3. Kā es varu iegūt Excel gadījumu VBA?
4. Tu vari izmantot GetObject("", "Excel.Application") lai iegūtu esošu Excel instanci vai CreateObject("Excel.Application") lai izveidotu jaunu.
5. Kā atvērt Excel darbgrāmatu VBA?
6. Izmantot xlbook.Workbooks.Open("filePath") lai atvērtu Excel darbgrāmatu no norādītā faila ceļa.
7. Kāds ir labākais veids, kā VBA nolasīt datu diapazonu no Excel?
8. Izmantojot xlsheet.Range("A1").CurrentRegion.Value nolasa visu pašreizējo lapas reģionu, sākot no šūnas A1, masīvā.
9. Kā es varu pārvietot kursoru uz Word dokumenta sākumu VBA?
10. Komanda Selection.HomeKey wdStory pārvieto atlasi uz dokumenta sākumu.
11. Ko Selection.Find.ClearFormatting dara VBA?
12. Tas notīra visus iepriekšējos formatēšanas iestatījumus, kas tika lietoti atrašanas darbībai, nodrošinot jaunu meklēšanu.
13. Kā VBA mainīt tekstu uz pareizo reģistru?
14. Izmantojiet StrConv(text, vbProperCase) funkcija, lai pārvērstu tekstu pareizajā reģistrā.
15. Kā VBA tekstam lietot vairākas formatēšanas opcijas?
16. Varat izmantot dažādus formatējumus, piemēram, slīprakstu, treknrakstu un fonta krāsu rng.Font.Italic = True, rng.Font.Bold = True, un rng.Font.Color = RGB(200, 187, 0).