2 Replies Latest reply on Oct 11, 2013 8:26 AM by Eric Tobias

    Automating UnpivotData

    Eric Tobias

      I am writing a macro to transform hundreds of CSV files into a format more suitable for analyzing in Tableau. I would like to include the Tableau "Reshape data" function to do this. I can run the function with the following command in VBA:

       

      • Application.Run "UnpivotData"

       

      However, that raises a dialog box that requires me to press the "OK" button. Is there a parameter that can be passed to the UnpivotData command that will automatically run it without an interactive dialog? I've set the first data cell by the time I get to this point, so the default values in the dialog are correct and all I have to do is press "OK" for the transform to occur. I've verified this lots of times, so I know my previous VBA macro code is properly selecting cells so that only the "OK" button press is required for things to work. All I need now, is to run UnpivotData without a dialog.

        • 1. Re: Automating UnpivotData
          Alex Kerin

          It would be really nice if Tab just released the code as an xlsm as well so we could deconstruct it

          • 2. Re: Automating UnpivotData
            Eric Tobias

            With the help of an Excel VBA expert who did some of the ground work, I modified his approach to simplify it. The following statement can replace the "Application.Run 'UnpivotData'" command:

             

            • Run "tsiUnpivot", ActiveSheet, ActiveCell, ActiveSheet.Range(ActiveCell, Cells(ActiveCell.CurrentRegion.Rows(ActiveCell.CurrentRegion.Rows.Count).Row, ActiveCell.CurrentRegion.Columns(ActiveCell.CurrentRegion.Columns.Count).Column)), ActiveCell.CurrentRegion, "", 0, False

             

            This replicates the selection of the current cell as the first data cell and leaving the optional column titles empty, which is the default values in the dialog itself.

             

            Feel free to play with this, or hit me up with questions. It took a few hours of investigation and trial and effort to succeed. Hopefully it saves other people some time!

            1 of 1 people found this helpful