Copy a matrix into excel

[Peter Seitz]

Hello,

i have a 29×29 dimensional matrix which I want to include in an excel sheet.
For example a matrix build up within in a formula like this:

Dim Output = ? # 29 # 29  // Dim a 29x29 matrix
Output[0,28][0,28] = -1	  // Fill all cells with -1

[ Output]

I want this Output to be copied into an excel sheet. I used following code:

Option Explicit
 
Const sExcelSheet As String = "P:\TestFile.xlsx"

Sub DataIntoExcel()

'access data from FlexPro
    Dim oFolder As Folder
    Set oFolder = ActiveDatabase.RootFolder
    Dim matrix
    Set matrix = oFolder.Object("Formel", fpObjectTypeFormula).Value

' open Excel sheet
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")

    With oExcel
        .Visible = True
        .Workbooks.Open Filename:=sExcelSheet, ReadOnly:=False
        With .Workbooks(1)
            Dim oSheet As Object
            Set oSheet = .Worksheets(1)
            
            oSheet.Range("C3:AE31") = matrix
        End With
    End With
    Set oExcel = Nothing
End Sub

The matrix is supposed to be placed starting at cell C3 or placed in the area from C3 to AE31. I keep getting runtime errors though.

Can someone tell me what i did wrong. Thanks in advance.

kind regards,
Peter

[Peter Seitz]

Hello,

i have a 29×29 dimensional matrix which I want to include in an excel sheet.
For example a matrix build up within in a formula like this:

Dim Output = ? # 29 # 29  // Dim a 29x29 matrix
Output[0,28][0,28] = -1	  // Fill all cells with -1

[
 Output]

I want this Output to be copied into an excel sheet. I used following code:

Option Explicit
 
Const sExcelSheet As String = "P:\TestFile.xlsx"

Sub DataIntoExcel()

'access data from FlexPro
    Dim oFolder As Folder
    Set oFolder = ActiveDatabase.RootFolder
    Dim matrix
    Set matrix = oFolder.Object("Formel", fpObjectTypeFormula).Value

' open Excel sheet
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")

    With oExcel
        .Visible = True
        .Workbooks.Open Filename:=sExcelSheet, ReadOnly:=False
        With .Workbooks(1)
            Dim oSheet As Object
            Set oSheet = .Worksheets(1)
            
            oSheet.Range("C3:AE31") = matrix
        End With
    End With
    Set oExcel = Nothing
End Sub

The matrix is supposed to be placed starting at cell C3 or placed in the area from C3 to AE31. I keep getting runtime errors though.

Can someone tell me what i did wrong. Thanks in advance.

kind regards,
Peter

[Bernhard Kantz]

Your code should work if you assign the value of the formula evaluation instead of the value object itself to your matrix variable.

Dim fml As Formula
Set fml = ActiveDatabase.RootFolder("Formel", fpObjectTypeFormula)
fml.Update
Dim matrix
matrix = fml.Value

Note the update of the formula before using its value.

Hint: To exchange rows and columns use Application.WorksheetFunction.Transpose(matrix) in the assignment to the range.

[Author]

Posts