Added a file dialog in order to select an Excel template file

[didierterrien]

Hello Marco,
I tried to submit a pull request but I couldn't succeed. So I add the new version of RunExcelProcess down below.

I added a file dialog in order to select an Excel template file.
If cancel button is pressed, an empty file is open (previous behaviour).
The template file can be created from an empty file. Open connection properties and check these 2 options :

• in first tab : refresh data when the file is open
• in second tab : always use the connection file

When the template is open from external tools, data is refreshed automatically without any message.
Thanks again for this fantastic tool
Didier Terrien

`
private void RunExcelProcess(string serverName, string databaseName, string cubeName)
{
// Create ODC file
OdcHelper.CreateOdcFile(serverName, databaseName, cubeName);
var ODCfileName = OdcHelper.OdcFilePath();

        // Select an Excel template. If Cancel button is pressed, an empty Excel file is opened
        string selectedFileName = "";
        OpenFileDialog openFileDialog = new OpenFileDialog
        {
            Title = "Select an Excel template file",
            //InitialDirectory = Directory.GetParent(Power_BI_file).ToString(),     //would be nice to start searching from the Power BI file folder
            Filter = "xlsx files (*.xlsx)|*.xlsx|xlsm files (*.xlsm)|*.xlsm|All files (*.*)|*.*",
            FilterIndex = 2,
            Multiselect = false,
            RestoreDirectory = true
        };
        if (openFileDialog.ShowDialog() == true)
        {
            // If ok is pressed, get the path of selected file
            selectedFileName = openFileDialog.FileName;
        }
        else
        {
            // If Cancel is pressed, use the ODC file
            selectedFileName = ODCfileName;
        }

        var p = new Process
        {
            StartInfo = new ProcessStartInfo(selectedFileName)
            {
                UseShellExecute = true
            }
        };
        p.Start();
    }

`

[marcosqlbi]

Thanks for the contribution! I would keep it on hold for a future release, the priority is now fixing issues that are appearing when the configuration is not good (missing Excel, missing OLAP drivers, ...).
For this step (which is part of the v2 roadmap) we also need to figure out a consistent user experience. However, you are free to build your own version and experiment!

[marcosqlbi]

@didierterrien I have a question - when you open an existing file with your code, do you change the connection string? It doesn't seem you do that, but maybe I'm missing something.
The build I'm testing now uses COM Interop to control Excel directly. Once tested well (thanks to telemetry we discover a large number of issues...) we'll reach a point where we replace the connection string of an existing Excel file...

[didierterrien]

Hello @marcosqlbi,
No I don't change the connection string. I just check two checkboxes in connection settings :

• in first tab : refresh data when opening the file
• in second tab : always use connection file
  This way, the connection string is refreshed from the ODC connection file each time the Excel file is opened. As your tool just updated the ODC file with the Power BI desktop port, it works like a charm.
  The only problem I see may happen if two Power BI reports open two Excel files. As they will share the same ODC file, there will be a conflict. If the OCD file could have the same name than the pbix and could be located in the same folder, there will be no conflict.
  Don't you think replacing the connection string of an existing Excel file can be considered as an attack by some antiviruses ?

[marcosqlbi]

Thanks for the explanation!
I think to proceed as planned - if the approach I'm including n v1 works (using COM Interop) we'll get rid of the ODC file and we'll include the connection straight in the Excel file. This should remove the concurrency issue and enable the change of the connection string while we open the file. However, this will be a v3 feature.

[didierterrien]

Hello Marco,

Do you agree if I reuse a part of your code (ODC_Helper) in my external tool ? Of course I will add credits to you.

https://thebipower.fr/index.php/power-bi-sidetools/

Best regards

[marcosqlbi]

No problems, it is allowed by the MIT license, I suggest you check it because it has an impact on the license of your tool.
https://github.com/sql-bi/AnalyzeInExcel/blob/master/LICENSE

[didierterrien]

Thanks a lot Marco,
I added the license in the code. I will refer to the web page, to the license and to the GitHub repository of Analyze in Excel in the credits.
Have a nice weekend

[marcosqlbi]

Great - thanks!