VBA makro Wordi dokumentides teaduslike nimede vormindamise värskendamiseks

VBA makro teaduslike nimede vormingu värskendamiseks Wordis

Visual Basic for Applications (VBA) Wordi ja Exceli jaoks

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-skript juhtude pärimiseks Exceli andmetest

VBA Exceli ja Wordi integreerimiseks

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

Levinud küsimused teksti vormindamise VBA makrode kohta

1. Kuidas avada VBA-s failidialoog?
2. Kasuta Application.FileDialog(msoFileDialogFilePicker) et avada failidialoog ja lubada kasutajatel faili valida.
3. Kuidas saan VBA-s Exceli eksemplari hankida?
4. Sa võid kasutada GetObject("", "Excel.Application") olemasoleva Exceli eksemplari hankimiseks või CreateObject("Excel.Application") uue loomiseks.
5. Kuidas avada VBA-s Exceli töövihik?
6. Kasuta xlbook.Workbooks.Open("filePath") Exceli töövihiku avamiseks määratud failiteelt.
7. Mis on parim viis VBA-s Excelist erinevate andmete lugemiseks?
8. Kasutades xlsheet.Range("A1").CurrentRegion.Value loeb kogu lehe praeguse piirkonna alates lahtrist A1 massiiviks.
9. Kuidas ma saan VBA-s kursori Wordi dokumendi algusesse viia?
10. Käsk Selection.HomeKey wdStory nihutab valiku dokumendi algusesse.
11. Mida teeb Selection.Find.ClearFormatting VBA-s?
12. See kustutab kõik varasemad otsimistoimingule rakendatud vormingusätted, tagades uue otsingu.
13. Kuidas muuta tekst VBA-s õigeks suureks?
14. Kasuta StrConv(text, vbProperCase) funktsioon teksti õigesse suurtähte teisendamiseks.
15. Kuidas rakendada VBA-s tekstile mitut vormingusuvandit?
16. Saate kasutada erinevaid vorminguid, näiteks kaldkirja, paksu kirja ja fondivärvi rng.Font.Italic = True, rng.Font.Bold = Trueja rng.Font.Color = RGB(200, 187, 0).