Excel VBA: Looping through table rows and replacing keyterms in word template Announcing the...
Is the Standard Deduction better than Itemized when both are the same amount?
Does classifying an integer as a discrete log require it be part of a multiplicative group?
Is it a good idea to use CNN to classify 1D signal?
Do wooden building fires get hotter than 600°C?
Using audio cues to encourage good posture
What are the out-of-universe reasons for the references to Toby Maguire-era Spider-Man in ITSV
How to tell that you are a giant?
Trademark violation for app?
Should I use a zero-interest credit card for a large one-time purchase?
Maximum summed powersets with non-adjacent items
How do I find out the mythology and history of my Fortress?
Why are there no cargo aircraft with "flying wing" design?
Is CEO the profession with the most psychopaths?
Is it ethical to give a final exam after the professor has quit before teaching the remaining chapters of the course?
Dating a Former Employee
Amount of permutations on an NxNxN Rubik's Cube
Where are Serre’s lectures at Collège de France to be found?
2001: A Space Odyssey's use of the song "Daisy Bell" (Bicycle Built for Two); life imitates art or vice-versa?
Closed form of recurrent arithmetic series summation
What does this Jacques Hadamard quote mean?
What causes the direction of lightning flashes?
What font is "z" in "z-score"?
When a candle burns, why does the top of wick glow if bottom of flame is hottest?
Generate an RGB colour grid
Excel VBA: Looping through table rows and replacing keyterms in word template
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
$begingroup$
So below is my code for a sub which does the following:
read header of a table into an array
reads first row of said table into a 2nd array
loops through the first array and replaces the marked keywords
<keyword>in a word template file (.dotx) with the data row cell (includes header and body).
This sub is then called by another sub specifiying both table rows and the template file.
Now since I am not a programmer by profession I am pretty sure that this is far from the best way to do it efficiently, so any comments or improvements are much appreciated.
Sub ReplacefromRange(header As Range, DataRow As Range, TemplateFile)
Dim heading() As String
Dim i As Integer
Dim x As Variant
' Creates a first array that takes "header" as argument
i = -1
For Each x In header.Cells
i = i + 1
ReDim Preserve heading(i) As String
heading(i) = x.Value
Next x
Dim data() As String
Dim y As Variant
Dim j As Integer
' Creates second array that takes "DataRow" as argument
j = -1
For Each y In DataRow.Cells
j = j + 1
ReDim Preserve data(j) As String
data(j) = y.Value
Next y
' Uses a for each loop through heading() to find and replace individual entries in the "TemplateFile" argument
Dim z As Variant
i = 0
For Each z In heading()
With TemplateFile 'looks at the word document body
.Application.Selection.Find.Text = "<" & heading(i) & ">"
.Application.Selection.Find.Execute
.Application.Selection = data(i)
.Application.Selection.EndOf
' the part below looks at the header content
.Sections(1).headers(wdHeaderFooterPrimary).Range.Find.Execute _
FindText:="<" & heading(i) & ">", Format:=False, ReplaceWith:=data(i), Replace:=wdReplaceAll, Wrap:=wdFindContinue
i = i + 1
End With
Next z
End Sub
vba excel ms-word
asked 25 secs ago
NickNick
11
New contributor
Nick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
add a comment |
$begingroup$
So below is my code for a sub which does the following:
read header of a table into an array
reads first row of said table into a 2nd array
loops through the first array and replaces the marked keywords
<keyword>in a word template file (.dotx) with the data row cell (includes header and body).
This sub is then called by another sub specifiying both table rows and the template file.
Now since I am not a programmer by profession I am pretty sure that this is far from the best way to do it efficiently, so any comments or improvements are much appreciated.
Sub ReplacefromRange(header As Range, DataRow As Range, TemplateFile)
Dim heading() As String
Dim i As Integer
Dim x As Variant
' Creates a first array that takes "header" as argument
i = -1
For Each x In header.Cells
i = i + 1
ReDim Preserve heading(i) As String
heading(i) = x.Value
Next x
Dim data() As String
Dim y As Variant
Dim j As Integer
' Creates second array that takes "DataRow" as argument
j = -1
For Each y In DataRow.Cells
j = j + 1
ReDim Preserve data(j) As String
data(j) = y.Value
Next y
' Uses a for each loop through heading() to find and replace individual entries in the "TemplateFile" argument
Dim z As Variant
i = 0
For Each z In heading()
With TemplateFile 'looks at the word document body
.Application.Selection.Find.Text = "<" & heading(i) & ">"
.Application.Selection.Find.Execute
.Application.Selection = data(i)
.Application.Selection.EndOf
' the part below looks at the header content
.Sections(1).headers(wdHeaderFooterPrimary).Range.Find.Execute _
FindText:="<" & heading(i) & ">", Format:=False, ReplaceWith:=data(i), Replace:=wdReplaceAll, Wrap:=wdFindContinue
i = i + 1
End With
Next z
End Sub
vba excel ms-word
asked 25 secs ago
NickNick
11
New contributor
Nick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
add a comment |
$begingroup$
So below is my code for a sub which does the following:
read header of a table into an array
reads first row of said table into a 2nd array
loops through the first array and replaces the marked keywords
<keyword>in a word template file (.dotx) with the data row cell (includes header and body).
This sub is then called by another sub specifiying both table rows and the template file.
Now since I am not a programmer by profession I am pretty sure that this is far from the best way to do it efficiently, so any comments or improvements are much appreciated.
Sub ReplacefromRange(header As Range, DataRow As Range, TemplateFile)
Dim heading() As String
Dim i As Integer
Dim x As Variant
' Creates a first array that takes "header" as argument
i = -1
For Each x In header.Cells
i = i + 1
ReDim Preserve heading(i) As String
heading(i) = x.Value
Next x
Dim data() As String
Dim y As Variant
Dim j As Integer
' Creates second array that takes "DataRow" as argument
j = -1
For Each y In DataRow.Cells
j = j + 1
ReDim Preserve data(j) As String
data(j) = y.Value
Next y
' Uses a for each loop through heading() to find and replace individual entries in the "TemplateFile" argument
Dim z As Variant
i = 0
For Each z In heading()
With TemplateFile 'looks at the word document body
.Application.Selection.Find.Text = "<" & heading(i) & ">"
.Application.Selection.Find.Execute
.Application.Selection = data(i)
.Application.Selection.EndOf
' the part below looks at the header content
.Sections(1).headers(wdHeaderFooterPrimary).Range.Find.Execute _
FindText:="<" & heading(i) & ">", Format:=False, ReplaceWith:=data(i), Replace:=wdReplaceAll, Wrap:=wdFindContinue
i = i + 1
End With
Next z
End Sub
vba excel ms-word
asked 25 secs ago
NickNick
11
New contributor
Nick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
So below is my code for a sub which does the following:
read header of a table into an array
reads first row of said table into a 2nd array
loops through the first array and replaces the marked keywords
<keyword>in a word template file (.dotx) with the data row cell (includes header and body).
This sub is then called by another sub specifiying both table rows and the template file.
Now since I am not a programmer by profession I am pretty sure that this is far from the best way to do it efficiently, so any comments or improvements are much appreciated.
Sub ReplacefromRange(header As Range, DataRow As Range, TemplateFile)
Dim heading() As String
Dim i As Integer
Dim x As Variant
' Creates a first array that takes "header" as argument
i = -1
For Each x In header.Cells
i = i + 1
ReDim Preserve heading(i) As String
heading(i) = x.Value
Next x
Dim data() As String
Dim y As Variant
Dim j As Integer
' Creates second array that takes "DataRow" as argument
j = -1
For Each y In DataRow.Cells
j = j + 1
ReDim Preserve data(j) As String
data(j) = y.Value
Next y
' Uses a for each loop through heading() to find and replace individual entries in the "TemplateFile" argument
Dim z As Variant
i = 0
For Each z In heading()
With TemplateFile 'looks at the word document body
.Application.Selection.Find.Text = "<" & heading(i) & ">"
.Application.Selection.Find.Execute
.Application.Selection = data(i)
.Application.Selection.EndOf
' the part below looks at the header content
.Sections(1).headers(wdHeaderFooterPrimary).Range.Find.Execute _
FindText:="<" & heading(i) & ">", Format:=False, ReplaceWith:=data(i), Replace:=wdReplaceAll, Wrap:=wdFindContinue
i = i + 1
End With
Next z
End Sub
vba excel ms-word
vba excel ms-word
asked 25 secs ago
NickNick
11
New contributor
Nick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 25 secs ago
NickNick
11
New contributor
Nick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 25 secs ago
NickNick
11
New contributor
Nick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 25 secs ago
NickNick
11
asked 25 secs ago
NickNick
11
11
New contributor
Nick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Nick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Nick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
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: "196"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
Nick is a new contributor. Be nice, and check out our Code of Conduct.
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%2fcodereview.stackexchange.com%2fquestions%2f217651%2fexcel-vba-looping-through-table-rows-and-replacing-keyterms-in-word-template%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Nick is a new contributor. Be nice, and check out our Code of Conduct.
Nick is a new contributor. Be nice, and check out our Code of Conduct.
Nick is a new contributor. Be nice, and check out our Code of Conduct.
Nick is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Code Review Stack Exchange!
- 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.
Use MathJax to format equations. MathJax reference.
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%2fcodereview.stackexchange.com%2fquestions%2f217651%2fexcel-vba-looping-through-table-rows-and-replacing-keyterms-in-word-template%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
