I develop models which read and write datas from and to Excel. I first have done it using the DDE functions ExcelRead and ExcelWrite and recently had experimented some stability problems while writing datas. So I decided to prefer the OLE method. I have already changed the scripts of writing thanks to the quickstart help file. It seems that a model have to use only OLE or DDE and not both because now the script of reading does'nt work properly anymore. So I need to read from Excel with the OLE method but I haven't found any example about this.
The help file about the Ole function says:
So I tried the following:Parameters 3 (e3) and 4 (e4) are optional values that are send to the Ole / ActiveX server or are returned by the Ole / ActiveX server to Enterprise Dynamics.
The Excel workbook contains this code:do(
{ Register a function used by Excel ActiveX.}
RegisterFunction(
[Xpdir],
[Files],
1,1,
[Do(
Model.XString := [],
Repeat(
SubstrCount(p(1), [\]),
Model.XString := Concat(
Model.XString, StrSeperate(p(1),[\], Count), [\\]
)
),
Model.XString
)
]
),
var([test1], vbString),
var([test2], vbString),
var([test3], vbString),
Dim([ExcelApp], vbOle),
ExcelApp := CreateOle([Excel.Application]),
Ole(ExcelApp, [Visible], 1),
Ole(
ExcelApp,
Concat(
[WorkBooks.Open(],
Sbo, Concat(XpDir(Pdir),[Work\\ModelGenerator\\My Models\\Workbook1.xls]), Sbc,
[)]
)
),
Ole(
ExcelApp,
Concat(
[Worksheets(],
Sbo,
[Sheet1],
Sbc,
[).Activate]
)
),
Ole(
ExcelApp,
Concat(
[Run(],
Sbo,
[EdRead],
Sbc,
[,],
Sbo,
[Sheet1],
Sbc,
[,],
Sbo,
[B2],
Sbc,
[)]
),
test1,
test2
),
Ole(
ExcelApp,
Concat(
[Cells(],
String(2),
[,],
String(2),
[)]
),
test3
),
Trace(Concat(String(test1),[ ],String(test2),[ ],String(test3)))
)
In conclusion, I'm able to write but not to read datas from Excel. How to do it?Public Function EdRead(ws, coords As String) As String
' Send the value of a cell to ED
'MsgBox ("Hello world")
EdRead = ThisWorkbook.Worksheets(ws).Range(coords).Value
MsgBox EdRead
End Function
Thank you in advance.