PlanetSquires Forums

Please login or register.

Login with username, password and session length
Advanced search  

Author Topic: Question on PB  (Read 789 times)

Petrus Vorster

  • Senior Member
  • ***
  • Posts: 487
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: 9411
  • 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: 487
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: 487
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: 9411
  • 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: 487
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

Jim Dunn

  • Junior Member
  • **
  • Posts: 127
  • Jim Dunn
Re: Question on PB
« Reply #6 on: August 03, 2021, 10:33:08 PM »

But... when I write those "quick little tools" with PowerBASIC... all the anti-malware alarms go off (the companies I write for use "Cylance") but the SAME code in FreeBasic... NO ALARMS...
Logged
3.14159265358979323846264338327950
"Ok, yes... I like pie... um, I meant, pi."

Petrus Vorster

  • Senior Member
  • ***
  • Posts: 487
Re: Question on PB
« Reply #7 on: August 11, 2021, 04:03:43 PM »

So it does have benefits to still work on ancient systems!
I am certain the hard drive at work a Tutankamen Mk2 with 20gig space.

I think I will be lost at work if, by some miracle of heavens, this retarded company will move past XP and Win7 all on Pentium 4's!
So bye the way gents, I lost my apatite for programming due to the disasterous collapse of the workplace.

Maybe i should try a little of it again with FB.
I know there used to be a link where you could download the entire FB compiler, Paul's designer and al the Libraries.
Please point me in that direction if you can?

Perhaps it can lift my mood.

-Peter
Logged

Josť Roca

  • Guru Member
  • *****
  • Posts: 3331
Re: Question on PB
« Reply #8 on: August 11, 2021, 04:13:24 PM »

Petrus Vorster

  • Senior Member
  • ***
  • Posts: 487
Re: Question on PB
« Reply #9 on: August 11, 2021, 04:52:13 PM »

Thanks mate
Logged