Excel Spreadsheet Column Options

[Ron Klassen]

Using the excel object is it possible to change the cell format? For example, I would like to change the format for all cells in column 1 from general to text.

[RobL]

Hi Ron,

I don't see a property or method of the *obj/excel object you could use to change the cell format, but there is the 'Excel property that give you access to the properties and methods of the Excel object.

The following works for me (PxPlus 2022 Windows):

begin
xl=new("*obj/excel")
wb=xl'OpenWorkbook("ExcelSS.xlsx")
ws=xl'SetWorkSheet("Sheet1")
xl'Excel'Columns("A")'NumberFormat$="@" ! set column 1 to text
xl'SaveWorkbook()
z=xl'CloseWorkbook(1)
drop object xl
end

Hope this helps!!

Regards,

Rob Leighton

[Ron Klassen]

Thanks Rob,

That's great.

Ron

[Ron Klassen]

There's a lot of info under excel and excel'columns etc. Where would I find some documentation on this?

[RobL]

Hi Ron,

I found useful information here: https://docs.microsoft.com/en-us/office/vba/api/overview/excel. I've also found examples here on the forum and the old mailing list as well as googling for examples of Excel VBA code and then trying to convert the solutions to PxPlus.

Rob