0

I wrote some VBA that loops through the PowerQuery queries in a workbook and exports the M formula to separate files, and I had the foolish idea of writing an excel addin to do this instead of pasting it in all the workbooks that might need it. Problem is, the workbook's queries collection doesn't appear to be available in interop.excel, and the QueryTables collection won't return the M formula. Barring programmatically adding the VBA below and running it, does anyone know a way I can get to the workbook's queries collection?

        For Each qry In ThisWorkbook.Queries
            filepath = directory & qry.Name & ".txt"
            Set filestream = fso.CreateTextFile(filepath, True)
            arr = Split(qry.Formula, Chr$(10))
            For i = LBound(arr, 1) To UBound(arr, 1)
                filestream.WriteLine (arr(i))
            Next i
            filestream.Close
        Next qry

Edit: For some clarification here's what the c# looks like (using Excel = Microsoft.Office.Interop.Excel). Issue is I can't do something like foreach(Excel.WorkbookQuery qry in wkbk.Queries) {} here

        void Application_WorkbookAfterSave(Excel.Workbook wkbk, bool suc)
        {
            string wkbk_name = Path.GetFileNameWithoutExtension(wkbk.Name);
            string wkbk_path = wkbk.Path + "\\" + wkbk_name;
            DialogResult save_res = MessageBox.Show("Do you want to save scripts?", wkbk_name, MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            
            if(save_res == DialogResult.Yes)
            {
                if(!Directory.Exists(wkbk_path))
                {
                    Directory.CreateDirectory(wkbk_path);
                }   
            }
        }
BRothman
  • 33
  • 5
  • `ThisWorkbook` could just as easily be `ActiveWorkbook`, and you can write your add-in as an xlam... – Tim Williams Oct 14 '20 at 23:07
  • The issue isn't identifying the workbook object, but the queries collection of the workbook. VBA allows for .queries while interop.excel does not appear to is the issue. – BRothman Oct 15 '20 at 01:19
  • I was only suggesting you consider using VBA for your add-in instead of C# if that doesn't offer all of the interfaces you need – Tim Williams Oct 15 '20 at 01:54
  • ..or maybe try using late binding and see if you can access the Queries property: https://www.xspdf.com/questions/41914.shtml – Tim Williams Oct 15 '20 at 01:59

0 Answers0