encrypting exported data (Excel 2000)

Topic

New Reply

What methods are available to encrypt/scramble some exported data I have from Excel?

I need to be able to allow the user to do a custom export (not regular SaveAs) of a fixed set of variables (over 500) from a spreadsheet. The export function I’ll design but I’m wondering how to write the data such that it’s not easily editable by the end user (no text editable files so that I don’t have to worry about validating the data when it’s imported). There could be multiple instances of these exported data files.

I’ll then write my own custom import function to prompt the user to identify which data file to be imported and then descramble the data and re-apply it to the specified cells in the workbook.

The answer is probably some Win32 calls, eh? I’ve only done trivial Win32 API calls before so if that’s the answer, please direct me to some sources where I can learn more details.

So why do all this import/export? Because we don’t want the usrs to have to keeping doing ‘SaveAs’ and creating 40MB .xls files for each data set they want to keep.

Thnx, Deb

Reply | Quote

Replies

Take a look at the MSFT Crypto API.
Check MSDN for info.

Reply | Quote

If you create it as a disconnected ADO recordset in the default proprietary format, it’s pretty incomprehensible without ADO. Could it be re-opened in Excel? Maybe… As far as I can tell, you can’t password protect the saved recordset, so determined users probably could mess it up. But, for the average user, you probably could discourage editing with a bizarre file extension…

See HOWTO: Create ADO Disconnected Recordsets in VBA/C++/Java (Q184397) for some sample code.

See ADO 2.7 API Reference: Save Method, or your favorite ADO book, for more technical guidance.

Reply | Quote

Hmmm, that’s an interesting idea… I’ve only mildly played around with ADO so I’d have to spend some time with it again.

I just got done installing Access to play around with and see that it has a security feature with encryption (from the Tools menu). What about from Excel exporting to an Access table and then encrypting it? I’ve never communicated to Access via Excel but this seemed to be one solution.

The user doesn’t have to have Access installed for this to work do they? I’d just be read/writing to the .mdb file via my Excel program. I’m quite good with Excel VBA but don’t know much about Access beyond creating trivial stuff.

The solution is adequate if it deters most users, it’s Office after all, security isn’t a integral feature anyway.

Thnx, Deb

Reply | Quote

The ADO DLLs are all you need to read, write, etc., and for most purposes, the version installed with Office 2000 is sufficient, if I remember correctly. You’re definitely right that an MDB offers more security options, but I don’t know how you create them (MDW?) without Access.

Reply | Quote

Somewhere I thought I heard that you don’t need Access to read/write .mdb files. If that’s true, then I can create an initially empty table and just (she says nievely) do code tin Excel to read/write to it.

Is that true? The end users definitely won’t have Access.

Thanks for the brain storming,
Deb

Reply | Quote

Hi Deb,

Another method that might work is to use a macro to write the export data to a worksheet in a new workbook and password-protect the worksheet before saving. Then you’d be able to import the exported data – without even opening the workbook or removing the password protection. In the meantime, anyone who needed to could still view the exported data.

The one problem that I see with whatever method you use is that there’s always a risk of users exporting erroneous data. Such is life…

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Sorry I was rushing through an idea and didn’t get it completely down. You can create the MDB from ADO. I don’t know how to do security with ADO, since usually it involves the creation of a workgroup file (MDW) in Access. Probably is a topic on this somewhere up at MS…

Reply | Quote

Why not write your own encryption/decryption routine? Its not too difficult and will keep away 98% of the prying eyes…..

My thoughts of how it would work:

You have a special spreadsheet which autoruns a form. The form would contain 2 buttons and 2 text boxes – One button for “Encryption” and one button for “Decryption” and the text boxes for the input and output spreadsheet(s).

The spreadsheet would of course be password protected and all the neccessary security frills so that the oridnary user can’t get to see your encryption/decryption routines without knowing the correct password.

On the sender’s side, the user fills in the input and output text boxes and clicks on “Encryption”. The spreadsheet then fetches the data from the input spreadsheet, encrypts the information and then writes the encrypted information out to the new spreadsheet.

On the receiver’s side, the user filles in the input and output text boxes and clicks on “Decryption”. The spreadsheet then fetches the data from the input spreadsheet, dencrypts the information and then writes the dencrypted information out to the new spreadsheet.

Any thoughts or ideas or comments on this suggestion?

Reply | Quote