Results 1 to 9 of 9
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    encrypting exported data (Excel 2000)

    What methods are available to encrypt/scramble some exported data I have from Excel? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    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 <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: encrypting exported data (Excel 2000)

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

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: encrypting exported data (Excel 2000)

    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.

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: encrypting exported data (Excel 2000)

    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. <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

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

    Thnx, Deb <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: encrypting exported data (Excel 2000)

    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.

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: encrypting exported data (Excel 2000)

    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. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Thanks for the brain storming,
    Deb

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: encrypting exported data (Excel 2000)

    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
    [MS MVP - Word]

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: encrypting exported data (Excel 2000)

    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...

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: encrypting exported data (Excel 2000)

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •