Extract Query Text from multiple Excel Files

[Zakkaroo]

I've struggled to find answers to this online, as most of the results are for extracting the DATA, but we're in the midst of a project that will require changes to a large number of queries that have been written in Excel Documents.

What I'd like to be able to do, is run something that can extract all queries (i.e. the text of the query) in an excel workbook, and export it to some other usable format
(I guess a text file, as a single excel cell won't be able to hold some of the longer queries)

Ideally that file would then be saved as something like FILENAME_QUERYNAME.

I'm capable of creating the loop to go through all of the files in a specified folder, but i have no idea if it's possible to grab query text from within an excel document and wouldn't know where to start.

Any help appreciated

 

[Worf]

Does this help?

Sub test2()
Dim c As WorkbookConnection
On Error Resume Next
For Each c In ActiveWorkbook.Connections
    MsgBox c.Name & vbLf & c.ODBCConnection.CommandText
Next
End Sub

 

[Worf]

Would you like the queries in a Word table? Note that a worksheet cell can hold 32K characters.

 

[Zakkaroo]

Really sorry for the slow reply Worf!!

This is exactly the bit of code I was looking for ... and yes, because of the character limit on cells, I was hoping to be able to put the query text into something like a text file - where the name of the file is something like ExcelFilename_QueryName.

But I suppose it would also work to simply show them in a table in a word document, with the filename and query name in separate columns.

Do you happen to have code that would help with that?

I'm struggling to find a way to now get that query text outside of excel ... i can save the workbook as a text file, but that doesn't really help

 

[Zakkaroo]

I'm making progress

Here's what I've got so far.

Public Sub ExportQueryToTxt()


    Dim c As WorkbookConnection
    Dim filePath As String
    
    On Error Resume Next
    For Each c In ActiveWorkbook.Connections
        filePath = "C:\Query Text\" & c.Name & ".txt"
    
        Dim fso As FileSystemObject
        Set fso = New FileSystemObject
        Dim fileStream As TextStream
    
        'file is created and opened for write access
        Set fileStream = fso.CreateTextFile(filePath)
    
        ' Write something to the file
        fileStream.WriteLine c.ODBCConnection.CommandText
    
        fileStream.Close
    
        'If fso.FileExists(filePath) Then
            'do something
        'End If
    
        'Set fileStream = Nothing
        'Set fso = Nothing
    Next


End Sub

 

[Worf]

So tell me if you are stuck. The best way to learn is doing it yourself.