none
Excel Data Connections RRS feed

  • Question

  • So I have like 9 Data/Connections that seem to go along with the 9 worksheets that exist within my Excel spreadsheet.

    How am I supposed to know which Data/Connection is used with each Worksheet? Since under the Data tab and then clicking on Connections they are numbered sequentially.

    Any help and guidance is greatly appreciated.

    Thanks.

    Monday, August 15, 2016 3:50 PM

Answers

  • No direct way but you can loop through....

    Sub GetSheet()
    
        Dim conWb As WorkbookConnection
        Dim sSheet As String
        Dim i As Long
        
        For Each conWb In ActiveWorkbook.Connections
            'One connection may be used in many sheet
            If conWb.Ranges.Count > 0 Then
                For i = 1 To conWb.Ranges.Count
                    sSheet = sSheet & "," & conWb.Ranges.Item(i).Worksheet.Name
                Next i
                sSheet = Mid(sSheet, 2)
                MsgBox """" & conWb.Name & """ is used in sheets: " & sSheet
                        
            Else
                MsgBox """" & conWb.Name & """ is not used in any sheet"
            End If
                
        Next conWb
    
    
    
    End Sub
    

    Check the WorkbookConnection object for more info...


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Monday, August 15, 2016 4:25 PM
    Answerer
  • In case you need in details....

    Keep the workbook active. Press Alt+F11. The VB editor will open

    Click Insert menu at top->Click Module-> Paste the code.

    Press Alt+F11. You will be back at excel window. Then run the macro..(View->Macro->....)


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Tuesday, August 16, 2016 9:25 AM
    Answerer

All replies

  • No direct way but you can loop through....

    Sub GetSheet()
    
        Dim conWb As WorkbookConnection
        Dim sSheet As String
        Dim i As Long
        
        For Each conWb In ActiveWorkbook.Connections
            'One connection may be used in many sheet
            If conWb.Ranges.Count > 0 Then
                For i = 1 To conWb.Ranges.Count
                    sSheet = sSheet & "," & conWb.Ranges.Item(i).Worksheet.Name
                Next i
                sSheet = Mid(sSheet, 2)
                MsgBox """" & conWb.Name & """ is used in sheets: " & sSheet
                        
            Else
                MsgBox """" & conWb.Name & """ is not used in any sheet"
            End If
                
        Next conWb
    
    
    
    End Sub
    

    Check the WorkbookConnection object for more info...


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Monday, August 15, 2016 4:25 PM
    Answerer
  • Do I add this VB syntax as a Macro? Or how?
    Monday, August 15, 2016 6:53 PM
  • Hi ITBobbyP,

    Yes, you could add these code to a new module, and then run from VBA Editor or Macro window.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, August 16, 2016 2:14 AM
  • In case you need in details....

    Keep the workbook active. Press Alt+F11. The VB editor will open

    Click Insert menu at top->Click Module-> Paste the code.

    Press Alt+F11. You will be back at excel window. Then run the macro..(View->Macro->....)


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Tuesday, August 16, 2016 9:25 AM
    Answerer