4 Replies Latest reply: Nov 4, 2011 8:48 PM by John Witherspoon RSS

    Add filter condition using macro

      Hi All,

       

      Is there a way to restrict data using macros?

       

      Regards,

      Sabarish

        • Add filter condition using macro

          did you ever get an answer for this?

            • Re: Add filter condition using macro
              John Witherspoon

              activedocument.fields("Product").select "Apples"

               

              It may be better to do it with an action if that's an option, though.

                • Re: Add filter condition using macro

                  Thank you I figured it out but I used it with the () around “Apples”.  It is working perfectly, is there a difference?

                   

                  I got the code below from a blog and added some formatting and another layer to the array for geo.  Creates a spreadsheet in a few seconds for all 3 geos plus global.

                   

                  for i = 0 to UBOUND(aryExportDefinition)

                   

                                  '// Get the properties of the exportDefinition array

                                  qvObjectId = aryExportDefinition(i,0)

                                  sheetName = aryExportDefinition(i,1)

                                  sheetRange = aryExportDefinition(i,2)

                                  pasteMode = aryExportDefinition(i,3)

                                  SelectGeo = aryExportDefinition(i,4) 'NEW

                   

                                  Set objExcelSheet = Excel_GetSheetByName(objExcelDoc, sheetName)

                                  if (objExcelSheet is nothing) then

                                                  Set objExcelSheet = Excel_AddSheet(objExcelApp, sheetName)

                                                  if (objExcelSheet is nothing) then

                                                                  msgbox("No sheet could be created, this should not occur!!!")

                                                  end if

                                  end if

                   

                      objExcelSheet.Select

                   

                                  set objSource = qvDoc.GetSheetObject(qvObjectId)

                                  Call objSource.GetSheet().Activate()

                                  objSource.Maximize

                                  qvDoc.GetApplication.WaitForIdle

                   

                   

                                  if (not objSource is nothing) then

                   

                                                  if (pasteMode = "image") then

                                                                  Call objSource.CopyBitmapToClipboard()

                                                  else

                   

                                                                                  if(SelectGeo = "*") then 'NEW

                                                                                                  ActiveDocument.Fields("ACCOUNT_GEO").Clear  'NEW

                                                                                  else 'NEW

                                                                                                  ActiveDocument.Fields("ACCOUNT_GEO").Select(SelectGeo) 'NEW

                                                                                 end if 'NEW

                   

                                                                  Call objSource.CopyTableToClipboard(true) '// default & fallback

                                                  end if

                   

                                                  Set objCurrentSheet = objExcelDoc.Sheets(sheetName)

                                                  objExcelDoc.Sheets(sheetName).Range(sheetRange).Select

                                                  objExcelDoc.Sheets(sheetName).Paste

                                                  'assign columns width

                  objExcelDoc.Sheets(sheetName).Range("A1:A60000").ColumnWidth = 15

                  'autosize rows to fit

                  objExcelDoc.Sheets(sheetName).Cells.EntireRow.AutoFit

                  objExcelDoc.Sheets(sheetName).Range("A1:Z1").WrapText = True

                  objExcelDoc.Sheets(sheetName).Range("B2").Select

                  objExcelApp.ActiveWindow.FreezePanes = True

                   

                   

                   

                   

                                                  if (pasteMode <> "image") then

                                                  With objExcelApp.Selection

                        '      .WrapText = False

                         '     .ShrinkToFit = False

                                                  End With

                                                  end if

                   

                                                  objCurrentSheet.Range("A1").Select

                                  end if

                   

                   

                   

                  next

                   

                  Call Excel_DeleteBlankSheets(objExcelDoc)

                   

                  '// Finally select the first sheet

                  objExcelDoc.Sheets(1).Select

                   

                  '// Return value

                  Set copyObjectsToExcelSheet = objExcelDoc

                   

                  end function

                  '//