Extract Query Text from multiple Excel Files

Zakkaroo

Active Member
Joined
Jul 6, 2009
Messages
382
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
 
Last edited:

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,756
Does this help?

Code:
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

Well-known Member
Joined
Oct 30, 2011
Messages
3,756
Would you like the queries in a Word table? Note that a worksheet cell can hold 32K characters.
 

Zakkaroo

Active Member
Joined
Jul 6, 2009
Messages
382
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 :p
 
Last edited:

Zakkaroo

Active Member
Joined
Jul 6, 2009
Messages
382
I'm making progress :p

Here's what I've got so far.

Code:
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

Well-known Member
Joined
Oct 30, 2011
Messages
3,756
So tell me if you are stuck. The best way to learn is doing it yourself.
 

Forum statistics

Threads
1,077,795
Messages
5,336,374
Members
399,078
Latest member
johnk94

Some videos you may like

This Week's Hot Topics

Top