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
aktualisiert.