Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
How about applying a filter using the Macro?
activedocument.fields("Fiscal_Year").select "2017"
Or Something similar?
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
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
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
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