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.

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.

• excel
• vba

Share Improve this question Follow edited Jan 29 at 6:37 braX 11.8k55 gold badges2222 silver badges3737 bronze badges asked Jan 29 at 6:36 DavidsonDavidson 1,10833 gold badges2222 silver badges3737 bronze badges

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 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

Can you try this?

Sub Test()
    Dim i As Integer, j As Integer
    
    For i = 1 To Sheets.Count
        For j = 1 To Worksheets(i).QueryTables.Count
            MsgBox Worksheets(i).QueryTables(j).Connection
        Next
    Next
End Sub

Yes, you can retrieve the full path of the source text files from WorkbookConnection objects by accessing the TextConnection or ODBCConnection properties.
Since your connections are simple text files, you should be able to use TextConnection.SourceDataFile.

Try this VBA code to extract the full path of the source text files: VBA Code:

Sub ListConnectionSourceFiles()
    Dim conn As WorkbookConnection
    Dim srcPath As String
    
    For Each conn In ThisWorkbook.Connections
        If Not conn.TextConnection Is Nothing Then
            srcPath = conn.TextConnection.SourceDataFile
            Debug.Print conn.Name & " - " & srcPath
        ElseIf Not conn.ODBCConnection Is Nothing Then
            srcPath = conn.ODBCConnection.SourceConnectionFile
            Debug.Print conn.Name & " - " & srcPath
        Else
            Debug.Print conn.Name & " - No source file found"
        End If
    Next conn
End Sub

Explanation:

TextConnection.SourceDataFile: Retrieves the path of text-based connections. ODBCConnection.SourceConnectionFile: Retrieves the path if it's an ODBC connection. If neither property exists, it prints "No source file found."

Next Steps:

Once you have the paths, you can attach the files to your email.