Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save Preview Property (Excel XP)

    Can anyone point me at the way to save an Excel workbook with the "Save Preview Picture" ticked from VBA code <img src=/S/question.gif border=0 alt=question width=15 height=15>
    - I've looked through the Excel VBA helpfile and can find lots of Save... SaveData, SaveHiddenData, etc. but nothing like SavePreview

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Save Preview Property (Excel XP)

    As far as I know this property is still not exposed to VBA - it wasn't in 97 or 2000 and I can't find any sign of it in XP either. The only workaround I know of is that hideous SendKeys thing.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Preview Property (Excel XP)

    Found a microsoft doc that says the property isn't exposed in VB.

    If you have a fixed location for your files or are in control of your users software setup;
    How about creating an empty template (or even a plain xls file with its name and extension changed to protect the innocent) with the "Save preview" property set. Then whenever you need to save a file with a preview, open the PreviewTemplate file. (copy sheets or what ever and save).

    If the file location for template can not be static, you could try this. I did a test sample and it seem to work OK.
    I'm using Office2000. I first manually opened a new spreadsheet and set the "Save preview" property and then saved the empty spreadsheet. The xls file was about 34K. I then wrote code to open the file and did a binary read of the file and captured the data. (ditched the file. its not needed any more).

    I then created a function that uses the file 'Open strFileName For Binary Access Read Write' to write the same byte code to a file and close it. Effectively recreating the "base" file whenever I need a spreadsheet with a "Save preview". So I can now call the function and it creates the spreadsheet in whatever location i would need, then in code open the file as any other xls file and do stuff with it.
    Of course Microsoft does not assure you future compatibility.
    Your mileage may vary
    As a side note:
    Something interesting I notice when opening the file with a HEX editor was that I found my name imbedded in the Excel file in two locations. One was from the Author property of the spreadsheet. the second one however must be Microsoft fetching my user name and adding it to the file.
    Here's what I did:
    1.open the xls file with a HEX editor and over wrote my name with "X"'s and save the file.
    2. reopen the file with the HEX editor to verify my name was gone.
    3. I opened the file with Excel. (Did nothing but close the file. No cell changes, no saves (not even prompted))
    4. open the file again with HEX editor and my name was *back* in the file. 1 time.

    I DID NOT save the file after opening it with Excel and I know I don't have Autosave for Excel loaded. I then tried it again but instead of overwriting my name with "x'''s I just removed the bytes that contained my name. The file became unusable. Ohhh yeahh I feel warm and fuzzy that Microsoft is in control.

    Later,
    Bruce

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Preview Property (Excel XP)

    Thanks Bruce, the suggestion of setting the property in a template worked great <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    especially as I was already using a template in my export routine <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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