R
Rawce
Hello All,
I have a macro that exports text from Excel to Word; it's running from
Excel and I've posted here as it seems to be an Excel problem (please
correct me if otherwise). All is working fine apart from the
following:
' Loop through each StoryRange, searching for each Find value in the
array and replacing it with the values in the Replace array.
For Each rngStory In wdDoc.StoryRanges
Do Until rngStory Is Nothing
For iArray = 0 To 37
With rngStory.Find
.Text = strFind(iArray)
.Replacement.Text = strReplace(iArray)
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
Next iArray
Set rngStory = rngStory.NextStoryRange
Loop
Next
The array is built up earlier using the following:
Dim strFind(37), strReplace(37) As String
strFind(14) = "xDocs2Selectx"
strReplace(14) =
ActiveWorkbook.Names("Docs2Select").RefersToRange.Value
xDocs2Selectx is one of a number of dummy text strings in a blank .doc
that I want to replace with the contents of a cell I've named
Docs2Select on my .xls worksheet.
My problem here is the 255 character limit that seems to be in place
when using strings in an array. The cells I'm copying across are
usually around 500 characters, and in my case nothing gets copied
across instead. I've broken the code at an appropriate point and the
text seems to be read into the strReplace(iArray), but never makes it
into the .Replacement.Text part upon Execute. Cells in Excel with less
than 256 characters work fine.
Is there a way round this limit in Excel? As you can see I have up to
38 of these find and replaces to do so I'd prefer to keep it in an
array.
Thanks for any help you can offer,
Ross.
I have a macro that exports text from Excel to Word; it's running from
Excel and I've posted here as it seems to be an Excel problem (please
correct me if otherwise). All is working fine apart from the
following:
' Loop through each StoryRange, searching for each Find value in the
array and replacing it with the values in the Replace array.
For Each rngStory In wdDoc.StoryRanges
Do Until rngStory Is Nothing
For iArray = 0 To 37
With rngStory.Find
.Text = strFind(iArray)
.Replacement.Text = strReplace(iArray)
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
Next iArray
Set rngStory = rngStory.NextStoryRange
Loop
Next
The array is built up earlier using the following:
Dim strFind(37), strReplace(37) As String
strFind(14) = "xDocs2Selectx"
strReplace(14) =
ActiveWorkbook.Names("Docs2Select").RefersToRange.Value
xDocs2Selectx is one of a number of dummy text strings in a blank .doc
that I want to replace with the contents of a cell I've named
Docs2Select on my .xls worksheet.
My problem here is the 255 character limit that seems to be in place
when using strings in an array. The cells I'm copying across are
usually around 500 characters, and in my case nothing gets copied
across instead. I've broken the code at an appropriate point and the
text seems to be read into the strReplace(iArray), but never makes it
into the .Replacement.Text part upon Execute. Cells in Excel with less
than 256 characters work fine.
Is there a way round this limit in Excel? As you can see I have up to
38 of these find and replaces to do so I'd prefer to keep it in an
array.
Thanks for any help you can offer,
Ross.