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
