How to obtain the full-path names of files read with WorkbookConnections

Question

I want to attach the actual files that are the source data for WorkbookConnections in an email. I've looked through many methods and properties of the WorkbookConnections but can't see a way.

This is simple code displaying the names.

Sub ListConnectionNames()
    Dim conn As WorkbookConnection
    For Each conn In ThisWorkbook.Connections
        Debug.Print conn.name
    Next conn
End Sub

The connections are simple text files. They could be on a network drive somewhere but they're not in SQL Server or anything like that--just text files.

Is there a way to get the network path or from a user's local drive? From a support perspective, I'm trying to retrieve the connection source files to include in an email I'm creating in Excel VBA.

Answer 1

If you mean text files connected to the spreadsheet using Data->From Text/CSV, the following code should work for you:

Sub ListSourceFilePaths()
    Dim ws As Worksheet
    Dim conn As WorkbookConnection
    Dim oledbConn As OLEDBConnection
    Dim textConn As TextConnection
    Dim pq As Object
    Dim query As Object
    Dim connectionString As String
    Dim sourcePath As String
    Dim lastRow As Long
    Dim startPos As Integer, endPos As Integer

    ' Create a new worksheet to display results
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets("Source Files")
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Sheets.Add
        ws.Name = "Source Files"
    End If
    On Error GoTo 0

    ' Clear previous data
    ws.Cells.Clear
    ws.Range("A1:B1").Value = Array("Connection Name", "Source File Path")
    
    lastRow = 2
    
    ' Loop through all Power Query queries
    Set pq = ThisWorkbook.Queries
    For Each query In pq
        sourcePath = ""
        Dim queryFormula As String
        queryFormula = query.Formula

        ' Extract source file paths from Power Query (M Code)
        If InStr(1, queryFormula, "File.Contents(") > 0 Then
            sourcePath = ExtractBetween(queryFormula, "File.Contents(""", """)")
        ElseIf InStr(1, queryFormula, "Excel.Workbook(File.Contents(") > 0 Then
            sourcePath = ExtractBetween(queryFormula, "Excel.Workbook(File.Contents(""", """)")
        End If
        
        ' Write to sheet if a path was found
        If sourcePath <> "" Then
            ws.Cells(lastRow, 1).Value = query.Name
            ws.Cells(lastRow, 2).Value = sourcePath
            lastRow = lastRow + 1
        End If
    Next query

    ' Autofit columns for readability
    ws.Columns("A:B").AutoFit
    
    MsgBox "Source file paths extracted to the 'Source Files' sheet.", vbInformation
End Sub

Function ExtractBetween(ByVal fullText As String, ByVal startStr As String, ByVal endStr As String) As String
    Dim startPos As Integer, endPos As Integer
    startPos = InStr(fullText, startStr)
    If startPos = 0 Then Exit Function
    startPos = startPos + Len(startStr)
    endPos = InStr(startPos, fullText, endStr)
    If endPos = 0 Then Exit Function
    ExtractBetween = Mid(fullText, startPos, endPos - startPos)
End Function