SAVE AS to Excel with Dates

[tigress]

I have a datawindow that I need to save to Excel. The dw has 2 date columns, but when I do a Save Rows As and save it to excel it puts these date columns to TEXT and I am not able to sort or format them in excel. Is there any other way to do this?

Thanks

 

[Gamecock]

Try sort() before writing to excel

 

[tigress]

That doesn't seem to work. The user is wanting to be able to sort the spreadsheet by any columns once she has it saved to Excel. She can sort it by date within powerbuilder and then save it to Excel...but once in excel she can't resort by something else and date.

 

[pbp]

Hi, tigress, Gamecock.

I am having the same problem with dates in Excel.
Have you been able to solve it?

I am saving dw with dates to Excel file. After it is saved, the user can not sort data by that column. The reason is : date format from sybase "mm-dd-yyyy hh:mm:ss".
I was able to find a solution in Excel itself:
Use replace function to replace "00:00:00" with " ". In this case the time and the single quote in the beginning of the date dissapear, which makes the field sortable.

Now I am trying to automate it in PB.
I save the data in Excel format and the open it thru OLE.
However I can not find solution for using vb "Replace" function in pb code. It gives me errors.

Any suggestions??
Please help, anybody.

Thank you,

pbp

 

[ibf123]

Hello,

You can create Excel file using OLE. In this case you can write what you need and you don't need to warry any replacing.

Thanks.

 

[everkleer]

I ran into this issue once. Try just using the saveasascii function to save your datawindow and import it into excel and if necessary you can open it thru pb using the excel ole and do any formatting (column widths etc..)

 

[pbp]

Thank you very much.

By this time I figured to save it as .html and then open it in Excel. Works slowly, data looks different, but it works.

I'll try saveasascii() function.

Also, I was trying to find a way to rename the file from PB code without OLE. Like renaming file (changing extention) in Explorer. Is it possible?

Thanks.

 

[everkleer]

When I was trying to export to Excel, I tried saving as all different types and I think saveasascii ended up working best for me, although I had to do some extra formatting on the file through the OLE to make it look good.

In reguard to your renaming issue, you could use the pb filemove function but remember, simply renaming the file (file.txt -> file.xls) only changes the name (and what it opens with by defaul) not the actual type. (Just because it says '.xls' doesn't mean it's a true Excel doc.)

 

[nikand]

You can use dw2xls library (

http://www.desta.com.ua/dw2xls/)to

export your reports to excel file.