Creating a 300 dpi of cells A1:A15

2010-08-15
2012-12-09
  • labrynthscore
    labrynthscore
    2010-08-15

    I need to create a 300 dpi tiff of cells A1:A15 and that is the only thing that should be showing on the tiff.  Is there a way to to this with XLToolbox?  Also, I cannot change the screen resolution to achieve the 300 dpi.  The 300 dpi tiff needs to be created similar to the way a virtual printer would create it where the dpi is independent of screen resolution.

    Thank you.

     
  • Daniel Kraus
    Daniel Kraus
    2010-08-17

    Hi,

    the XL Toolbox cannot do this, but I can think of several ways to try and accomplish what you want:

    The usual screen resolution is around 96 dpi. You can zoom the cell range A1:A15 to about 300%, then take a screenshot, to get a 300 dpi equivalent graphic file. Of course, this only works if your vertical screen resolution is large enough to display all 15 rows at 300% zoom.

    Alternatively, you can create a pdf file from your worksheet (there are lots of free pdf creators out there). Adobe Reader (the full Acrobat package is *not* required) can take screenshots at arbitrarily high resolutions. You can determine the screenshot resolution in the Adobe Reader preferences, then use the "snapshot" tool. If you want to have gridlines between your cells, you will need to turn on this option in Excel's page setup.

    Hope this helps!

     
  • labrynthscore
    labrynthscore
    2010-08-17

    Hello,

    Thank you for your reply.

    I appreciate your suggestions and I know you don't know a lot about my project so let me explain.  Unfortunately, I cannot guarantee that my screen will be large enough to view all 15 rows at 300% zoom and more than one computer / user will be involved in the process, so each person may like a different screen dpi which means different zooms for different people and so this can be cumbersome trying to manage all this.  Also, I need to create a tiff not a pdf because another software package that will use the image does not support pdf, but does support tiff.  Lastly, everything needs to be controlled programatically from visual basic that comes with Excel.

    In an effort to try and hammer out a solution to my problem I have a few questions.  Is there a way to import cells A1:A15 into an Excel graph or to copy cells A1:A15 as a picture (shift + edit/copy picture) and paste this picture into a graph?  Then, can I save a graph in Excel at 300dpi using XL Toolbox even if the screen resolution is set at 96 dpi?  Can I control XL Toolbox from the visual basic that comes with Excel?

    Thank you.

    Mitch

     
  • Daniel Kraus
    Daniel Kraus
    2010-08-19

    Hi Mitch,

    what I meant to say was to take a snapshot of the PDF file at high resolution (to be set in the Adobe Reader preferences), then save that snapshot as a TIF file. This can't be automated though.

    Yes, it's possible to copy a cell range to the clipboard and save it as an image file. However you need to employ some special tricks to increase the DPI of the resulting image. That's basically what the XL Toolbox does when you export a chart for publication.

    It's possible to patch the XL Toolbox so that the "Export for publication" function can save cell ranges as image files as well. I can incorporate such a fix in the next version. If you want to do it yourself, do the following: Start the Visual Basic Editor (Alt+F11 from Excel). Open the "Form_ExportForPublication" form (code view). Scroll way down to the "UserForm_Initialize" procedure. Below the line "If TypeOf Selection Is Range Then", change the "false" to "true". There you go.

    As for automating the whole process, the XL Toolbox does not have an API which could be used by other Excel addins. However, since it's free & open source, you can just edit the code yourself to make it do what you want. I must warn you though - the "Export for Publication" code is very complex…

    Please let me know if this answers your question!

    Daniel

     
  • labrynthscore
    labrynthscore
    2010-08-23

    Hello Daniel,

    I really appreciate your reply.

    I am going to try to implement your suggestion to patch the XL Toolbox so it can save a range of cells as a tiff.  By the way I need to save the cells with no border around them.  Please let me know if this is possible.

    If I patch XL Toolbox correctly, then will I be able to use VB to automatically call XL Toolbox and have XL Toolbox save a (mouse) selected range of cells as a tiff (with no border)?  If this is possible then my mission can be accomplished.  Because, next a printer's software (not related to Excel) will then call up the tiff and print it out to make labels.

    Please let me know if I am on the right track.

    Thank you.

    Mitch

     
  • Daniel Kraus
    Daniel Kraus
    2010-08-30

    Hi Mitch,

    sorry for the late reply. Yes, I think principally this approach should be feasible. However, I'm not sure if there aren't easier ways to accomplish what you want. Surely you will have thought about some purpose-built labeling software? Or you could use the "Merge" functionality of MS Word to feed your data into labels?

    Best

    Daniel

     
  • Hello Daniel,

    Thank you for getting back to me.

    I made the changes ("fix") to XL Toolbox as you instructed…then I selected the range of cells I wanted to capture as a tiff.  Next I went to the Excel user interface menu bar and clicked on XL Toolbox then selected All charts \ Export for publication, set the DPI to 2400, clicked Export, typed in the file name and clicked Save.  I got an excellent quality tiff of the selected range of cells.

    Can you tell me what code I should use in Excel VB to call the XL Toolbox code and automatically save the selected cells (which were selected with running vb code) as a tiff.

    The label making printer I am using requires a tiff which it then prints out.  This is why I cannot use Words Merge label making feature.

    Thank you very much for your help.

     
  • Hello Daniel,

    Just to clarify…if I select a range of cells in Excel using VB with the code Range("A1:A15").Select, then what VB code should I use after this line to save this Range of cells as a tiff with DPI 2400 using your XL Toolbox.

    Thank you.

     
  • Daniel Kraus
    Daniel Kraus
    2010-09-03

    Hi,

    I can add some code so that this action can be easily performed. However I'll need some time to accomplish this, life is currently very busy. I'm planning to release an update soon.

     
  • Hello Daniel,

    First of all let me congratulate you on writing some beautiful code in XL Toolbox.  I poked around in it and reailized a tremendous amount of time, thouight and creativity must have gone into this.

    Also, per your suggestion above, please include the ability to save cell ranges and objects (for example pictures) on a spreadsheet as image files using the "Export for publication" function in your next update if possible.

    Yes, I would appreciate it if you would add some code so that I can use VBA to call your XL Toolbox to save cell ranges and objects (for example pictures) as image files, for example tiffs.  I am guessing you are going to let VBA call your "Export for publication" functionality in the XL Toolbox to accomplish this.

    I know you are extremely busy…just wondering if you are talking days, weeks or months to accomplish this VBA / XL Toolbox interactivity because I would like to use this functionality in my own little project so I may need to provide some time lines to people I work with.

    Thank you.

     
  • labrynthscore
    labrynthscore
    2010-09-04

    Hello Daniel,

    I guess I forgot to sign in a couple of times…a couple of postings show from "nobody", but they are from me.

    Mitch

     
  • Daniel Kraus
    Daniel Kraus
    2010-09-05

    Mitch,

    I'll hopefully be able to publish an update within the next week that provides a minimal API. You will need to set a reference to the XL Toolbox from your VBA project (Tools->References in the Visual Basic editor). I don't know yet how well this will work when your solution is distributed, but I guess there will be a way to find out.

    Best

    Daniel

     
  • labrynthscore
    labrynthscore
    2010-09-06

    Daniel,

    Thank you for your help.  I look forward to trying out your XL Toolbox with API functionality and will let you know how everything goes when it is distributed.

    Best regards,

    Mitch

     
  • Daniel Kraus
    Daniel Kraus
    2010-09-08

    Mitch,

    I just uploaded the new version (2.72). Please let me know if it works for your project. It's more of a quick hack right now, lots of room for improvement. Remember you need to set the reference to the Toolbox for your VBA project. Also, if you want to save the file without Excel gridlines, you will need to turn off grid line display in Excel (this can be done programmatically if need be).

    Daniel

     
  • Hello Daniel,

    Thank you for the update with the API.

    So far so good when I want to save a selected cell range as a Tiff; however when I try to save a picture (which I created using Shift + Copy as Picture)  I get an error message "Unable to create DIB section..".

    I tried this manually through the UI and found that the error message occurs when the radio button "Selected chart(s)/graphic objects" is selected, but everything works fine when the radio button "All graphic objects on the current worksheets" is selected.

    Please advise.

    Thank you.

    Mitch

     
  • labrynthscore
    labrynthscore
    2010-09-09

    Hello Daniel,

    I really appreciate your help.

    I also noticed using the API that the dpi setting stays at 300 even when I change it, for example to 600.

    For example with the following code:

    Dim XLToolBoxSucces As Long
    XLToolBoxSucces = DanielXLToolbox2003.XLTB_SaveSelectionAsFile("C:\test.tif", 600, XLTB_ColorMode_RGB)

    I get the same size image as I did when I was using 300 instead of 600.  Can this be easily fixed?

    Thank you.

    Mitch

     
  • Daniel Kraus
    Daniel Kraus
    2010-09-10

    Mitch,

    well, this happens when coding in a haste… In the XLTB_SaveSelectionAsFile function, a line is missing. Simply add ".SetDPI = DPI" above the ".FileName=FileName" line. Or you can download the corrected version from the SVN source repository here at SourceForge.

    As for the other problem… This is something that I have yet to find a solution for. The DIB section error message is returned by the operating system. This appears to be a bug in the Windows API. The error does not occur reproducibly. Both decreasing and increasing (!) the DPI may help.

    Hopefully with the added line, the Toolbox does what you want it to do. I'll improve on the API further (better exception handling etc.).

    Daniel

     
  • labrynthscore
    labrynthscore
    2010-09-11

    Hello Daniel,

    What you have done is terrific.

    I may set up my computer to utilize the SVN Source Repository; in the meantime I added your suggested DPI line of code and it worked to enable me to change the DPI.

    Regarding the DIB section error message, I tried both decreasing and increasing the DPI to no avail.  I found I am able to save the pics of cell ranges (which I create by selecting a range of cells then using “Shift + Edit / Copy as Picture”, then “Edit / Paste”) using the API if I add the following lines of code at the beginning of the subs:

    Private Sub UserForm_Initialize()

        ‘Add these two lines
        ob_ExportAllShapes.Enabled = True
        ob_ExportAllShapes = True

    Private Sub ob_ExportSelection_Change()
       
        ‘Add these two lines
        ob_ExportAllShapes.Enabled = True
        ob_ExportAllShapes = True

    Of course all I have done (I think) is set the radio button to “All graphic objects on the current worksheet” in the “All charts / Export for Publication” menu selection window of the UI.

    If I move the pics I want saved to a blank new worksheet this will work fine, but I would also like to be able to toggle the radio button between the “All graphic objects on the current worksheet” and “Selected chart(s)/graphic objects” using VB code so I am not locked into one radio button or the other being selected.  Is there an easy way for me to toggle this radio button using VB code and the XL Toolbox API?  If I can do this then the requirements for my project will be met (of course if you are ever able to work your magic in the XL Toolbox code to remedy this DIB / Operating System issue that would be ideal).

    By the way I can distribute my Excel Project which uses your XL Toolbox (and has a reference set to it in the VB Code window) if I load the XL Toolbox onto the computer using Excel.  So I am wondering if there is a way for me to get an .exe of the XL Toolbox with the changes mentioned in this reply so I can easily distribute it.

    Thank you for your help.

    Mitch

     
  • Daniel Kraus
    Daniel Kraus
    2010-09-26

    Mitch,

    just wanted to let you know that I'm working on it. Sorry it takes so long. One step at a time…

    Daniel

     
  • labrynthscore
    labrynthscore
    2010-09-26

    Daniel,

    Thank you for letting me know.

    You really created a wonderful and unique app and with the enhancement you are working on for my application it will be a critical component of our process.

    Mitch

     
  • Daniel Kraus
    Daniel Kraus
    2010-11-04

    Hi Mitch,

    I did extensive refactoring of the graphic export code, to be able to better develop and especially fix the "Unable to create DIB section" problem. This one is really quite tricky. You can check out the latest changes from the Repository and simply replace the existing modules and classes in the addin with the new code, if you want to try out if it works better for you. The API function should now correctly save the selected cells/chart/drawing(s), but the DIB section error may still occur.

    Best

    Daniel

     
  • Daniel Kraus
    Daniel Kraus
    2010-12-01

    Hi Mitch,

    don't know if you are still interested in the add-in, but I wanted to let you know that tonight's 2.73 release should hopefully provide the functionality you were looking for. Please let me know if it doesn't.

    Daniel

     
  • labrynthscore
    labrynthscore
    2010-12-03

    Hello Daniel,

    I have been anxiosly awaiting this release.  I will ket you know how things go.

    Mitch

     
  • Hello Daniel,

    Your Toolbox has been working beautifully for our application.  Thank you for the update.  It provides the functionallity we require.

    Mitch

     


Anonymous


Cancel   Add attachments