Setting language, text direction and alignment in

2019-08-28 21:21发布

问题:

I've made an excel visual basic script that takes data from an excel sheet, and produces a paragraph in a word sheet for each row. The default language for the document is Hebrew, with text aligned right and direction right to left. For one (the last) line in the paragraph, I want to set the language to English, the direction left to right, and the alignment right. Then, for the first line in the next paragraph change back to Hebrew, direction right to left and alignment right. When recording a macro in word when being on the last line of a paragraph, pressing home, shift end, clicking the icons on the toolbar to change language, direction and alignment as wanted, I get:

Sub test()
    Selection.HomeKey Unit:=wdLine
    Selection.EndKey Unit:=wdLine, Extend:=wdExtend
    Application.Keyboard (2057)
    Selection.LtrPara
    Selection.ParagraphFormat.Alignment = wdAlignParagraphRight
End Sub

One thing that surprised me is that changing the language is recorded as if I was using the keyboard, instead of being a property of the object, just like direction and alignment. How do I translate this to excel visual basic? The line in question is currently added by using:

With f
    .Content.InsertAfter Format(a, "standard") & " x " & Format(b, "#,##0.000") & " x " & Format(c, "#,##0.000") & " / " & Format(d, "#,##0.000") & " = " & Format(e, "standard")
    .Content.InsertParagraphAfter
End With

Thanks,

Ernst

回答1:

Okay, I've got a workaround, this is only a partial answer. This is not a solution in Excel visual basic, but one in Word:

Search for all occurences of ^13[!^13]@x*^13 and then do the language, alignment and direction change. When recording it as a macro in word, I get the following:

Sub Macro1()

Selection.Find.ClearFormatting
With Selection.Find
    .Text = "^13[!^13]@x*^13"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchKashida = False
    .MatchDiacritics = False
    .MatchAlefHamza = False
    .MatchControl = False
    .MatchAllWordForms = False
    .MatchSoundsLike = False
    .MatchWildcards = True
End With
Selection.LtrPara
Selection.ParagraphFormat.Alignment = wdAlignParagraphRight
Selection.Find.ClearFormatting
With Selection.Find
    .Text = "^13[!^13]@x*^13"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchKashida = False
    .MatchDiacritics = False
    .MatchAlefHamza = False
    .MatchControl = False
    .MatchAllWordForms = False
    .MatchSoundsLike = False
    .MatchWildcards = True
End With

End Sub

But when running this on a freshly generated Word document, this does not do what it's supposed to do. Any ideas for a workaround on that? Btw, can I add and run a word macro to a document generated by excel vb using excel vb?