Hi Nigel,

As you'd probably already know, you have to use OLE to do this. I think the reason why not many of us are responding, is that - at least myself - don't have a clue of what half of the statements of your macro is doing.

If you don't know how to use OLE with Excel I can try to give some example, but there should be many available examples if you search on OLE or Excel on this Q&A or google a bit.

regards

Thanks Miguel,

Yes, lots of Google searches haven't uncovered much.  I've read the MSDN documentation on the Range Add Validation function but cannot determine how to translate VB syntax into something Powerbuilder/OLE will be happy with.

I got the syntax to Add a NamedList working...

Sub NamedList()
' NamedList Macro Selects column on Status Sheet and defines as Named List
Sheets("Status").Select
Range("A2:A20").Select
ActiveWorkbook.Names.Add Name:="StatusList", RefersToR1C1:= _
"=Status!R2C1:R65536C1"
ActiveWorkbook.Names("StatusList").Comment = ""
End Sub

successfully translates to...

ole_excel.Worksheets("Status").Activate
ole_excel.ActiveSheet.range("A2:A20").Select
ole_excel.Selection.Name = "StatusList"

Now I need to get the syntax to tie the Named list as a Validation list.  Type 3 supposedly indicates a xlValidateList is being used.

Formula1 indicates the Named List

This is not it, but you get the idea...

ole_excel.ActiveSheet.Range("E2:E65536").Select
ole_excel.ActiveSheet.Range("E2:E65536").Validation.Add.Type=3
ole_excel.ActiveSheet.Range("E2:E65536").Formula1 = '=StatusList'

Thanks

Hi Nigel,

It is usually not so difficult to translate VBA into OLE calls. But you have to understand, what the VBA calls do and how you can call it from PB.

It is very helpful to use the "Object Catalog" in the Excel VBA Editor (Press F2) to see the objects, properties, events and subroutines/functions.

There you can see, that (for example) Validation object has a subroutine

Sub Add(Type As XlDVType, [AlertStyle], [Operator], [Formula1], [Formula2])

You have to call it from PB as a function!

Problem with PB is: You can't call a function with named arguments (e.g. Type:=1) like in your VBA code (I don't know a way to do it). So you have to specify the arguments in the order of the function definition. Sometimes you have to specify also optional arguments to have the arguments you want to specify at the right position in your call.

In your case this could work:

ole_excel.ActiveSheet.Range("E2:E65536").Validation.Add (3, 1, 1, "=StatusList", "")

Hope it helps to start!

René

René,

Thank you for your reply.  Your explanation makes sense.  I had not understood the position-related function syntax until now.

I still get an 'Error calling external object function' error message, but at least I know the syntax and can experiment with different argument combinations.

Thanks

René,

I have tried several things...

Using long variables for the Type, Alertstyle, and Operator parameters, using only one Formula value, using a second "" Formula, wrapping the function in a Try/Catch block.  Each time the code is execute I get an "Error calling external object function add..." message.  

Long ll_validate, ll_alertstyle, ll_operator
ll_validate = 3 //Value must be present in a specified list
ll_alertstyle = 1 //Stop
ll_operator = 1 //Between

TRY
ole_excel.ActiveSheet.Range("E2:E65536").Validation.Add(ll_validate, ll_alertstyle, ll_operator, "=StatusList")
CATCH (OLERuntimeError er)
MessageBox("Runtime Error", er.Description)
END TRY

Thanks for any further help or advice you can give.

Hi Nigel;

   Make sure that your ConnectToObject and/or ConnectToNewObject OLE command is working OK for the "ole_excel" object class. If these fail, you could end up with this type of error.  HTH

Regards .... Chris

Hi Nigel,

Have you tried some very basic/ simple data validation settings via OLE first to see if they can even be set? You may wish to try:

1) ole_excel.ActiveSheet.Range("A7:A9").Validation.Add(3, 1, 1, "High, Medium, Low")
2) ole_excel.ActiveSheet.Range("A7:A9").Validation.Add(3, 1, 1, "=D3:D5") where D3 through D5 contain some values already

If you can't add data validation via the above then René may be on to something as far as protected cells. As well, if these cells already have a data validation rule set then you must remove it first via ole_excel.ActiveSheet.Range("A7:A9").Validation.Delete. If those aren't the problem, then I'm thinking the issue may be in how your named lists were created.

HTH...regards,

Mark

Thank you René, Mark, and Miguel for your input and advice.

René... I could create a new spreadsheet using your example.

Mark... your High, Low Medium example helped me prove the validation process does work.

I think I was struggling with the Workbook, Worksheet definitions.

I got the following code to open an existing Excel spreadsheet, create a NamedList on one of the worksheets, and then use the NamedList as validation for a column on another worksheet.  I hope others can use this as an example.

Thanks,

Nigel

int li_ret

OLEObject lole_excel, lole_workbook, lole_sheet

lole_excel = CREATE OLEObject
li_ret = lole_excel.ConnectToNewObject("Excel.Application")
//Make sure we can start up Excel on the local machine
IF li_ret < 0 THEN
DESTROY lole_excel
MessageBox("Connecting to Excel Object Failed", "Error: " + String(li_ret))
Return
END IF

//Allow FileOpen window to resize. Adding '2' as a parameter opens Explorer-style dialog box.
li_ret = GetFileOpenName("Select File", is_import_path, is_import_file, "Doc", "Excel Files (*.xls),*.xls", '', 2)
If li_ret <> 1 then RETURN

//Open the path/filename as an Excel workbook and hide from User
lole_excel.WorkBooks.Open(is_import_path )
//The entire spreadsheet is a Workbook. The rows/columns are on individual Worksheets.
//The main Worksheet is 'Equipment'
//Any additional 'Status' Worksheet contains all valid Equipment Status codes
lole_workbook = lole_excel.application.workbooks(1)

//We dont need to see Excel maximized as we modify it
lole_excel.Application.Visible = True
lole_excel.windowstate = 1 // 1 : Normal, 2 : Minimize, 3 : Maximize

//Each supporting exported Code table should be create as a Named List
//Set the Status sheet as active and the entire column, except heading, as NamedList
lole_excel.Worksheets("Status").Activate
lole_excel.ActiveSheet.range("A2:A65536").Select
lole_excel.range("A2:A65536").Name = "StatusList"

//The main worksheet 'Equipment' has a Status column E that has data (starting in row 3) to be validated against the Named List "StatusList"
//Set the Equipment sheet as active
lole_excel.Worksheets("Equipment").Activate
//Remove any existing validation from column E
lole_excel.Range("E3:E65536").Validation.Delete
//Set the 'Equipment' sheet column E to validate against the 'Status' code list
//Validation Type 3 (Value must be present in a specified list)
//AlertStyle = 1 (Stop)
//Operator = 1 (Between)
lole_excel.Range("E3:E65536").Validation.Add(3, 1, 1, "=StatusList")

//Save the entire Workbook
lole_workbook.Save()
//Quit Excel
lole_excel.Quit()
lole_excel.Disconnectobject()
//Destroy the created OLE object
DESTROY lole_excel
//Cleanup any memory
Garbagecollect()