PlanetSquires Forums

Please login or register.

Login with username, password and session length
Advanced search  

Author Topic: Question on PB  (Read 97 times)

Petrus Vorster

  • Senior Member
  • ***
  • Posts: 480
Question on PB
« on: April 07, 2021, 11:26:09 AM »

Hi gents

I made a little tool for our auditors that extract info from TXT reports from a real lame system.
Works like a charm, and simply finds the correct info and puts it in an Excel sheet.

I figured out how to send TEXT and Currencies/Numeric to the Excel sheet using Paul's Excel.Sll
Marvelous tool still.( Thanks Paul)

Unfortunately, it also needs to send a DATE in such a way that EXCEL will recognize it as a DATE and not TEXT.
The manual formatting in Excel later consumes copious amounts of time that the team don't have.
The reason is that we must sort those sheets in User Names and then DATE order and that wont work unless Excel recognizes the column as a date.

In time I will try and do that sorting in the tool, but for now I just need to know how to pass the DATE correctly to EXCEL.Sll

If someone here is still keeping their PB projects around.

Regards, Peter

Logged

Paul Squires

  • Administrator
  • Guru Member
  • *****
  • Posts: 9386
  • Windows 10
    • PlanetSquires Software
Re: Question on PB
« Reply #1 on: April 07, 2021, 12:02:46 PM »

Hi Peter,

I dug out my old code for the excel sll and found the following in my test code file. Looks like I had designed the api so that you can output a date. Take a look at the following (I am copying this directly from my test file):

Code: [Select]
    'write a date to the file. Dates can be written as literal text strings but doing so will not allow
    'the date to be formatted. The date will be eventually written as a number after conversion to a
    'Julian date number.
    'Write todays date..... Use format #12 mm/dd/yy (you can change the different formats by modifying
    'the code in xlsWriteDefaultFormats. The date is expected to be in the YYYYMMDD format. You can convert
    'from mm-dd-yyyy format to YYYYMMDD by calling the CTOD$ function.
    mDate$ = CTOD$(DATE$)
    stat& = xlsWriteDate(mDate$, 15, 1, %xlsFont1, %xlsLeftAlign, %xlsCellNormal, 12)
   
    mDate$ = "19991128"
    stat& = xlsWriteDate(mDate$, 16, 1, %xlsFont1, %xlsLeftAlign, %xlsCellNormal, 13)

    mDate$ = "20000331"
    stat& = xlsWriteDate(mDate$, 17, 1, %xlsFont1, %xlsLeftAlign, %xlsCellNormal, 14)
   
    mDate$ = "20010630"
    stat& = xlsWriteDate(mDate$, 18, 1, %xlsFont1, %xlsLeftAlign, %xlsCellNormal, 15)

    mDate$ = "19991023"
    stat& = xlsWriteDate(mDate$, 19, 1, %xlsFont1, %xlsLeftAlign, %xlsCellNormal, 20)

Logged
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

Petrus Vorster

  • Senior Member
  • ***
  • Posts: 480
Re: Question on PB
« Reply #2 on: April 07, 2021, 01:53:33 PM »

Thank you for digging up the old Files Paul

If i use those lines, the XLSWRITEDATE gives an Error 516. Deftype, Type ID ....

This is how i currently write to those excel fields:

iCell = ws.AddCell( y&,colz&+1, Val(mydata)) ' For Values
iCell = ws.AddCell( y&,colz&+1, mydata)        ' For text

the Y&, and colz& are cell coordinates.
This is from some of your examples.
If i just change the date to YYYYMMDD it still assumes it's text.

Thanks you for spending time on old stuff for me.

Kind regards.

Peter
Logged

Petrus Vorster

  • Senior Member
  • ***
  • Posts: 480
Re: Question on PB
« Reply #3 on: April 07, 2021, 04:21:21 PM »

Thanks Paul.

It turns out after studying your documents all i had to do was:
Write a date string YYYY-MM-DD
Then use the icell.format(yyyy/MM/dd)

You are indeed a genius.

Regards,

Peter
Logged

Paul Squires

  • Administrator
  • Guru Member
  • *****
  • Posts: 9386
  • Windows 10
    • PlanetSquires Software
Re: Question on PB
« Reply #4 on: April 07, 2021, 08:06:29 PM »

Lol,  :-)   Happy that you got it working!

I rarely look at old PB code anymore. I have it all stored in some old directories collecting hard drive dust.
Logged
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

Petrus Vorster

  • Senior Member
  • ***
  • Posts: 480
Re: Question on PB
« Reply #5 on: April 08, 2021, 01:17:26 AM »

For me, PB still fulfill the need for making quick little tools that doesn't need to be installed and can be hidden from all kinds of IT policies.
On our vintage work systems that is perfectly fine.

I am also busy with freebasic, but I still have quite some distance to go to get where i am on PB.

Regards, Peter
Logged