使用vba遍历Excel中的所有文本(在单元格,图表,文本框中) [英] Iterate through all text (in cells, charts, textboxes) in Excel using vba
问题描述
我构建了一个功能来搜索和替换整个Excel文档中的特定文本.我可以使用
I built a function to search for and replace specific text throughout an entire excel document. I can go through the entire document and all its cells just fine using
For Each WS In Worksheets
For Each ActiveCell In WS.UsedRange
If ActiveCell <> "" Then
ActiveCell.Value = ReplaceWord(ActiveCell.Value, Search, Replacement)
End If
next
next
这可行,但是很多文档在文本框和其他位置都有带有标题的图表,我不确定如何在不知道其确切名称的情况下访问它们.基本上,我想搜索excel文档中的每个字符串,并使用我的ReplaceWord函数替换单词.但是我迷失了:)
This works, but a lot of the documents have charts with titles in textboxes and other places and I am not sure how to access those without knowing their exact names etc. Basically I would like to do a search for every single string in the excel document and use my ReplaceWord function to replace the words. But I am lost on how :)
任何帮助将不胜感激.谢谢!
Any help would be much appreciated. Thanks!
推荐答案
似乎您必须迭代图表的属性.一旦设置了 cht
变量,就可以使用VBE中的locals窗口查看其他属性.这不是详尽的选项列表,但足以让您入门!
It seems like you will have to iterate over the properties of the chart. You can use the locals window in the VBE to view other properties of the cht
variable once it's been set. This is not an exhaustive list of options, but it should be enough to get you started!
Sub ReplaceTextInChart()
Dim cObj As ChartObject
Dim cht As Chart
Dim ax As Axis
Dim legEnt As LegendEntry
Dim srs As Series
Dim str As String 'this variable will use to hold the various text of the chart.'
Dim strSearch As String
Dim strReplace As String
strSearch = "s" '<-- test that I used, modify as needed.'
strReplace = "##" '<-- test that I used, modify as needed.'
For Each cObj In ActiveSheet.ChartObjects
Set cht = cObj.Chart
With cht
'## Check if the chart has a title, if so, do the replace.'
If .HasTitle Then
str = .ChartTitle.Characters.Text
.ChartTitle = Replace(.ChartTitle, strSearch, strReplace)
End If
'## Check if the chart has a legend, if so, do the replace'
If .HasLegend Then
For Each legEnt In .Legend.LegendEntries
str = legEnt.Format.TextFrame2.TextRange.Characters.Text
legEnt.Format.TextFrame2.TextRange.Characters.Text = _
Replace(str, strSearch, strReplace)
Next
End If
For Each ax In .Axes
'## Check if each Axis has a Title, if so, do the replace'
If ax.HasTitle Then
str = ax.AxisTitle.Characters.Text
ax.AxisTitle.Characters.Text = Replace(str, strSearch, strReplace)
End If
Next
'## For each series, do the replace in series.name'
For Each srs In .SeriesCollection
str = srs.Name
srs.Name = Replace(str, strSearch, strReplace)
Next
End With
Next
End Sub
这篇关于使用vba遍历Excel中的所有文本(在单元格,图表,文本框中)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!