Hi to all,
I'm using Excel and VBA as reporting tool for AS.
The process of filling the Excel worksheets with headers and data is very
slow (about ten seconds for 10 cols and 20 rows). I use the
Sheets(index).cells(indRow, indCol)= value method.
Is there a fastest way to perform this operation.

Thanks in advance
Massi

You can speed it up significantly by populating an array, then placing the
results onto the worksheet as a single operation.

tom @ the domain below
www.tomchester.net


"Massi" <massi (AT) tiscali (DOT) it> wrote

Can I also format the cells in the same single step?

Thanks Tom

"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> ha scritto nel messaggio
news8fgb.155$gG3.9981 (AT) news (DOT) uswest.net...

Only if the same format is being applied to all cells. But even if you have
to iterate the cells to format them, you should still get a significant
speed improvement by using the array approach.

tom @ the domain below
www.tomchester.net


"Massi" <massi (AT) tiscali (DOT) it> wrote

If you don't need any of the formatting information provided by the ADOMD
Cellset you may consider just using an ADODB Recordset and use the
CopyFromRecordset Method.

For example:

Sub DumpToWorksheet()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim iTotalColumns, iColumnCNT As Integer
Dim oTargetSheet As Object
Dim sTempRowHeader As String
Dim sRowHeader() As String
Dim sMDX As String

'***Set Connection Information for the OLAP Server
cn.Provider = "MSOLAP.2"
cn.Properties("Data Source").Value = "localhost"
cn.Properties("Initial Catalog").Value = "FoodMart 2000"

'***Open the Connection to the OLAP Server
cn.Open

sMDX = "select {[Measures].[Unit Sales]} on columns,
order(except([Promotion Media].[Media Type].members,{[Promotion
Media].[Media Type].[No Media]}),[Measures].[Unit Sales],DESC) on rows from
Sales"

'***Submit the MDX query
rs.Open sMDX, cn

'***Determine the total number of columns in the MDX query
iTotalColumns = rs.Fields.Count

'***Establish an array for the Row Header
ReDim sRowHeader(iTotalColumns + 1) As String

'***Iterate through the columns to get the headers and populate the
array
For iColumnCNT = 0 To iTotalColumns - 1
sTempRowHeader = rs.Fields(iColumnCNT).Name '***Grab the column
heading
sTempRowHeader = Replace(sTempRowHeader, "[", "") '***Replace open
square bracket with nothing
sTempRowHeader = Replace(sTempRowHeader, "]", "") '***Replace close
square bracket with nothing
sTempRowHeader = Replace(sTempRowHeader, ".", " ") '***Replace a
period with a space
sTempRowHeader = Replace(sTempRowHeader, "MEMBER_CAPTION", "")
'***Replace the phrase MEMBER_CAPTION with nothing

sRowHeader(iColumnCNT + 1) = sTempRowHeader
Next iColumnCNT

'***Setting the sheet where the data will be dumped
Set oTargetSheet = Application.ThisWorkbook.Worksheets("Sheet1")

'***Clear the previous data from the worksheet
oTargetSheet.Cells.ClearContents

'***Populate the worksheet with the row headers (starting in cell A1)
oTargetSheet.Range(Cells(1, 1), Cells(1, iTotalColumns + 1)) =
sRowHeader

'***If were are at the end of file there isn't any data.
If rs.EOF = True Then
oTargetSheet.Cells(2, 1) = "No Data to Fetch."
Else
'***Go to the first row of the recordset returned by the MDX query
rs.MoveFirst
'***Populate the worksheet with the data from the recordset
(starting in cell B1)
oTargetSheet.Cells(2, 1).CopyFromRecordset rs
End If

'***Clean-up
Erase sRowHeader
rs.Close
cn.Close
End Sub

HTH,

Barry
"Massi" <massi (AT) tiscali (DOT) it> wrote