Extract Query Text from multiple Excel Files

Zakkaroo

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

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
Would you like the queries in a Word table? Note that a worksheet cell can hold 32K characters.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top