Want to send current month data from pivot table into excel through macro

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

Hi All,

Can any one help on below requirement.

I want to export month wise data in excel.

Here my Date is Purcahse Receipt Date is in number format.

the max value of Purcahse Receipt Date='31-07-2017'

So I want to export Data from Jul 1st 2017 to Jul 31st 2017.

I have create variable vVehStock2=Date(([VLE.Purchase Receipt Date]))

Macro is working fine but only blank excel is coming

Sub VehicleStockTesting

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("vInc_Country33")

SET f2 = Doc.Variables("vCompanyName")

SET f3 = Doc.Variables("vVehStock2")

DIM Var1

Var1="VehicleStockandPipeline"

FieldName3 = f3.GetContent.STRING

Doc.Fields(FieldName3).Clear

SET Field3 = Doc.Fields(FieldName3).GetPossibleValues(100000)

For i=0 to Field3.Count-1

If Field3.Item(i).Text<=Date(Max([VLE.Purchase Receipt Date])) and Field3.Item(i).Text>=MonthStart(Date(Max([VLE.Purchase Receipt Date])) then

Doc.Fields(FieldName3).SELECT Field3.Item(i).Text

FieldName = f.GetContent.STRING

Doc.Fields(FieldName).Clear

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

For j=0 to Field.Count-1

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

FieldName2 = f2.GetContent.STRING

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:\BSC_Test\QVD\Old\"&Var1&"_"&Field.Item(j).Text&"_"&Field2.Item(k).Text&".xls"

' Path = "E:\Qllikview\BSC dashboard\Incadea Excel\"&Field.Item(i).Text&".xls"

' Path = "E:\Qllikview\BSC dashboard\" &Field.Item(i).Text &"_" &Field1.Item(j).Text &"_" &Field2.Item(k).Text &".xls"

set obj = Doc.GetSheetObject("CH2662")

obj.ExportBiff Path

end if

End Sub

topic Want to send current month data from pivot table into excel through macro in QlikView