Death to Apostrophes (remove leading apostrophes from active excel sheet)

For those who has had the pleasure of working with accounting software which doesn't correctly create exports to excel, here is a tip to get rid of the irritating leading apostrophe in the cells which forces them to text.
Yes Pastel Accounting, I am looking at you!

It is a small macro which removes ALL the leading apostrophes from the current active sheet.

Sub DeathToApostrophe()
   'Removes the apostrophe at the start of each cell which forces the content to be text
   Dim s As Range
   For Each s In ActiveSheet.UsedRange
      If s.HasFormula = False Then
      s.Value = Trim(s.Text)
   End If
   Next s
End Sub

Note: This was copied and edited from the macro created by this guy (Zack Barresse). If you are unsure as to how to use the above macro, follow the previous link for more information.

Note regarding dates:
Take care with regards to dates which excel automatically recognises and converts to a DATE formate. Sometimes excel converts it to the incorrect date format, creating extra work for you if you notice it, or leads to error if you don't.

For example: I exported journals from Pastel accounting, and the date format for the specific company file was set to dd/mm/yyyy. I ran the DeathToApostrophe macro and excel recognised and converted the dates to a mm/dd/yyyy format where possible. I attempted in vain to adjust my regional settings in Windows and Excel to correct this behavior, but nothing worked.

The solution was to change the date format used by Pastel accounting to my regional settings (yyyy/mm/dd). In Pastel accounting the date format is company specific and can be changed through the following menu items: Setup > Company parameters > Formats (tab) > Date Entry Format (section).

I might be necessary to similarly change the way the source formats its dates to prevent errors when removing the apostrophes in excel.

Pastel 2011 user password encryption

Do you have a pastel file that is password protected and you don't have the password?

Note: Make a backup before attempting the following.
*The following was tested on Pastel 2011, but is likely to work for other versions as well.

Pastel accounting software makes use of the Pervasive SQL database to store all the accounting data. Open the Pervasive.SQL Control Center (there should be a start menu entry). In Pervasive.SQL Control Center open the "AccountUser" table (follow the tree structure from Engines > $Computername$ > Databases > $Pastel database$ > Tables > AccountUser). If the database you are looking for isn't listed, then add a new database and pointing the location to the folder with your Pastel company's data in.

Now that the "AccountUser" table is open you will see the following fields: Password; ID; Description; AccessTable; ...

The Password field is an Char(8) encrypted using a caesar cipher shifted 18 places to the right on the ASCII table. eg. An encrypted password "twwRRRRR" is actually "bee". Note that the empty spaces of the field are denoted with the character "R".

If you wish to clear the password for a specific user, change the user's password field to "RRRRRRRR".

If you wish to decrypt the password, I find it best to copy the password into a HEX editor, subtract 12 (Hex) from each CHAR and convert the result back to ASCII to obtain the original password.
To edit the password is the opposite of the decryption.

Yey for awesome Pastel security features! Why do they store the actual password, when using hashes for password verification is standard good practice? Why implement a password feature when the data it protects isn't even encrypted (we are able to view/edit the data directly through the Pervasive.SQL Control Center or other applications, eg. Excel making use of ODBC, but more on that later).

So much for trying to keep your confidential Pastel accounting records confidential.