topic Re: Macro Help in QlikView

Macro Help

Kushal_Chawda — Wed, 07 Aug 2013 06:36:02 GMT

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

Re: Macro Help

Wed, 07 Aug 2013 06:47:44 GMT

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

Re: Macro Help

qlikpahadi07 — Thu, 08 Aug 2013 10:18:51 GMT

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

ActiveDocument.UnlockAll

ActiveDocument.ClearAll true

end Sub