8 Replies Latest reply: Apr 15, 2016 2:10 AM by appi karna RSS

    Macros issue

    appi karna

      Hi Experts,

       

      I need a help for one a report. There's an export button on my report which should send the current selection to excel if clicking on it. Not required to do a send to excel. But the issue is it's excluding 3 of the values from a field and then giving the excel sheet saved directly on the desktop which shouldn't happen.

       

      the macros code is as follows:

       

       

      sub exportToExcel

      dim o
      ExportPath = ActiveDocument.Variables("vExportPath").GetContent.String
      ObjectID = ActiveDocument.Variables("vObjectID").GetContent.String
      ChartName = ActiveDocument.GetSheetObject(ObjectID).GetCaption.Name.v
      Set o = ActiveDocument.GetSheetObject(ObjectID)
      o.ExportBiff ExportPath & ChartName & ".xls"

      Set o = nothing


      end sub

       

      where the value for vExportPath = =if(vExportPhysicalFolder = 'My Desktop', vExportPath1, vExportPath2)

       

      and the value for vObjectID = 12Month , then to further drill down vExportPath1 = ='C:\Users\'&Subfield(OSUser(), '\', 2)&'\Desktop\'

      and vExportPath2 = ='C:\Users\'&Subfield(OSUser(), '\', 2)&'\Documents'

       

      Request you to please provide some inputs on it.

       

        • Re: Macros issue
          Chanty 4u
          • Re: Macros issue
            Marcus Sommer

            It's not quite clear what you mean with:

             

            But the issue is it's excluding 3 of the values from a field and then giving the excel sheet saved directly on the desktop which shouldn't happen.

             

            Did it mean that the macro generally worked unless for certain objects or didn't it work?

             

            - Marcus

              • Re: Macros issue
                appi karna

                Hi Marcus,

                 

                Yes the macros worked but one of the field say Field A has 10 values in it and out of 10 its excluding 3 of the values and giving me the details of just 7 values from the Field A.

                  • Re: Macros issue
                    Marcus Sommer

                    You mean if you looked on the chart in qv it has 10 rows and if it's exported without any further selections or actions it has only 7 rows? If yes, I think you exported the wrong object. This meant you need to check and compare the object-id against your (possible) variable-values.

                     

                    - Marcus

                      • Re: Macros issue
                        appi karna

                        Hi Marcus,

                         

                        The macros code has a variable vObjectID set to "12 month" but the data from where I am exporting is a straight table and has the ObjectID as CH90. So do you mean to say that since the object ids differ as 12 month and CH90 it's filtering out the 3 values out of 10 and giving me just 7.

                         

                        In such a case I need to figure out the ObjectID which is set as 12 month and should ensure that it has all the 10 values because in macros its referring to vObjectID set to 12 month.

                         

                        Just as an info I also tried to change the vObjectID set to 12 month as CH90 but it didn't work.

                          • Re: Macros issue
                            Marcus Sommer

                            Have also a look on the caption name of these table then if it's empty or contained space or other special chars your:

                             

                            ExportPath & ChartName & ".xls"

                             

                            wouldn't return a valid path.

                             

                            - Marcus

                              • Re: Macros issue
                                appi karna

                                Hi Marcus,

                                 

                                After changing the ObjectID in the macros code to the table I wanted to extract the data from I got the excel reading all the 10 values from the table. earlier it was reading the ObjectID as 12Month which was no where in my report.

                                 

                                Thanks for the guidance.

                      • Re: Macros issue
                        Avinash R

                        could you demonstrate the issue with sample outputs or snapshots ?? so that it will be helpful for us to understand