Automate Excel Export of Predicted Values - Statistica Visual Basic - Statistica - Dell Community

Automate Excel Export of Predicted Values

Automate Excel Export of Predicted Values

This question is not answered

I want to use VBA to automate the export of predicted values to an Excel workbook.  I recorded the process in VBA and the recorded macro leaves out the step wherein the predicted values dialog gets activated prior to the export.  I am not sure of the right syntax to do this.

Here is what gets recorded:

    ' GLZ -- Results: Analysis 1: Test1
    Dim oAD3 As STAGLZ.GLZResults
    Set oAD3 = newanalysis2.Dialog
    oAD3.PLevelForHighlighting = 0.05
    oAD3.PForConfidenceLimits = 95
    oAD3.NumberOfCategoriesForHistograms = 12
    oAD3.SetCovariatesAtUserDefinedValues = "none"
    newanalysis2.Dialog.ResultsVariables = "1"
    newanalysis2.Dialog.SavePredicted.Visible = True
    ' Save As Excel File
    ActiveDataSet.ExportXLSEx(TheFile,1,1090,1,7,False,False,False,False,False)

When I recorded the macro the predicted values dialog was properly exported.  If I run the macro, then it actually exports the original dataset since, I guess, that is what is still active.  If I pause the macro and select the predicted values dialog (i.e manually activate it) then this also works.  I am hoping to find out what the VBA command is that would activate the dialog before exporting, or to otherwise export the dialog.  I hope this makes sense, and thanks for  the help.

Rick

All Replies
  • Hi Rick !

    Try adding the 3 blue lines

    ' GLZ -- Results: Analysis 1: Test1
        Dim oAD3 As STAGLZ.GLZResults
        Set oAD3 = newanalysis2.Dialog
        oAD3.PLevelForHighlighting = 0.05
        oAD3.PForConfidenceLimits = 95
        oAD3.NumberOfCategoriesForHistograms = 12
        oAD3.SetCovariatesAtUserDefinedValues = "none"
        newanalysis2.Dialog.ResultsVariables = "1"
        newanalysis2.Dialog.SavePredicted.Visible = True

         Dim ss as Spreadsheet
        Set ss = oAD3.PredictedValues
        ss.Activate


        ' Save As Excel File
        ActiveDataSet.ExportXLSEx(TheFile,1,1090,1,7,False,False,False,False,False)

  • Hi Rick !

    Try adding the lines highlighted in blue

    GLZ -- Results: Analysis 1: Test1
        Dim oAD3 As STAGLZ.GLZResults
        Set oAD3 = newanalysis2.Dialog
        oAD3.PLevelForHighlighting = 0.05
        oAD3.PForConfidenceLimits = 95
        oAD3.NumberOfCategoriesForHistograms = 12
        oAD3.SetCovariatesAtUserDefinedValues = "none"
        newanalysis2.Dialog.ResultsVariables = "1"
        newanalysis2.Dialog.SavePredicted.Visible = True

    Dim ss As Spreadsheet
     Set ss = oAD3.PredictedValues

     ss.Activate


        ' Save As Excel File
        ActiveDataSet.ExportXLSEx(TheFile,1,1090,1,7,False,False,False,False,False)

  • Thank you very much for the reply.  

    However, this does not work.  I get a "Script Error" on the "Set SS" statement.

    The error is listed as (10080)  Type Mismatch.

    any suggestions?

    ...Rick

  • Hi Rick,

    Try

        oAD3.PredictedValues(1)

    instead of

     oAD3.PredictedValues

  • Thanks for the response.  This change does now no longer produce an error, but the output file is not the predicted values spreadsheet, but rather the original spreadsheet that I used to do the modeling.  In other words, the Export function outputs to the original ActiveDataSet... activating the spreadsheet does not seem to change this.  Any ideas?

    Here is what I am actually running

    Sub Main
        Dim AO As AnalysisOutput
        Dim AWB As Workbook
        Dim Index As Integer

        Dim S1 As Spreadsheet
        Set S1 = ActiveDataSet

        Dim newanalysis2 As Analysis
        Dim oStaDocs2 As StaDocuments
        Dim oAD1 As STAGLZ.GLZStartup

    For Index = 1 To 25

      TheFile = "C:\Temp\StatRun" & Format(Index,"0") & ".xlsx"

    --- Code to define model: this all works fine

    newanalysis2.Run

        ' GLZ General custom design: RSL3

        Dim oAD2 As STAGLZ.GLZSpecifications
        Set oAD2 = newanalysis2.Dialog

        ' GLZ -- Results: Analysis 1: RSL3
        Dim oAD3 As STAGLZ.GLZResults
        Set oAD3 = newanalysis2.Dialog

        newanalysis2.Dialog.ResultsVariables = "1"
        newanalysis2.Dialog.SavePredicted.Visible = True

    Dim SS As Spreadsheet
    Set SS = oAD3.PredictedValues(1)
     SS.Activate

        ' Save As Excel File
        ActiveDataSet.ExportXLSEx(TheFile,1,1090,1,7,False,False,False,False,False)
        newanalysis2.Close
        Set newanalysis2 = Nothing

    SS.Close
    Set SS = Nothing
    Next Index
    End Sub

  • Rick,

    Sorry. I left a  small detail when I sent the code last time. Try passing the spreadsheet object instead of ActiveDataSet. Its a small change but it will get the you the results you want. Tthe code change is highlighted in blue:

    Sub Main
        Dim AO As AnalysisOutput
        Dim AWB As Workbook
        Dim Index As Integer

        Dim S1 As Spreadsheet
        Set S1 = ActiveDataSet

        Dim newanalysis2 As Analysis
        Dim oStaDocs2 As StaDocuments
        Dim oAD1 As STAGLZ.GLZStartup

    For Index = 1 To 25

      TheFile = "C:\Temp\StatRun" & Format(Index,"0") & ".xlsx"

    --- Code to define model: this all works fine

    newanalysis2.Run

        ' GLZ General custom design: RSL3

        Dim oAD2 As STAGLZ.GLZSpecifications
        Set oAD2 = newanalysis2.Dialog

        ' GLZ -- Results: Analysis 1: RSL3
        Dim oAD3 As STAGLZ.GLZResults
        Set oAD3 = newanalysis2.Dialog

        newanalysis2.Dialog.ResultsVariables = "1"
        newanalysis2.Dialog.SavePredicted.Visible = True

    Dim SS As Spreadsheet
    Set SS = oAD3.PredictedValues(1)
     SS.Activate

        ' Save As Excel File
        SS.ExportXLSEx(TheFile,1,1090,1,7,False,False,False,False,False)
        newanalysis2.Close
        Set newanalysis2 = Nothing

    SS.Close
    Set SS = Nothing
    Next Index
    End Sub

  • yeah, it had occurred to me to try that.  I get the following error message

    (10090) ActiveX Automation Error. 'Unspecified Error'

    This error does occur when executing the ExportXLSEx command.

    Thanks for engaging on this.

    Rick