PxPlus User Forum

Main Board => Discussions => Programming => Topic started by: Ron Klassen on August 30, 2022, 12:29:32 PM

Title: Excel Spreadsheet Column Options
Post by: Ron Klassen on August 30, 2022, 12:29:32 PM

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.

Title: Re: Excel Spreadsheet Column Options
Post by: RobL on August 30, 2022, 06:24:22 PM

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

Title: Re: Excel Spreadsheet Column Options
Post by: Ron Klassen on August 31, 2022, 01:15:33 PM

Thanks Rob,

That's great.

Ron

Title: Re: Excel Spreadsheet Column Options
Post by: Ron Klassen on August 31, 2022, 05:51:08 PM

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

Title: Re: Excel Spreadsheet Column Options
Post by: RobL on September 01, 2022, 12:40:01 PM

Hi Ron,

I found useful information here: https://docs.microsoft.com/en-us/office/vba/api/overview/excel (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