How to set Replacement Text to Bullet Format when creating WordDoc from Excel using VBA
Hello and thank you in advance.
I am creating a very complex Word document out of an Excelfile using VBA.
It shall be possible to activate something and a text written in a cell shall be transfered to the word document. I got that already done. But at some parts it must be in a Bullets-Format.
Right now I use tags like "<< replacementPoint1 >>" in the Word Template, find and replace them with the ReplacementText using something as easy as that one-line-code:
With WordDoc.Content.Find
.Execute FindText:=ReplacementTextF, ReplaceWith:=ReplacementText, Replace:=2
End With
But how can I set the replacementText to a bullet or a number like this:
ReplacementText some more text here......
- ReplacementText some more text here.....
excel vba excel-vba ms-word format
edited Nov 15 '18 at 8:25
Pᴇʜ
23.6k62952
asked Nov 15 '18 at 8:16
Christian GoldChristian Gold
1179
add a comment |
Hello and thank you in advance.
I am creating a very complex Word document out of an Excelfile using VBA.
It shall be possible to activate something and a text written in a cell shall be transfered to the word document. I got that already done. But at some parts it must be in a Bullets-Format.
Right now I use tags like "<< replacementPoint1 >>" in the Word Template, find and replace them with the ReplacementText using something as easy as that one-line-code:
With WordDoc.Content.Find
.Execute FindText:=ReplacementTextF, ReplaceWith:=ReplacementText, Replace:=2
End With
But how can I set the replacementText to a bullet or a number like this:
ReplacementText some more text here......
- ReplacementText some more text here.....
excel vba excel-vba ms-word format
edited Nov 15 '18 at 8:25
Pᴇʜ
23.6k62952
asked Nov 15 '18 at 8:16
Christian GoldChristian Gold
1179
add a comment |
Hello and thank you in advance.
I am creating a very complex Word document out of an Excelfile using VBA.
It shall be possible to activate something and a text written in a cell shall be transfered to the word document. I got that already done. But at some parts it must be in a Bullets-Format.
Right now I use tags like "<< replacementPoint1 >>" in the Word Template, find and replace them with the ReplacementText using something as easy as that one-line-code:
With WordDoc.Content.Find
.Execute FindText:=ReplacementTextF, ReplaceWith:=ReplacementText, Replace:=2
End With
But how can I set the replacementText to a bullet or a number like this:
ReplacementText some more text here......
- ReplacementText some more text here.....
excel vba excel-vba ms-word format
edited Nov 15 '18 at 8:25
Pᴇʜ
23.6k62952
asked Nov 15 '18 at 8:16
Christian GoldChristian Gold
1179
Hello and thank you in advance.
I am creating a very complex Word document out of an Excelfile using VBA.
It shall be possible to activate something and a text written in a cell shall be transfered to the word document. I got that already done. But at some parts it must be in a Bullets-Format.
Right now I use tags like "<< replacementPoint1 >>" in the Word Template, find and replace them with the ReplacementText using something as easy as that one-line-code:
With WordDoc.Content.Find
.Execute FindText:=ReplacementTextF, ReplaceWith:=ReplacementText, Replace:=2
End With
But how can I set the replacementText to a bullet or a number like this:
ReplacementText some more text here......
- ReplacementText some more text here.....
excel vba excel-vba ms-word format
excel vba excel-vba ms-word format
edited Nov 15 '18 at 8:25
Pᴇʜ
23.6k62952
asked Nov 15 '18 at 8:16
Christian GoldChristian Gold
1179
edited Nov 15 '18 at 8:25
Pᴇʜ
23.6k62952
asked Nov 15 '18 at 8:16
Christian GoldChristian Gold
1179
edited Nov 15 '18 at 8:25
Pᴇʜ
23.6k62952
edited Nov 15 '18 at 8:25
Pᴇʜ
23.6k62952
edited Nov 15 '18 at 8:25
Pᴇʜ
23.6k62952
23.6k62952
asked Nov 15 '18 at 8:16
Christian GoldChristian Gold
1179
asked Nov 15 '18 at 8:16
Christian GoldChristian Gold
1179
asked Nov 15 '18 at 8:16
Christian GoldChristian Gold
1179
1179
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
There are a number of possible approaches. One would be to not use wdReplaceAll - instead, the code would do one "find" at a time, apply the required formatting, then loop the find/replace and format again and again until nothing more is found. There are lots of examples of this approach here on Stack Overflow and on the Internet, in general.
Quicker would be to leverage the fact that Word can apply certain kinds of formatting as part of the Replace functionality. In the Word UI, press Ctrl+H to view the Find & Replace dialog box; click "More" then click "Format" to see the possibilities. Bullets and Numbering is not a selection, here... But Styles are.
If you're working with a template to generate these documents (highly recommended) then define the bullets / numbering to be used as styles in the template. If no template is used, the code can create the style definition(s) on-the-fly.
Specify the Style name (case-sensitive!) as part of the Replacement properties defined for the Find and set the Format property to True. Something like:
Dim rngFind as Word.Range 'Object if late-binding
Set rngFind = WordDoc.Content
With rngFind.Find
.ClearFormatting
.Replacement.ClearFormatting
.Replacement.Style = "My style"
.Format = True
.Execute FindText:=ReplacementTextF, ReplaceWith:=ReplacementText, Replace:=2
End With
answered Nov 15 '18 at 11:48
Cindy MeisterCindy Meister
15.6k102337
Thank you. It worked with this code smoothly. I created a new style in the template and used it at .replacement.Style. Just the font size was not correct afterwards but I set it to the proper value and now it fits my needs.
– Christian Gold
Nov 15 '18 at 13:43
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53315018%2fhow-to-set-replacement-text-to-bullet-format-when-creating-worddoc-from-excel-us%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
There are a number of possible approaches. One would be to not use wdReplaceAll - instead, the code would do one "find" at a time, apply the required formatting, then loop the find/replace and format again and again until nothing more is found. There are lots of examples of this approach here on Stack Overflow and on the Internet, in general.
Quicker would be to leverage the fact that Word can apply certain kinds of formatting as part of the Replace functionality. In the Word UI, press Ctrl+H to view the Find & Replace dialog box; click "More" then click "Format" to see the possibilities. Bullets and Numbering is not a selection, here... But Styles are.
If you're working with a template to generate these documents (highly recommended) then define the bullets / numbering to be used as styles in the template. If no template is used, the code can create the style definition(s) on-the-fly.
Specify the Style name (case-sensitive!) as part of the Replacement properties defined for the Find and set the Format property to True. Something like:
Dim rngFind as Word.Range 'Object if late-binding
Set rngFind = WordDoc.Content
With rngFind.Find
.ClearFormatting
.Replacement.ClearFormatting
.Replacement.Style = "My style"
.Format = True
.Execute FindText:=ReplacementTextF, ReplaceWith:=ReplacementText, Replace:=2
End With
answered Nov 15 '18 at 11:48
Cindy MeisterCindy Meister
15.6k102337
Thank you. It worked with this code smoothly. I created a new style in the template and used it at .replacement.Style. Just the font size was not correct afterwards but I set it to the proper value and now it fits my needs.
– Christian Gold
Nov 15 '18 at 13:43
add a comment |
There are a number of possible approaches. One would be to not use wdReplaceAll - instead, the code would do one "find" at a time, apply the required formatting, then loop the find/replace and format again and again until nothing more is found. There are lots of examples of this approach here on Stack Overflow and on the Internet, in general.
Quicker would be to leverage the fact that Word can apply certain kinds of formatting as part of the Replace functionality. In the Word UI, press Ctrl+H to view the Find & Replace dialog box; click "More" then click "Format" to see the possibilities. Bullets and Numbering is not a selection, here... But Styles are.
If you're working with a template to generate these documents (highly recommended) then define the bullets / numbering to be used as styles in the template. If no template is used, the code can create the style definition(s) on-the-fly.
Specify the Style name (case-sensitive!) as part of the Replacement properties defined for the Find and set the Format property to True. Something like:
Dim rngFind as Word.Range 'Object if late-binding
Set rngFind = WordDoc.Content
With rngFind.Find
.ClearFormatting
.Replacement.ClearFormatting
.Replacement.Style = "My style"
.Format = True
.Execute FindText:=ReplacementTextF, ReplaceWith:=ReplacementText, Replace:=2
End With
answered Nov 15 '18 at 11:48
Cindy MeisterCindy Meister
15.6k102337
Thank you. It worked with this code smoothly. I created a new style in the template and used it at .replacement.Style. Just the font size was not correct afterwards but I set it to the proper value and now it fits my needs.
– Christian Gold
Nov 15 '18 at 13:43
add a comment |
There are a number of possible approaches. One would be to not use wdReplaceAll - instead, the code would do one "find" at a time, apply the required formatting, then loop the find/replace and format again and again until nothing more is found. There are lots of examples of this approach here on Stack Overflow and on the Internet, in general.
Quicker would be to leverage the fact that Word can apply certain kinds of formatting as part of the Replace functionality. In the Word UI, press Ctrl+H to view the Find & Replace dialog box; click "More" then click "Format" to see the possibilities. Bullets and Numbering is not a selection, here... But Styles are.
If you're working with a template to generate these documents (highly recommended) then define the bullets / numbering to be used as styles in the template. If no template is used, the code can create the style definition(s) on-the-fly.
Specify the Style name (case-sensitive!) as part of the Replacement properties defined for the Find and set the Format property to True. Something like:
Dim rngFind as Word.Range 'Object if late-binding
Set rngFind = WordDoc.Content
With rngFind.Find
.ClearFormatting
.Replacement.ClearFormatting
.Replacement.Style = "My style"
.Format = True
.Execute FindText:=ReplacementTextF, ReplaceWith:=ReplacementText, Replace:=2
End With
answered Nov 15 '18 at 11:48
Cindy MeisterCindy Meister
15.6k102337
There are a number of possible approaches. One would be to not use wdReplaceAll - instead, the code would do one "find" at a time, apply the required formatting, then loop the find/replace and format again and again until nothing more is found. There are lots of examples of this approach here on Stack Overflow and on the Internet, in general.
Quicker would be to leverage the fact that Word can apply certain kinds of formatting as part of the Replace functionality. In the Word UI, press Ctrl+H to view the Find & Replace dialog box; click "More" then click "Format" to see the possibilities. Bullets and Numbering is not a selection, here... But Styles are.
If you're working with a template to generate these documents (highly recommended) then define the bullets / numbering to be used as styles in the template. If no template is used, the code can create the style definition(s) on-the-fly.
Specify the Style name (case-sensitive!) as part of the Replacement properties defined for the Find and set the Format property to True. Something like:
Dim rngFind as Word.Range 'Object if late-binding
Set rngFind = WordDoc.Content
With rngFind.Find
.ClearFormatting
.Replacement.ClearFormatting
.Replacement.Style = "My style"
.Format = True
.Execute FindText:=ReplacementTextF, ReplaceWith:=ReplacementText, Replace:=2
End With
answered Nov 15 '18 at 11:48
Cindy MeisterCindy Meister
15.6k102337
answered Nov 15 '18 at 11:48
Cindy MeisterCindy Meister
15.6k102337
answered Nov 15 '18 at 11:48
Cindy MeisterCindy Meister
15.6k102337
answered Nov 15 '18 at 11:48
Cindy MeisterCindy Meister
15.6k102337
15.6k102337
Thank you. It worked with this code smoothly. I created a new style in the template and used it at .replacement.Style. Just the font size was not correct afterwards but I set it to the proper value and now it fits my needs.
– Christian Gold
Nov 15 '18 at 13:43
add a comment |
Thank you. It worked with this code smoothly. I created a new style in the template and used it at .replacement.Style. Just the font size was not correct afterwards but I set it to the proper value and now it fits my needs.
– Christian Gold
Nov 15 '18 at 13:43
Thank you. It worked with this code smoothly. I created a new style in the template and used it at .replacement.Style. Just the font size was not correct afterwards but I set it to the proper value and now it fits my needs.
– Christian Gold
Nov 15 '18 at 13:43
Thank you. It worked with this code smoothly. I created a new style in the template and used it at .replacement.Style. Just the font size was not correct afterwards but I set it to the proper value and now it fits my needs.
– Christian Gold
Nov 15 '18 at 13:43
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53315018%2fhow-to-set-replacement-text-to-bullet-format-when-creating-worddoc-from-excel-us%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
