copy data from Flexpro to Excel via VBA ?

SupportCommunity Automation and VBA copy data from Flexpro to Excel via VBA ?

Ansicht von 3 Beiträgen - 1 bis 3 (von insgesamt 3)
  • Autor
    Beiträge
  • #12775
    MAZOYER ThomasMAZOYER Thomas
    Teilnehmer

    Hello,

    i would like to copy a data from Flexpro to a specific cell on Excel 2007. How can I do ?

    I try this code:

    ActiveDatabase.TaskWindows(fpTaskWindowObjectList).Activate
    ActiveDatabase.Selection.SelectObject “Data”
    ActiveDatabase.Selection.Copy

    but there is nothing in the clipbord and I have the error :
    “Run-time error 1004”.

    I create an object Excel and open it:

    Set oAppExcel = CreateObject(“Excel.Application”)
    sFichier = InputBox(“Sélectionner le fichier Excel à renseigner”, File, “H:\macro\toto.xlsx”)
    oAppExcel.Workbooks.Open (sFichier)

    but it doesn’t work.

    could someone help me ?

    Thanks,
    ToM

    #8441
    MAZOYER ThomasMAZOYER Thomas
    Teilnehmer

    Hello,

    i would like to copy a data from Flexpro to a specific cell on Excel 2007. How can I do ?

    I try this code:

    ActiveDatabase.TaskWindows(fpTaskWindowObjectList).Activate
    ActiveDatabase.Selection.SelectObject “Data”
    ActiveDatabase.Selection.Copy

    but there is nothing in the clipbord and I have the error :
    “Run-time error 1004”.

    I create an object Excel and open it:

    Set oAppExcel = CreateObject(“Excel.Application”)
    sFichier = InputBox(“Sélectionner le fichier Excel à renseigner”, File, “H:\macro\toto.xlsx”)
    oAppExcel.Workbooks.Open (sFichier)

    but it doesn’t work.

    could someone help me ?

    Thanks,
    ToM

    #9248
    Bernhard KantzBernhard Kantz
    Moderator

    With the lines

    
    ActiveDatabase.TaskWindows(fpTaskWindowObjectList).Activate
    ActiveDatabase.Selection.SelectObject "Data"
    ActiveDatabase.Selection.Copy
    

    you activate the object list, add the object named “Data” to an existing selection and copy them all as objects. But Excel can’t insert whole FlexPro objects, what leads to an error.

    Using

    
    Set oAppExcel = CreateObject("Excel.Application")
    sFichier = InputBox("Sélectionner le fichier Excel à renseigner", File, "H:\macro\toto.xlsx")
    oAppExcel.Workbooks.Open (sFichier)
    

    works fine, but one has to set the Visible property of oAppExcel to see something happening.

    One possible solution for writing FlexPro values to Excel cells would be the following: We assume a folder “DataFolder” containing two datasets, one named “Scalar” containing a single value, the other named “Vector” containing 25 values. To write them into some cells of an excisting Excel sheet, one chan use a VBA script like the following:

    
    Option Explicit
    
    Const sDefaultExcelSheet As String = "C:\Users\Public\Documents\Test.xlsx"
    
    Sub DataIntoExcel()
        
        ' access data from FlexPro
        Dim oFolder As Folder
        Set oFolder = ActiveDatabase.RootFolder.Object("DataFolder", fpObjectTypeFolder)
        
        Dim vScalar
        vScalar = oFolder.Object("Scalar", fpObjectTypeDataSet).Value
        
        Dim vVector
        vVector = oFolder.Object("Vector", fpObjectTypeDataSet).Value
        
        ' open Excel sheet
        Dim oExcel As Excel.Application
        Set oExcel = CreateObject("Excel.Application")
        Dim sSheet As String
        sSheet = InputBox("Excel sheet", "Choose an Excel sheet", sDefaultExcelSheet)
        
        With oExcel
            .Visible = True
            .Workbooks.Open Filename:=sSheet, ReadOnly:=False
            With .Workbooks(1)
                Dim oSheet As Excel.Worksheet
                Set oSheet = .Worksheets(1)
                
                MsgBox "Transfer data?"
                
                oSheet.Cells(2, 2).Value = vScalar
                oSheet.Range("B5:B29").Value = WorksheetFunction.Transpose(vVector)
                
                If MsgBox("Save Excel sheet?", vbYesNo) = vbYes Then
                    .Save
                Else
                    .Close SaveChanges:=False
                End If
            End With
            .Quit
        End With
        Set oExcel = Nothing
    End Sub
    
Ansicht von 3 Beiträgen - 1 bis 3 (von insgesamt 3)
  • Du musst angemeldet sein, um auf dieses Thema antworten zu können.