none
how do i change a picture DPI programaticaly in Excel via Automation RRS feed

  • Question

  • How do I change a picture DPI via code?

    How do I access the following menu? What values are each? Am trying to get to Screen 150 ppi

    I already tried to create a macro, however when edited it I only saw the line where it selected the cell, nothing else.

    Friday, July 19, 2019 12:40 PM
    Moderator

All replies

  • As info, I asked the same on Excel for developers forum, I got the following macro

    Public Sub Sample()
      If TypeName(Selection) = "Picture" Then
        '* select [Web (150 ppi)]
        'press Alt and typing W key.
        Application.SendKeys "%w"
        
        '* select [Web (150 ppi)] and enter
        'press Alt and typing W key, and then press the Enter key.
        'Application.SendKeys "%w~"
        
        'show [Compress Pictures] dialog.
        Application.CommandBars.ExecuteMso "PicturesCompress"
      End If
    End Sub

    Friday, July 19, 2019 6:54 PM
    Moderator
  • Sounds like there's nothing directly in VBA for this (I don't know this for sure--I've never heard of this functionality before) and so you have to simulate doing it interactively. The problem, of course, is doing that from VFP.

    It is possible to tell VFP to run an Excel macro, so you could create the macro via Automation, then run it via Automation, and then even delete it via Automation.

    Tamar

    Friday, July 19, 2019 8:29 PM
    Moderator
  • I thinik that is a very good idea, you also find VBA code to automate the Worlbook.VBProjects.VBComponents object, but I think this is a bit of a Munchhausen pullíng himself out of quicksand operation aka bootstrapping.

    As the macros dialog lists macros of the current and other open workbooks, I think the easiest would be to manually create a workbook with that macro in Excel, store it as a template and use it, or open it as a secondary excel workbook and find out how to address a makro from another workbook to act on the workbook picture object you want to change.

    Sounds more complicated than it is, I mean one such workbook would be an assistant workbook, you'd not use it as a template and so not need other workbooks to be based on it, you'd juyt open it in parallel, use its macro and close it again.

    That should be possible without needing to deep dive into the automation of the VBA IDE component of Excel or Office.

    I gave it a go to make this proof of concept:

    1. Step create a new Excel sheet (in Excel itself) and create the following macro in it:

    Sub helloworld(ByRef oWorksheet As Worksheet)
       oWorksheet.Cells(1, 1).Value = "Hello World"
    End Sub
    
    Save it as c:\mymacros\macros.xlsm (xlsm is the extension for Excel workbooks with macros!)


    2. Run this in VFP:

    Local loExcel, loMacrosWorkbook, loNewWorkbook loExcel = CreateObject("Excel.Application") loNewWorkbook = loExcel.WorkBooks.Add() loMacrosWorkbook = loExcel.Workbooks.Open('c:\mymacros\macros.xlsm') loExcel.Visible =.t. loExcel.Run("helloworld",loNewWorkbook.Sheets[1]) loMacrosWorkbook.Close()

    That works out for me, I didn't test how this works when the macro you call triggers something interactive like the dialog of the picture settings, but to let this be possible I put the loExcel.visible = .T. before the Run() call.

    You'd likely want this without the interactive dialog, but I also didn't find a pure programmatically way to do that.

    Bye. Olaf.

    Sunday, July 21, 2019 7:49 AM
  • Do you specifically want to do this in Excel so it produces smaller xlsx files with that ppi?

    Because I think it is easier, if you simply modify your imagtes before adding them to excel with gdiplusx. Actually shrinking the picture size in the first place.

    If you want to shrink pictures already in a file you could do that by unzipping the xlsx, manipulating the pictiures and then zip that up again. 

    Bye, Olaf.

    Sunday, July 21, 2019 7:55 AM
  • I do need this to be done in excel.
    Sunday, July 21, 2019 6:53 PM
    Moderator
  • Hi,

    Do you resolve the issue? If you resolve the issue, please mark the helpful as answer. It will be beneficial to other community.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 24, 2019 2:28 AM
    Moderator