Regression Analysis

[bb29829]

OK I am working hard to give you guys a juicy one this morning! So this is what I have. I have a button on a form that runs a query and saves it in excel for me to then go out and run multiple regression analysis on the data that file generated by the query. The query is fully formatting the data so I am only going to excel to actually run the regression. These are my questions:

1) Do I have to go to Excel - is there a way to run MULTIPLE regression in Access? If there is not a way to keep it in Access,
2) How can I then just call the cells from the Excel regression back into a calculation on my form? So the form will just go look at the Excel file for me? The regression data in Excel defaults to [Sheet1] and I always need cells B17, B18, B19, B20, and B21.

Thanks, as always. I want to be just like you guys one day!

 

[sneuberg]

2) How can I then just call the cells from the Excel regression back into a calculation on my form? So the form will just go look at the Excel file for me? The regression data in Excel defaults to [Sheet1] and I always need cells B17, B18, B19, B20, and B21

Maybe you can adapt the code below that came from this site.

Dim xl              As Object
    Dim xlWrkBk         As Object
    Dim xlSht           As Object
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True 'Control whether or not Excel should be visible to
                            'the user or not.
    Set xlWrkBk = xl.Workbooks.Open("C:\mypath\Order1234.XLS")
    Set xlSht = xlWrkBk.Worksheets(1)
    a = xlSht.cells(10, 1) 'RowNo, ColumnNo
    xlWrkBk.Close False 'Close the Workbook without saving any changes
    xl.Quit 'Close Excel
    Set xlSht = Nothing
    Set xlWrkBk = Nothing
    Set xl = Nothing

 

[Isskint]

Of course you could always do the analysis in Access. Either look up the formulae(s) and create custom Functions OR look for purpose built add ons (eg http://www.fmsinc.com/MicrosoftAccess/StatisticalAnalysis.html)

 

[bb29829]

Awesome! Thanks!!!