1. Nigel Pilsbury
  2. PowerBuilder
  3. Tuesday, 25 January 2022 16:54 PM UTC

Hi all,

I am trying to convert Excel macro commands into Powerbuilder script.

The following macro script validates a column against a named list...

Sub SetColumnList()
' SetColumnList Macro
' Assigns a Named List to a column
Range("G3:G65536,G1").Select
Range("G1").Activate
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=StatusList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Validation"
.ErrorTitle = "Error"
.InputMessage = "Please select from list"
.ErrorMessage = "Entered value not found in list"
.ShowInput = True
.ShowError = True
End With
End Sub

 

I'm trying to convert this script so it will run in my Powerbuilder window.

Thanks for the help with this.

Accepted Answer
René Ullrich Accepted Answer Pending Moderation
  1. Friday, 4 February 2022 06:04 AM UTC
  2. PowerBuilder
  3. # Permalink

It's difficult to say whats going wrong. Maybe cells are protected?

 

Here a very simple example that works for me with Excel 2016:

OLEObject lole_excel, lole_workbook, lole_sheet

lole_excel = CREATE OLEObject
lole_excel.ConnectToNewObject ("Excel.Application")
lole_excel.visible = TRUE
lole_workbook = lole_excel.Workbooks.Add ()
lole_sheet = lole_workbook.ActiveSheet

// create a list in Cells C1-C3
lole_sheet.Range("C1").FormulaR1C1 = "1"
lole_sheet.Range("C2").FormulaR1C1 = "2"
lole_sheet.Range("C3").FormulaR1C1 = "3"
lole_sheet.Range("C1:C3").Name = "MyList"

// create the validation for cells A1-A3
lole_sheet.Range("A1:A3").Validation.Delete
lole_sheet.Range("A1:A3").Validation.Add(3, 1, 1, "=MyList")

lole_excel.Disconnectobject()
DESTROY lole_excel

 

Comment
There are no comments made yet.
Nigel Pilsbury Accepted Answer Pending Moderation
  1. Wednesday, 9 February 2022 17:12 PM UTC
  2. PowerBuilder
  3. # 1

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

Comment
  1. Miguel Leeuwe
  2. Wednesday, 9 February 2022 17:53 PM UTC
Thank you Nigel, this can be very useful!
  1. Helpful
  1. Mark Goldsmith
  2. Wednesday, 9 February 2022 19:00 PM UTC
You're welcome Nigel, thanks for the update (and the code snippet), glad to see you've got it working!
  1. Helpful
There are no comments made yet.
Mark Goldsmith Accepted Answer Pending Moderation
  1. Friday, 4 February 2022 21:27 PM UTC
  2. PowerBuilder
  3. # 2

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

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 3 February 2022 15:05 PM UTC
  2. PowerBuilder
  3. # 3

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

Comment
  1. Nigel Pilsbury
  2. Thursday, 3 February 2022 16:15 PM UTC
Chris,

Thanks for the reply. I do have code to create and check the object...

OLEObject ole_excel

ole_excel = CREATE OLEObject



li_ret = ole_excel.ConnectToNewObject("Excel.Application")

IF li_ret < 0 THEN

DESTROY ole_excel

MessageBox("Connecting to Excel Object Failed", "Error: " + String(li_ret))

Return

END IF



I even get the spreadsheet to create three NamedLists from selected sheet/column ranges.

This last step to validate a column against a NamedList is proving challenging.

Thanks
  1. Helpful
  1. Chris Pollach @Appeon
  2. Thursday, 3 February 2022 16:37 PM UTC
Since that basic connection works ... then I would look into the possibility that there was an MS-Office update that has also possibly changed the DOM model inside the workings of the spreadsheet. Food for thought.
  1. Helpful
There are no comments made yet.
Nigel Pilsbury Accepted Answer Pending Moderation
  1. Thursday, 3 February 2022 13:57 PM UTC
  2. PowerBuilder
  3. # 4

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.

 

Comment
There are no comments made yet.
Nigel Pilsbury Accepted Answer Pending Moderation
  1. Wednesday, 26 January 2022 14:00 PM UTC
  2. PowerBuilder
  3. # 5

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

 

Comment
  1. René Ullrich
  2. Wednesday, 26 January 2022 14:20 PM UTC
Yes this error message is not very helpful.

Use a Try-Catch-Block to catch exceptions. For OLE it is useful to catch the OLERuntimeError exception. It has a property "Description" that often contains the error message from OLE server (in your case Excel).
  1. Helpful
There are no comments made yet.
René Ullrich Accepted Answer Pending Moderation
  1. Wednesday, 26 January 2022 06:41 AM UTC
  2. PowerBuilder
  3. # 6

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é

Comment
There are no comments made yet.
Nigel Pilsbury Accepted Answer Pending Moderation
  1. Tuesday, 25 January 2022 23:20 PM UTC
  2. PowerBuilder
  3. # 7

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

Comment
  1. Miguel Leeuwe
  2. Tuesday, 25 January 2022 23:37 PM UTC
Okay, so you know how OLE works. I see what your problem is, it's very hard to translate any VB example to PB. Can't you just get the validation range values, run through all the cells, get the values and validate against the list in powerbuilder code?

regards
  1. Helpful
  1. Miguel Leeuwe
  2. Tuesday, 25 January 2022 23:43 PM UTC
does this page help at all? https://www.automateexcel.com/vba/data-validation-drop-down-lists-in-vba/

To find out the corresponding values of any VBA constants you can use MsgBox in a macro (as you probably already know).

regards.
  1. Helpful 1
  1. René Ullrich
  2. Thursday, 27 January 2022 05:50 AM UTC
VBA constants with its values are also listed in Object Catalog and in Online help.
  1. Helpful 1
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 25 January 2022 21:43 PM UTC
  2. PowerBuilder
  3. # 8

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

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.