Copy a matrix into excel

SupportCommunity Automation and VBA Copy a matrix into excel

Ansicht von 3 Beiträgen - 1 bis 3 (von insgesamt 3)
  • Autor
    Beiträge
  • #12899
    Peter SeitzPeter Seitz
    Mitglied

    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

    #8559
    Peter SeitzPeter Seitz
    Mitglied

    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

    #9422
    Bernhard KantzBernhard Kantz
    Moderator

    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.

Ansicht von 3 Beiträgen - 1 bis 3 (von insgesamt 3)
  • Du musst angemeldet sein, um auf dieses Thema antworten zu können.