Read Excel values with OLE ActiveX

[DjZU]

Hello,

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:

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.

So I tried the following:

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)))

)

The Excel workbook contains this code:

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

In conclusion, I'm able to write but not to read datas from Excel. How to do it?

Thank you in advance.

[marlies]

Hello,

Did you try the atom ExcelActiveX already? This atom contains all the code to communicate with Excel using OLE.
This atom is available in the Data part of the library when you're using ED8. You can also find a lot of usuable example code in this atom.

However the code to read data from Excel using OLE could be something like (to read one cell):

Code: Select all

do(
 var([valRow], vbValue, p(1)),
 var([valCol], vbValue, p(2)),
 var([strSheetName], vbString, p(3)),
 var([valExcelAtomPointer], vbValue, p(4)),
 var([atmExcelAtom], vbAtom, if(valExcelAtomPointer <> 0, vtp(valExcelAtomPointer), atmExcelActiveX)),
 
 var([oleWorkbook], vbOle),

 if(
  atmExcelAtom = 0, 
  Do(
   Msg(
    Concat(
     [ No default Excel atom has been set.], cr, lf,
     [ Reset the Model or open the Excel User interface and try again. ]
    ), 
    1
   ),
   return(0)
  )
 ),
  
 if(
  StringLength(strSheetName) = 0, 
  strSheetName := Att(2,atmExcelAtom)
 ), 
 
 oleWorkbook := ExcelActiveX_GetRefWorkbook(atmExcelAtom), 
 Ole(
  oleWorkbook, 
  Concat(
   [Sheets(], sbo,
   strSheetName,
   sbc, [).Cells(],
   String(valRow), [,], String(valCol),
   [).Value]
  )
 )
)

When I have a look at this piece of code in your example:

Code: Select all

Ole(
 ExcelApp, 
Concat(
 [Cells(],
 String(2),
 [,],
 String(2),
 [)]
 ),
 test3
 ), 

It should work when you change it into:

Code: Select all

Ole(
 ExcelApp, 
Concat(
 [Cells(],
 String(2),
 [,],
 String(2),
 [).Value]
 )
) 

Hope this helps (let me know if not )!

Regards,

Marlies

[DjZU]

I get by with a little help from Marlies!

I expected that the Ole function would return the value in parameter e3 or e4. I finally created a function to read the value of a cell which coordonates are given in parameters and I call this function and keep its result in a variable.

If it can helps someone else, here is the function:

RegisterFunction(
[XlRead],
[ActiveX],
3,3,
[Ole(
ExcelApp,
Concat(
[Worksheets(],
Sbo,
p(3),
Sbc,
[).Cells(],
String(p(1)),
[,],
String(p(2)),
[).Value]
)
)
],
[p(1) as row, p(2) as column, p(3) as worksheet],
Concat([XlRead(2,1,],Sbo,[Sheet1],Sbc,[)]),
1,
[DjZU]
),

And the call:

Dim([ExcelApp], vbOle),
Dim([cellValue], vbValue),
ExcelApp := CreateOle([Excel.Application]),
Ole(ExcelApp, [Visible], 1),
Ole(
ExcelApp,
Concat(
[WorkBooks.Open(],
Sbo, Concat(XpDir(Pdir),[Work\\Workbook1.xls]), Sbc,
[)]
)
),

cellValue := XlRead(2,1,[Sheet1])

Thank you for your Help!

[marlies]

Hi,

I had a closer look at your code an made the following example for you. This code is executed for a fil named: book1.xls at C:\. I had a value in cell(1,1) of sheet 1.

When this code is executed the value will be visible in the tracer.

Code: Select all

do(

 var([test1], vbString),
 var([test2], vbString),
 var([test3], vbvalue),
 Dim([ExcelApp], vbOle),
 
 ExcelApp := CreateOle([Excel.Application]),
 Ole(ExcelApp, [Visible], 1),
 Ole(
  ExcelApp, 
  Concat(
   [WorkBooks.Open(],
   Sbo, [C:\\book1.xlsx], Sbc,
   [)]
  )
 ),

 Ole(
  ExcelApp, 
  Concat(
   [Worksheets(],
   Sbo,
   [Sheet1],
   Sbc,
   [).Activate]
  )
 ),
 test3 := Ole(
  ExcelApp, 
  Concat(
   [Cells(],
   String(1),
   [,],
   String(1),
   [).Value]
  )
 ), 

 Trace(String(test3))
)

The main issue I changed is: to store the result of the excelread in a vbString wouldn't work as it is returning a value. If you want to read strings, you could use .Text instead of .Value.

Let me know if it works!

[DjZU]

It works perfectly for boths values and strings using .Value and the according variable type (vbString or vbValue).
Thank you again for responding quickly.