Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Rename Connection Name (Data Connection) 2

Status
Not open for further replies.

arvarr

MIS
Nov 24, 2009
260
AU
Hi
I have a workbook with 10+ connections.
Some connections are not being used.
These connections are used to produced an OLAP-type PVT.

Each connection having their own connection name.
Naming convention can be random.

I'm looking for a vba code to loop through all connections in the workbook
and
rename them to Connection 1, Connection 2, Connection 3, etc...

Many Thanks in advance for your assistance.

Regards,
arv
 
For instance:
[pre]Sub RenameConnections()
Dim conn As WorkbookConnection
For Each conn In ThisWorkbook.Connections
i = 1
With conn
.Description = "Old name: " & .Name
.Name = "Connection " & i
i = i + 1
End With
Next
End Sub[/pre]

combo
 
Hi combo
Thanks for the code.

It changes one of the connection name then came up with an error.
Run time error 5. Invalid procedure call or argument.

It appears that it is trying to rename the same connection when it loops.
It does not seem to have a sequence from top to bottom or bottom to top.

Regards,
arv
 
Thanks strongm.
Issue resolved.

Many Thanks to both Combo and strongm!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top