Macro Help

[Kushal_Chawda]

Hi ,

I have one chart. I want to export the chart in format : year_month_reportname.

for eg. for year 2012 it should create 12 excel files as 2012_jan_report..2012_feb_report..............

Thanks in advance

[#2]

Hi Kushal,

Check this Macro

Sub ExcelExpwReport

     set f = activedocument.Fields(YearMonth)

     if IsEmpty(f.GetSelectedValues) then

     set pv = f.GetSelectedValues

    'adds selected values if any

     else

     set pv = f.GetPossibleValues()

    'adds possible values if none selected

     end if

     set Array1 = f.GetNoValues

    'empty array

     for i = 0 to pv.count-1 'adds values from the first array

     Array1.Add

     Array1(i).Text = pv.item(i).Text

     Array1(i).IsNumeric = false

     'if text in your field

     next

    

     for i = 0 to Array1.count-1

     f.Select Array1(i).Text  

     FileName = Array1(i).Text & ".xlsx"

'     'Set the path where the excel will be saved

'     filePath = "C:\Test.xlsx"



     'Create the Excel spreadsheet

     Set excelFile = CreateObject("Excel.Application")

    

'     f_name=excelFile.GetOpenFilename("All Files (*.*),*.*",,"Select file",False)

'     If f_name="False" then

'     'user cancelled out of dialog box

'     Set excelFile=nothing

'      Exit sub

'     End If

'    'store file pathname in the file_pathname variable

'     ret=ActiveDocument.GetVariable("file_pathname").SetContent(f_name,false)

'     Set excelFile=nothing

    

     excelFile.Visible = true

     'Create the WorkBook

     Set curWorkBook = excelFile.WorkBooks.Add

     'Create the Sheet

     Set curSheet = curWorkBook.WorkSheets(1)

    

      curWorkBook.SaveAs Path & FileName

End Sub

[qlikpahadi07]

Hi Kushal,

This code will help you

Sub ExportReport

           SET zonelst = ActiveDocument.Fields("Region").GetPossibleValues

     For l=0 to zonelst.Count -1

                              ActiveDocument.Fields("Region").Select zonelst.Item(l).Text

                              ActiveDocument.Fields("Region").Lock

                              sName = zonelst.Item(l).text

                              Set vReportID = ActiveDocument.Fields("ReportID").GetPossibleValues

                        Set obj = ActiveDocument.GetSheetObject("CH24")

                              obj.ExportBiff cPDF_PATH&"\"&sName&"_" & replace(FormatDateTime(now,2),"/","")&".xls"

                              sExcel = cPDF_PATH&"\"&sName&"_" & replace(FormatDateTime(now,2),"/","")&".xls"

                        ActiveDocument.GetApplication.WaitForIdle(1000)

                               ActiveDocument.GetApplication.Sleep(1000)

                              ActiveDocument.Fields("Region").Unlock

                              ActiveDocument.Fields("Region").Clear

     NEXT

     ActiveDocument.UnlockAll

           ActiveDocument.ClearAll true

end Sub