Topic
  • 2 replies
  • Latest Post - ‏2013-11-14T18:10:58Z by emily.butt
emily.butt
emily.butt
6 Posts

Pinned topic Export to Excel with indentation

‏2013-11-14T14:29:59Z |

Hello all,

 I'm working on a bit of custom DXL that includes some exporting to Excel and setting various Excel properties on the output and I'm stumped.  I'm trying to indent within a cell and so far I've not been able to get it to work.  But I've been able to set other properties without issue.

 

Here's how I tried it with "olePut" ( trying to mimic the VBA that is recorded when I right-click a cell and select Format Cells and change the indent property on the alignment tab):

 s_result = olePut ( objExcelSelection, "HorizontalAlignment", xlLeft )
print "HorizontalAlignment: " s_result "\n"
s_result = olePut ( objExcelSelection, "IndentLevel", 1 )
print "IndentLevel: " s_result "\n"
 

The "HorizontalAlignment" part works, but the "IndentLevel" does not. 

 

Here's how I tried it with "oleMethod" (trying to mimic the VBA that is recorded when I use the toolbar increase indent button on a cell):

 

put ( objArgs, 1 )
s_result = oleMethod ( objExcelRange, "InsertIndent", objArgs )
print "InsertIndent: " s_result "\n"
clear objArgs


 

It doesn't work, but if I try to use other methods with oleMethod they work.

I've also tried each of these with both ranges and selections and had no luck, and I've tried using both bits of code together with no luck.

 

Does anyone have any idea where I'm going wrong?

 

Thanks!

 

  • GregM_dxler
    GregM_dxler
    166 Posts

    Re: Export to Excel with indentation

    ‏2013-11-14T16:25:53Z  

    According to Microsoft.  IndentLevel is a Property, so you would need to use the olePut command for the cell property.  Maybe try the oleGet to see what the current value is.  Maybe 1 is too small to be noticed.  In their library, their example is setting the indentlevel to 15.

    Hope this helps,

    Greg

    Updated on 2013-11-14T16:28:59Z at 2013-11-14T16:28:59Z by GregM_dxler
  • emily.butt
    emily.butt
    6 Posts

    Re: Export to Excel with indentation

    ‏2013-11-14T18:10:58Z  

    According to Microsoft.  IndentLevel is a Property, so you would need to use the olePut command for the cell property.  Maybe try the oleGet to see what the current value is.  Maybe 1 is too small to be noticed.  In their library, their example is setting the indentlevel to 15.

    Hope this helps,

    Greg

    Very strange.  If I do an oleGet before I use olePut, I get 0.  Then if I do another oleGet after I use olePut,  I get 1 or whatever I tried to set it to with olePut.  But when I go to the Excel output, it remains zero, confirmed using the popup window used to manually change the properties as well as querying the property with some VBA (and because it just looks wrong).  Likewise for the second approach, using oleGet on IndentLevel before and after using the InsertIndent method with oleMethod instead of the olePut approach.

     

    Even more interesting, when I tried bringing the olePut (or oleMethod) command out of the loop where I'm doing the export to the cells and instead going back at the end of the script and changing the indent of the cells with one of these commands, it works!

     

    So I guess both approaches work (using the InsertIndent method or changing the IndentLevel property), just not in the export loop, even though doing it in the export loop oleGet would lead one to believe that DXL thinks it made the change to the property.  Such complex subtleties!

     

    Thanks so much for your help, Greg!