2 Replies Latest reply: Apr 16, 2018 4:13 PM by jonnathan rodrigues RSS

    Export To Excel

    John Roy

      HI

      I have to export the straight table into excel , in my dashbaord i aslo have the Branch list box and region list box .

      I have implemented the code it works fine but it gives me the data by Region and i keep geting the message box all the branches

      but i want the branches of each Region should also be pasted in the Excel , Can some one suggest ,

      Here is my code where upon i get the values

      sub ExportToExcel_Divisionwise

           Dim Counter

             Counter = 1

       

       

      Dim CLApp, XLDoc,XLSheet

             set XLApp = CreateObject("Excel.Application") ' Define Object

             XLApp.Visible = True 'Visible set as true

             set XLDoc = XLApp.Workbooks.Add 'Open new workbook

             Set ListBoxy = ActiveDocument.GetSheetObject("LB17") 'Name of the Branch

                Set Fieldy = ListBoxy.GetField

              valy = ListBoxy.GetPossibleValues  

             

        For y = LBound(valy) to UBound(valy)

              Counter = 1

                    Fieldy.Select valy(y) 'vSelectedItemy

                    set XLSheet = XLDoc.Worksheets.Add

                    Set BranchVal = ActiveDocument.Fields("Branch").GetPossibleValues

              For BranchValList = 0 To  BranchVal.Count-1

                 msgbox  BranchVal.Item(BranchValList).Text

                

                 IF(Counter = 1) then

                       ActiveDocument.GetSheetObject("CH09").CopyTableToClipboard TRUE

                 XLSheet.paste

                 xlSheet.Paste xlSheet.Cells(Counter, 1)

                       else

                          

                       'Finding blank cell to paste the tables data.

                      Dim clm, row

                       clm = 1

                         For row = 3 To 1048576 Step 1

                                  If(xlSheet.Cells(row,clm) = "") Then

      '                                 xlSheet.Cells(row,clm).EntireRow.Delete                      

                                        Counter = row

      '                                 Msgbox(row)

                                 exit for

                               End If

                           Next

                                          

      'Copy and Paste New Product table

                      BranchVal.Item(BranchValList).Text.CopyTableToClipboard TRUE

                       xlSheet.Paste xlSheet.Cells(Counter+1, 1)

                       'ActiveDocument.GetSheetObject("LB01").CopyTableToClipboard TRUE

                       'xlSheet.Paste xlSheet.Cells(Counter, 1)

                       ActiveDocument.GetSheetObject("CH09").CopyTableToClipboard TRUE

                       xlSheet.Paste xlSheet.Cells(Counter+2, 1)

                       'xlSheet.Paste xlSheet.Cells(Counter, 1)

                 End If

                 'Fieldz.Select valz(z) 'vSelectedItemy

                

                 Next

      '       ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard TRUE

      '       XLSheet.paste

             xlSheet.name = valy(y)

             Fieldy.Clear       

             next

       

       

             

      Fieldy.Clear

      end sub