compare and replace visio text using excel VBA

**SammyB** · Mar 22 '07, 03:02 PM

Originally posted by joemo2003

I try to compare the text in excel and visio, if the text match, then replace that visio text with a range of excel text. Do anybody have any experiment with that? any ideal will be a big help.

Thanks

Seems like I've already given you all that you need, so it's not clear what you having trouble with. Is the excel text in a cell or a shape? How do you know which Visio shape to use? How is the Excel Range Text inserted into the Visio shape? In multiple lines?

**joemo2003** · Mar 22 '07, 03:59 PM

Originally posted by SammyB

Seems like I've already given you all that you need, so it's not clear what you having trouble with. Is the excel text in a cell or a shape? How do you know which Visio shape to use? How is the Excel Range Text inserted into the Visio shape? In multiple lines?

In a visio drawing have a textbox with text "ABC",
in excel cells (two cells in one column) have text:
"ABC
DEF".
What I want is after I open the visio drawing from excel VBA, if the "ABC" in visio match "ABC" in excel, then replace the "ABC" in visio with
"ABC
DEF".

Any ideal? all i can think about is the "replace" function, but still cannot figure out a way.

**SammyB** · Mar 22 '07, 05:45 PM

This works for me:

Code:

	Dim c As Range			 ' Actually a single cell
	Dim s As Visio.Shape
	Dim sFind As String		 'String to find
	Dim sReplacement As String 'Replacement string
	Dim i As Integer
	With Range("A1:A2")		 'My location of ABC & DEF
		sFind = .Cells(1)
		For Each c In .Cells
			sReplacement = sReplacement & c.Text & Chr(10)
		Next c
		sReplacement = Left(sReplacement, Len(sReplacement) - 1) ' Remove last CR
	End With
	For Each s In vsApp.ActivePage.Shapes
		If s.Text = sFind Then s.Text = sReplacement
	Next s

**joemo2003** · Mar 22 '07, 06:45 PM

Originally posted by SammyB

This works for me:

Code:

	Dim c As Range			 ' Actually a single cell
	Dim s As Visio.Shape
	Dim sFind As String		 'String to find
	Dim sReplacement As String 'Replacement string
	Dim i As Integer
	With Range("A1:A2")		 'My location of ABC & DEF
		sFind = .Cells(1)
		For Each c In .Cells
			sReplacement = sReplacement & c.Text & Chr(10)
		Next c
		sReplacement = Left(sReplacement, Len(sReplacement) - 1) ' Remove last CR
	End With
	For Each s In vsApp.ActivePage.Shapes
		If s.Text = sFind Then s.Text = sReplacement
	Next s

when i use this code, it didin't change the "ABC" to "ABC DEF" in the visio textbox, but input "ABC DEF" to all other shapes. and even i change the "ABC" to "XXX" in A1, it still input "ABC DEF" to all other shapes. Should we not declare the textbox as shape?

**SammyB** · Mar 22 '07, 07:10 PM

Originally posted by joemo2003

when i use this code, it didin't change the "ABC" to "ABC DEF" in the visio textbox, but input "ABC DEF" to all other shapes. and even i change the "ABC" to "XXX" in A1, it still input "ABC DEF" to all other shapes. Should we not declare the textbox as shape?

>Should we not declare the textbox as shape?
I did: Dim s As Visio.Shape

>input "ABC DEF" to all other shapes
Put a breakpoint at the begining of your code and single step (F8) through it and see why If s.Text = sFind is always true

When steping thru, you can hover with the mouse over a variable and see their values, or you can select a variable and do Debug, Quick Watch on the menu to see the value.

**joemo2003** · Mar 22 '07, 07:29 PM

Originally posted by SammyB

>Should we not declare the textbox as shape?
I did: Dim s As Visio.Shape

>input "ABC DEF" to all other shapes
Put a breakpoint at the begining of your code and single step (F8) through it and see why If s.Text = sFind is always true

When steping thru, you can hover with the mouse over a variable and see their values, or you can select a variable and do Debug, Quick Watch on the menu to see the value.

when i do debug, it said vsApp: "object variable not set", how can i set it as active vsapp?

**SammyB** · Mar 22 '07, 08:08 PM

Code:

	Dim vsApp As Visio.Application
	Dim vsDoc As Visio.Document
	Set vsApp = GetObject(, "Visio.Application")
	Set vsDoc = vsApp.ActiveDocument

**joemo2003** · Mar 22 '07, 10:08 PM

Figure out it is not vsApp problem. I just mess up with the range. It work for a single column. Since the column of text varies everytime, so i set the range to A1 to Z2.
In excel have something like:
A1 B1 ....
ABC XXX
DEF YYY

Do Until (IsEmpty(Cells( 1, j)))
If Not (IsEmpty(Cells( 1, j))) Then
With Range("A1:Z2")
sFind = .Cells(1, j)
For Each c In .Cells
sReplacement = sReplacement & c.Text & Chr(10)
Next c
sReplacement = Left(sReplaceme nt, Len(sReplacemen t) - 1)
For Each ashape In apage.Shapes
If ashape.Text = sFind Then ashape.Text = sReplacement
Next ashape
j = j + 1
End If
Loop

But the all the text change to:
ABC
XXX
.
.
.
.
, how to make each column text separate?

thanks

**SammyB** · Mar 23 '07, 02:04 AM

It hard to tell what went wrong because all of the indents were removed and there is a missing End With, please learn how to use the CODE tags. However, there are two serious problems: (1) you need to initialize sReplacement for each column, and (2) hard to explain, but Cells is actually two different methods: with a sheet, Cells(1,1) is A1, but with a range, Cells(1,1) is the first cell, so Range("C1:C6"). Cells(1,1) is C1. So, I'm sure that my post was confusing. Here's my solution for multiple columns:

Code:

	Dim c As Range			 ' A single cell
	Dim s As Visio.Shape
	Dim sFind As String		 ' String to find
	Dim sReplacement As String ' Replacement string
	Dim i As Integer			' Column index
	For i = 1 To Cells(1, 1).CurrentRegion.Columns.Count	'for each column
		With Range(Cells(1, i), Cells(1, i).End(xlDown))	'with the data in the column
			sFind = .Cells(1)							 'the first cel is what to look for
			sReplacement = ""
			For Each c In .Cells	'concatinate all of the cells in the column
				sReplacement = sReplacement & c.Text & Chr(10)
			Next c
			sReplacement = Left(sReplacement, Len(sReplacement) - 1) ' Remove last CR
		End With
		For Each s In vsApp.ActivePage.Shapes ' for each shape
			If s.Text = sFind Then s.Text = sReplacement	' if it has the text, then replace it
		Next s
	Next i

**joemo2003** · Mar 27 '07, 08:31 PM

cool, it work. Thanks a lot, Samm

Originally posted by SammyB

It hard to tell what went wrong because all of the indents were removed and there is a missing End With, please learn how to use the CODE tags. However, there are two serious problems: (1) you need to initialize sReplacement for each column, and (2) hard to explain, but Cells is actually two different methods: with a sheet, Cells(1,1) is A1, but with a range, Cells(1,1) is the first cell, so Range("C1:C6"). Cells(1,1) is C1. So, I'm sure that my post was confusing. Here's my solution for multiple columns:

Code:

	Dim c As Range			 ' A single cell
	Dim s As Visio.Shape
	Dim sFind As String		 ' String to find
	Dim sReplacement As String ' Replacement string
	Dim i As Integer			' Column index
	For i = 1 To Cells(1, 1).CurrentRegion.Columns.Count	'for each column
		With Range(Cells(1, i), Cells(1, i).End(xlDown))	'with the data in the column
			sFind = .Cells(1)							 'the first cel is what to look for
			sReplacement = ""
			For Each c In .Cells	'concatinate all of the cells in the column
				sReplacement = sReplacement & c.Text & Chr(10)
			Next c
			sReplacement = Left(sReplacement, Len(sReplacement) - 1) ' Remove last CR
		End With
		For Each s In vsApp.ActivePage.Shapes ' for each shape
			If s.Text = sFind Then s.Text = sReplacement	' if it has the text, then replace it
		Next s
	Next i

**SammyB** · Mar 27 '07, 10:46 PM

>cool, it work. Thanks a lot, Samm

(¬_¬)/¯

**joemo2003** · Mar 28 '07, 06:57 PM

Sam, If I want to use the the data on sheet 2 (sheet name: "TEXT"), where I should put the sheet number to? or is there a way to match the sheet name instead the sheet number?
thanks

Code:

  
For i = 1 To Sheet2.Cells(1, 1).CurrentRegion.Columns.Count	'for each column
		With Range(sheet2.Cells(1, i), sheet.Cells(1, i).End(xlDown))	'with the data in the column
			sFind = Cells(1)

Originally posted by SammyB

It hard to tell what went wrong because all of the indents were removed and there is a missing End With, please learn how to use the CODE tags. However, there are two serious problems: (1) you need to initialize sReplacement for each column, and (2) hard to explain, but Cells is actually two different methods: with a sheet, Cells(1,1) is A1, but with a range, Cells(1,1) is the first cell, so Range("C1:C6"). Cells(1,1) is C1. So, I'm sure that my post was confusing. Here's my solution for multiple columns:

Code:

	Dim c As Range			 ' A single cell
	Dim s As Visio.Shape
	Dim sFind As String		 ' String to find
	Dim sReplacement As String ' Replacement string
	Dim i As Integer			' Column index
	For i = 1 To Cells(1, 1).CurrentRegion.Columns.Count	'for each column
		With Range(Cells(1, i), Cells(1, i).End(xlDown))	'with the data in the column
			sFind = .Cells(1)							 'the first cel is what to look for
			sReplacement = ""
			For Each c In .Cells	'concatinate all of the cells in the column
				sReplacement = sReplacement & c.Text & Chr(10)
			Next c
			sReplacement = Left(sReplacement, Len(sReplacement) - 1) ' Remove last CR
		End With
		For Each s In vsApp.ActivePage.Shapes ' for each shape
			If s.Text = sFind Then s.Text = sReplacement	' if it has the text, then replace it
		Next s
	Next i

**SammyB** · Mar 28 '07, 07:46 PM

Originally posted by joemo2003

Sam, If I want to use the the data on sheet 2 (sheet name: "TEXT"), where I should put the sheet number to? or is there a way to match the sheet name instead the sheet number?
thanks

Code:

 
For i = 1 To Sheet2.Cells(1, 1).CurrentRegion.Columns.Count	'for each column
		With Range(sheet2.Cells(1, i), sheet.Cells(1, i).End(xlDown))	'with the data in the column
			sFind = Cells(1)

Replace Sheet2 with Worksheets("TEX T"). Probably easiest to nest With blocks:

Code:

	With Worksheets("TEXT")
		For i = 1 To .Cells(1, 1).CurrentRegion.Columns.Count	'for each column
				With .Range(.Cells(1, i), .Cells(1, i).End(xlDown))	'with the data in the column
					sFind = .Cells(1)
				End With
		Next i
	End With

**joemo2003** · Mar 28 '07, 09:34 PM

If i want specify only two rows in each column in sheet "TEXT" input to visio, and my goal is enter the row numbers i want to two cells in sheet1. and then the sReplacement will only have values I pick. how to make that happen?

compare and replace visio text using excel VBA

compare and replace visio text using excel VBA

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment