Data Connections

[KevBarry]

Having used this forum many times over the years for tips I find myself posting for the first time with a query, so here goes

I am using data connection to retrieve data from dozens of workbooks, each workbook has 17 worksheets, Apr through Dec, total of Q1, Q2, Q3, Q4 and Grand Total

To retrieve the required data, lets say for Q2, I have to open the the connection properties, select the individual workbook, go to definition and then enter Q2 in the command text box.

My question is; is there a way of globally changing this, at present I am linking up to 60 workbooks, but this could rise to well over a hundred, I dont necessarily want to have to manually change every sheet every time I want a different period reported

I had hoped that I could multiple select the workbooks but when I do this the properties box becomes unavailable !!

Thanks for any help that you can provide, I have tried googling this but with no success
Kev

 

[jkpieterse]

I'm not sure what kind of data connections we're talking about here? Can you perhaps post a couple of screenshots?

 

[John_w]

You could use this macro as a starting point. It displays the current Workbook connections (OLEDB or ODBC), and prompts for the new Command Text for each one. Test it on a copy of your data connections workbook.

Public Sub Change_Workbook_Connections()

    Dim wbConnection As WorkbookConnection
    Dim SheetNameInput As String
    
    For Each wbConnection In ActiveWorkbook.Connections
        
        If Not wbConnection.OLEDBConnection Is Nothing Then
        
            SheetNameInput = InputBox("Connection name = " & wbConnection.Name & vbCrLf & _
                   "Connection string = " & Left(wbConnection.OLEDBConnection.Connection, 100) & vbCrLf & _
                   "Command Text = " & wbConnection.OLEDBConnection.CommandText & vbCrLf & vbCrLf & _
                   "Enter new sheet name for Command Text", Title:="OLEDB Connection - Current Properties")
            If SheetNameInput <> "" Then
                wbConnection.OLEDBConnection.CommandText = SheetNameInput
                wbConnection.Refresh
            End If
            
        ElseIf Not wbConnection.ODBCConnection Is Nothing Then
        
            SheetNameInput = InputBox("Connection name = " & wbConnection.Name & vbCrLf & _
                   "Connection string = " & Left(wbConnection.ODBCConnection.Connection, 100) & vbCrLf & _
                   "Command Text = " & wbConnection.ODBCConnection.CommandText & vbCrLf & vbCrLf & _
                   "Enter new sheet name for Command Text", Title:="ODBC Connection - Current Properties")
            If SheetNameInput <> "" Then
                wbConnection.ODBCConnection.CommandText = SheetNameInput
                wbConnection.Refresh
            End If
            
        End If
        
    Next
    
End Sub

 

[KevBarry]

WOW John !!!

I have to admit I have no idea what you have done but it works, very much appreciated.

The macro asks me to confirm for each linked connection but that is way quicker than going into the definitions each time

There are some seriously clever people out there of which you are one my friend, thanks again

 

[KevBarry]

Sorted now, but thanks for the reply Jan

 

[John_w]

It was just a starting point, glad you got it working. Just move the InputBox outside the loop and change the code like this:

Public Sub Change_Workbook_Connections2()

    Dim wbConnection As WorkbookConnection
    Dim SheetNameInput As String
    
    SheetNameInput = InputBox("Enter new sheet name for Command Text")
    If SheetNameInput = "" Then Exit Sub
   
    For Each wbConnection In ActiveWorkbook.Connections
        If Not wbConnection.OLEDBConnection Is Nothing Then
            wbConnection.OLEDBConnection.CommandText = SheetNameInput
            wbConnection.Refresh
        ElseIf Not wbConnection.ODBCConnection Is Nothing Then
            wbConnection.ODBCConnection.CommandText = SheetNameInput
            wbConnection.Refresh
        End If
    Next
    
End Sub

Your workbook may contain only 1 type of connection (OLEDB or ODBC), in which case one of the If/Else parts is redundant.

 

[KevBarry]

Genius ! Cheers John