Extract Query Text from multiple Excel Files
Results 1 to 6 of 6

Thread: Extract Query Text from multiple Excel Files

  1. #1
    Board Regular
    Join Date
    Jul 2009
    Posts
    377
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract Query Text from multiple Excel Files

    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 by Zakkaroo; Aug 15th, 2019 at 06:42 AM.
    MS Excel 2010

  2. #2
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extract Query Text from multiple Excel Files

    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
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  3. #3
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extract Query Text from multiple Excel Files

    Would you like the queries in a Word table? Note that a worksheet cell can hold 32K characters.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  4. #4
    Board Regular
    Join Date
    Jul 2009
    Posts
    377
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Query Text from multiple Excel Files

    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 by Zakkaroo; Aug 28th, 2019 at 04:00 AM.
    MS Excel 2010

  5. #5
    Board Regular
    Join Date
    Jul 2009
    Posts
    377
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Query Text from multiple Excel Files

    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
    MS Excel 2010

  6. #6
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extract Query Text from multiple Excel Files

    So tell me if you are stuck. The best way to learn is doing it yourself.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •