PxPlus User Forum

Twitter Twitter Twitter

Author Topic: Create/Update Excel Workbook  (Read 2221 times)

Tom Anderson

  • Silver Member
  • ***
  • Posts: 26
    • View Profile
Create/Update Excel Workbook
« on: June 06, 2023, 02:12:47 PM »
I am attempting to create and update an Excel workbook from within PxPlus. I have read through the documentation and cannot not get it to work. I keep getting error 20's so obviously I do not understand how to format the code correctly.

All I want to do is create a new workbook or overwrite an existing one if it exists. Then I need to add data to the first worksheet within the workbook.

Could someone assist with some simple code to accomplish this.

Much thanks!

Tom Anderson

Jeffrey Ferreira

  • Diamond Member
  • *****
  • Posts: 175
  • I must have taken a wrong turn at Albuquerque.
    • View Profile
Re: Create/Update Excel Workbook
« Reply #1 on: June 06, 2023, 02:30:11 PM »
Hi Tom,
I'm not sure at what point you are getting the error, but are you at least able to do this:

def object excel_obj,"Excel.Application"
filename$="c:\temp\customers.xls" ! Replace filename$ with your file name
workbook=excel_obj'workbooks'open(filename$)

can you get that far without an error?

jeff

James Crowther

  • Member
  • **
  • Posts: 11
    • View Profile
Re: Create/Update Excel Workbook
« Reply #2 on: June 06, 2023, 02:35:37 PM »
0010 DEF OBJECT EXCEL,"[WDX]EXCEL.APPLICATION"
0020 EXCEL'VISIBLE=1
0030 JUNK=EXCEL'WORKBOOKS'ADD()
0040 SHEET=EXCEL'ACTIVEWORKBOOK'WORKSHEETS(1)
0050 EXCEL'ACTIVEWORKBOOK'SHEETS(1)'NAME$="My Worksheet"
0060 ROW=1,COLUMN=1; SHEET'CELLS(ROW,COLUMN)'VALUE$="MY DATA"

Jeffrey Ferreira

  • Diamond Member
  • *****
  • Posts: 175
  • I must have taken a wrong turn at Albuquerque.
    • View Profile
Re: Create/Update Excel Workbook
« Reply #3 on: June 06, 2023, 02:42:57 PM »
Oops - I thought that was Tom posting that.
« Last Edit: June 06, 2023, 02:51:51 PM by Jeffrey Ferreira »

Tom Anderson

  • Silver Member
  • ***
  • Posts: 26
    • View Profile
Re: Create/Update Excel Workbook
« Reply #4 on: June 06, 2023, 04:12:02 PM »
Jeff,

Here is what I get:

-}def object excel_obj,"[WDX]Excel.application"
-}filename$="c:\qc_updates\test.xls"
-}workbook=excel_obj'workbooks'open(filename$)
Error #88: Invalid/unknown property name

Tom

Susan Cryderman

  • Staff
  • Gold Member
  • *****
  • Posts: 63
    • View Profile
    • PVX Plus Technologies Ltd.
Re: Create/Update Excel Workbook
« Reply #5 on: June 06, 2023, 04:16:46 PM »
If you are using PxPlus 2017 or newer - you can use the PxPlus Excel Object.

 ! demo program for Pxlus Excel Object
 ! retval can be checked after each command to ensure there were no errors
 !
  x_obj=NEW("*obj/excel") ! instantiate the object
 !
  path$="c:\spreadsheets\workbook_name.xlsx"
  retval=x_obj'CreateWorkbook(path$,1) ! 2nd parameter overwrites workbook_name.xlsx if it exits
  retval=x_obj'SetWorksheet(1) ! set active worksheet to 1 (not really neede - should already be 1 after creating the workbook
 !
  retVal=x_obj'write("value for cell C2","C2") ! ! Write a value to cell C2 in active worksheet
 !
  retval=x_obj'SetRange("A3:E3") ! set range to 5 cells in row 3
  new_range$="one"+SEP+"two"+SEP+"three"+SEP+"*four"+SEP+"five"+SEP
  retVal=x_obj'write(new_range$) ! write data to current range
 !
  retVal=x_obj'SaveWorkbook()
  retVal=x_obj'CloseWorkbook()
  DROP OBJECT x_obj
  END

Tom Anderson

  • Silver Member
  • ***
  • Posts: 26
    • View Profile
Re: Create/Update Excel Workbook
« Reply #6 on: June 06, 2023, 04:22:57 PM »
Susan,

Using version 16.

Tom

Tom Anderson

  • Silver Member
  • ***
  • Posts: 26
    • View Profile
Re: Create/Update Excel Workbook
« Reply #7 on: June 06, 2023, 07:27:12 PM »
OK, getting closer.
Created the following program:

01000 LET X_OBJ=NEW("*obj/excel")
01020 LET PATH$="c:\qc_updates\test.xlsx"
01040 LET RETVAL=X_OBJ'CREATEWORKBOOK(PATH$,1)
01060 LET RETVAL=X_OBJ'SETRANGE("A1:E1")
01080 LET NEW_RANGE$="one"+SEP+"two"+SEP+"three"+SEP+"*four"+SEP+"five"+SEP
01100 LET RETVAL=X_OBJ'WRITE(NEW_RANGE$)
01120 LET RETVAL=X_OBJ'SAVEWORKBOOK()
01140 LET RETVAL=X_OBJ'CLOSEWORKBOOK()
01160 DROP OBJECT X_OBJ

It completes successfully but the data in the cells A1 - E1 is the number 32003 in each cell.

Suggestions?

Tom

Susan Cryderman

  • Staff
  • Gold Member
  • *****
  • Posts: 63
    • View Profile
    • PVX Plus Technologies Ltd.
Re: Create/Update Excel Workbook
« Reply #8 on: June 07, 2023, 11:14:04 AM »
Hi Tom,

I tried this with our current version and with PxPlus 2019 (version 16) of the excel object and it works fine for me.

When the workbook is created - it will use the excel default template.  Is it possible this has some sort of formatting already there for these cells?

Tom Anderson

  • Silver Member
  • ***
  • Posts: 26
    • View Profile
Re: Create/Update Excel Workbook
« Reply #9 on: June 07, 2023, 05:18:40 PM »
Susan,

The only thing that has been changed in the default settings is the font for the cells.

Everything is as it is from the initial install.

Using the latest version of Excel fully patched.

Thoughts?

Tom

Tom Anderson

  • Silver Member
  • ***
  • Posts: 26
    • View Profile
Re: Create/Update Excel Workbook
« Reply #10 on: June 08, 2023, 03:47:31 PM »
Susan,

Have you had any other thoughts as to how to solve this issue?

Anyone else on here have any ideas?

Thanks!

Tom

James Zukowski

  • Diamond Member
  • *****
  • Posts: 297
    • View Profile
Re: Create/Update Excel Workbook
« Reply #11 on: June 08, 2023, 04:47:32 PM »
Tom,
We're running v15.1 and the program you provided worked fine; the cells have the text "numbers".
Have you tried checking the RETVALs after each step?
James Zukowski
Sr. Developer - J&E

BRAND>SAFWAY
Brand Industrial Services

Tom Anderson

  • Silver Member
  • ***
  • Posts: 26
    • View Profile
Re: Create/Update Excel Workbook
« Reply #12 on: June 08, 2023, 05:05:03 PM »
James,

The retval after each step is 1

Tom

Susan Cryderman

  • Staff
  • Gold Member
  • *****
  • Posts: 63
    • View Profile
    • PVX Plus Technologies Ltd.
Re: Create/Update Excel Workbook
« Reply #13 on: June 08, 2023, 05:21:10 PM »
Hi Tom,

I have had some co-workers try your program as well.  It worked fine in all cases.

It might also be a problem with the SEP separator.  The current separator can be checked by printing X_OBJ'SEP$ after instantiating the X_OBJ object.

Setting X_OBJ'VISIBLE = 1 shows the Excel application and might help to see what it looks like immediately after the WRITE function is completed.

Version 16 is no longer supported.  If the issue is still unresolved, you could download a newer version and retry your program using a demo activation.  Assuming the issue still occurs, you could then open a support ticket.  We would be interested in screen shots of the program as well as the results in excel etc.


Tom Anderson

  • Silver Member
  • ***
  • Posts: 26
    • View Profile
Re: Create/Update Excel Workbook
« Reply #14 on: June 08, 2023, 05:36:04 PM »
Susan,

When I print x_obj'sep$ I get nothing back.

I added the visible statement and the sheet shows with the same wrong data immediately after the write.

Tom