topic Re: Need to export only current and previous year data in excel through Macro in QlikView

Need to export only current and previous year data in excel through Macro

tripatirao — Wed, 25 Nov 2020 16:16:04 GMT

Hi All ,

Can anybody suggest me on below requirement.

I need to generate excel dump for Current and Previous Year.

The below attached code generate data for all years.

The variable vFisc_Year=Fiscal_Year is my year field in application.

I tried changing variable to vFisc_Year=max(Fiscal_Year)-1 but it did not work.

Kindly assist...

Your help will be apopreciated!!!!!!!

Sub Export
on error resume next
SET Doc = ActiveDocument
'Assign Inv_year field to FieldName, because the report generates for each Inv_year
SET f = Doc.Variables("vFisc_Year")
SET f1 = Doc.Variables("vRegion")
SET f2 = Doc.Variables("vReport_Type")
FieldName = f.GetContent.STRING
FieldName1 = f1.GetContent.STRING
FieldName2 = f2.GetContent.STRING
Doc.Fields(FieldName).Clear

SET Field = Doc.Fields(FieldName).GetPossibleValues(100000)
For i=0 to Field.Count-1
Doc.Fields(FieldName).SELECT Field.Item(i).Text

Doc.Fields(FieldName1).Clear
SET Field1= Doc.Fields(FieldName1).GetPossibleValues(100000)
For j=0 to Field1.Count-1
Doc.Fields(FieldName1).SELECT Field1.Item(j).Text

Doc.Fields(FieldName2).Clear
SET Field2 = Doc.Fields(FieldName2).GetPossibleValues(100000)
For k=0 to Field2.Count-1
Doc.Fields(FieldName2).SELECT Field2.Item(k).Text
Doc.GetApplication.WaitForIdle
Path = "E:\STEEL\Data\Excel\PL_BS_Excel\" &Field.Item(i).Text &"_" &Field1.Item(j).Text &"_" &Field2.Item(k).Text &".xls"
set obj = Doc.GetSheetObject("CH230")
obj.ExportBiff Path
Next
Next
Next
ActiveDocument.Save
Application.Quit
End Sub

Re: Need to export only current and previous year data in excel through Macro

tripatirao — Fri, 22 Sep 2017 05:57:58 GMT

Hi All,

My Fiscal_Year has value from 2011 to 2018.

the above macro generate excel for all years. I need the macro code which generate only last 2 year data(2017 and 2018)

Regards

Tripati

Re: Need to export only current and previous year data in excel through Macro

tripatirao — Fri, 22 Sep 2017 06:47:25 GMT

pcammaert

stalwar1

Please help

Re: Need to export only current and previous year data in excel through Macro

dmac1971 — Fri, 22 Sep 2017 09:56:59 GMT

How about applying a filter using the Macro?

activedocument.fields("Fiscal_Year").select "2017"

Or Something similar?

Re: Need to export only current and previous year data in excel through Macro

tripatirao — Mon, 25 Sep 2017 05:03:25 GMT

Hi,

The code which you have given is hard coding.

we wants it as a dyanmic.

If nothing is selected, then previous year and current year will be selected automatically.

Regards

Tripati

Re: Need to export only current and previous year data in excel through Macro

tamilarasu — Tue, 26 Sep 2017 07:09:35 GMT

Hi Tripati,

Try this.

Sub Export

on error resume next

SET Doc = ActiveDocument

'Assign Inv_year field to FieldName, because the report generates for each Inv_year

SET f = Doc.Variables("vFisc_Year")

SET f1 = Doc.Variables("vRegion")

SET f2 = Doc.Variables("vReport_Type")

FieldName = f.GetContent.STRING

FieldName1 = f1.GetContent.STRING

FieldName2 = f2.GetContent.STRING

Doc.Fields(FieldName).Clear

SET Field = Doc.Fields(FieldName).GetPossibleValues(100000)

For i=0 to Field.Count-1

If Field.Item(i).Text = Trim(year(Now())) or Field.Item(i).Text = Trim(Year(Now())-1) then

Doc.Fields(FieldName).SELECT Field.Item(i).Text

Doc.Fields(FieldName1).Clear

SET Field1= Doc.Fields(FieldName1).GetPossibleValues(100000)

For j=0 to Field1.Count-1

Doc.Fields(FieldName1).SELECT Field1.Item(j).Text

Doc.Fields(FieldName2).Clear

SET Field2 = Doc.Fields(FieldName2).GetPossibleValues(100000)

For k=0 to Field2.Count-1

Doc.Fields(FieldName2).SELECT Field2.Item(k).Text

Doc.GetApplication.WaitForIdle

Path = "E:\STEEL\Data\Excel\PL_BS_Excel\" &Field.Item(i).Text &"_" &Field1.Item(j).Text &"_" &Field2.Item(k).Text &".xls"

set obj = Doc.GetSheetObject("CH230")

obj.ExportBiff Path

End If

ActiveDocument.Save

Application.Quit

End Sub

Re: Need to export only current and previous year data in excel through Macro

tripatirao — Mon, 09 Oct 2017 04:29:28 GMT

Hi Nagaraj,

Thanks your solution.... it did work for me!!

But I am facing one issue when I reload the macro manually it is generating excel for 2 years which is as per expectation, but when the same code is ran through batch file(Windows task scheduler) it is showing ambiguous behavior the code generate few files for year 2016,few files for 2017 ,few files for 2018.

I have verified Server time zone and date which is correct...!!!

Can you please suggest some solution on this...!!

Thanks,

Tripati

Re: Need to export only current and previous year data in excel through Macro

tamilarasu — Wed, 11 Oct 2017 02:02:06 GMT

Hi Tripati,

Sorry for the delay in my response. It's sad that the macro is not working properly in batch file. Could you try debugging by adding "Msgbox" statement like below? Let me know what you see for each loop.

Sub Export

on error resume next

SET Doc = ActiveDocument

'Assign Inv_year field to FieldName, because the report generates for each Inv_year

SET f = Doc.Variables("vFisc_Year")

SET f1 = Doc.Variables("vRegion")

SET f2 = Doc.Variables("vReport_Type")

FieldName = f.GetContent.STRING

FieldName1 = f1.GetContent.STRING

FieldName2 = f2.GetContent.STRING

Doc.Fields(FieldName).Clear

SET Field = Doc.Fields(FieldName).GetPossibleValues(100000)

For i=0 to Field.Count-1

If Field.Item(i).Text = Trim(year(Now())) or Field.Item(i).Text = Trim(Year(Now())-1) then

Msgbox "Current year: " & Trim(year(Now())) & " Previous Year: " & Trim(Year(Now())-1)

Doc.Fields(FieldName).SELECT Field.Item(i).Text

Doc.Fields(FieldName1).Clear

SET Field1= Doc.Fields(FieldName1).GetPossibleValues(100000)

For j=0 to Field1.Count-1

Doc.Fields(FieldName1).SELECT Field1.Item(j).Text

Doc.Fields(FieldName2).Clear

SET Field2 = Doc.Fields(FieldName2).GetPossibleValues(100000)

For k=0 to Field2.Count-1

Doc.Fields(FieldName2).SELECT Field2.Item(k).Text

Doc.GetApplication.WaitForIdle

Path = "E:\STEEL\Data\Excel\PL_BS_Excel\" &Field.Item(i).Text &"_" &Field1.Item(j).Text &"_" &Field2.Item(k).Text &".xls"

set obj = Doc.GetSheetObject("CH230")

obj.ExportBiff Path

End If

ActiveDocument.Save

Application.Quit

End Sub

You should see message box like below