Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
tripatirao
Creator II
Creator II

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

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

7 Replies
tripatirao
Creator II
Creator II
Author

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

tripatirao
Creator II
Creator II
Author

pcammaert

stalwar1

Please help

dmac1971
Creator III
Creator III

How about applying a filter using the Macro?

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

Or Something similar?

tripatirao
Creator II
Creator II
Author

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

tamilarasu
Champion
Champion

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

Next

Next


End If


Next

ActiveDocument.Save

Application.Quit

End Sub

tripatirao
Creator II
Creator II
Author

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

tamilarasu
Champion
Champion

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

Next

Next


End If


Next

ActiveDocument.Save

Application.Quit

End Sub


You should see message box like below

Capture.PNG