1

I am using ADOMD 6.1 in an Excel 2010 Environment to send custom MDX Queries to an Analysis Services 2008 R2 Cube and to display the results in the excel workbook. This works fine so far, however the Excel instance is freezing while it waits for the Analysis Services Server to return the query result, and some of the queries may probably run for several minutes.

I would probably use threading to outsource the Connection into another thread, but according to some other questions asked around here, this is not possible in VBA itself - and we do have the constraint to not use DLLs, as we would need to distribute the resulting Excel with its macros to some end users and installing DLLs ist not an option. Using built-in Excel-Pivot-tables is not an option either as we rely on MDX-functions like intersect and exists.

Is there a non-blocking way to send querys to an Analysis Services Server using ADOMD in VBA without using external DLLs?

Dim ObjConnection As New ADODB.Connection
Dim rs As New ADODB.Recordset
ObjConnection.Open strCon

rs.Open strMDX, ObjConnection

Do While Not rs.EOF
    For i = 0 To rs.Fields.Count - 1
        Cells(i, 1).Value = rs.Fields(i).Name
        Cells(i, 2).Value = rs.Fields(i).Value
    Next
    rs.MoveNext
Loop

Thanks a lot!

Thomas Tschernich
  • 1,264
  • 15
  • 29

1 Answers1

1

Yes, it can be done. You'll need to start another instance of the Excel application (in the background, so you can hide it) and get that instance to run the macro for you.

The key steps are

    Set newexcel = New Application
    Set newworkbook = newexcel.Workbook.Open("Path goes here")
    ' newworkbook is of Workbook data type

    Dim MacroName as String
    ArgumentMacroName = "'" & SomeStringArgument & "'!ModuleName.YourSubName"

    Output = newexcel.Run(MacroName, ThisWorkbook.Name)  'Output is of the returned data type

In the new workbook, you can include the Macro you want to run and pass on this string parameter ByVal.

Since the application is hidden, it'll be completely transparent to the user and the values can be returned while other activity proceeds.

The code required is discussed on http://www.mrexcel.com/forum/excel-questions/570562-visual-basic-applications-open-another-instant-excel-run-macro.html

hnk
  • 2,216
  • 1
  • 13
  • 18