VBA How to use variables to extract pivot table data

[Matador_24]

Hello,

I am working on a code that extracts data from a pivot table.

When using this code, I get the value I want to obtain, basically I obtain the value of Account number 10003 for the period 3 of Year 2017 from a complex pivot table:

LngValue = ActiveSheet.PivotTables(1).GetPivotData("Amount", "Account Number", "10003", "Period Year", "2,016", "Period Month", "3")

Now, the account number will change depending on data and thus, I need to replace the 10003 by a variable in the code. However, I have an error on the syntax when using the variable LngAccount:

Dim LngValue As Long
Dim LngAccount As Long

LngAccount = 10003
MsgBox "xx" & LngAccount & "xx"
LngValue = ActiveSheet.PivotTables(1).GetPivotData("Amount", "Account Number", LngAccount, "Period Year", "2,016", "Period Month", "3")

Does anybody know how to use a variable with the above code??????
note that the result of the msgbox is xx10003xx, meaning that the value of LngAccount is 10003, I did it to test if the format was fine.
I have searched on line but I have not been able to find something similar,

Help please!

Thanks,

Luis

 

[Marcelo Branco]

Hi Luis

Are you sure the first formula worked?

LngValue = ActiveSheet.PivotTables(1).GetPivotData("Amount", "Account Number", "10003", "Period Year", "[COLOR=#ff0000]2,016[/COLOR]", "Period Month", "3")

I think Period Year should be "2016", not "2,016"

M.

 

[Matador_24]

Hello,

Yes, sure it worked, "2,016" is the format of the origin data base for the columns years, it is not ideal but it was set up that way and I do not need to modify it for now....

Thanks,

Luis

 

[Marcelo Branco]

I'm asking because i created a very simple pivot table and this worked perfectly for me (Period Year as Numbers)

Sub aTest()
Dim LngValue As Long
Dim LngAccount As Long

LngAccount = 10003
LngValue = ActiveSheet.PivotTables(1).GetPivotData("Amount", "Account Number", LngAccount, "Period Year", "2016", "Period Month", "3")
MsgBox LngValue
End Sub

Pivot table

	A​	B​	C​	D​
1​
2​
3​	Account Number​	Period Year​	Period Month​	Sum of Amount​
4​	10001​	2.016​	2​	10​
5​			3​	16​
6​	10002​	2.016​	2​	12​
7​			3​	18​
8​	10003​	2.016​	2​	14​
9​			3​	20​
10​	Grand Total​			90​
11​

M.

 

[Marcelo Branco]

Complementing my previous post: in my Regional Setting the . (dot) in 2.106 is thousands separators (not comma)

M.

 

[Matador_24]

Hello,

I tried with "2016" and still the error, it shows the error:
Run-time error '1004': Application-defined or object-defined error

It highlights that line of code. So, I wonder what the issue is if for you works fine..... what can you think of?



The pivot table is more complex, basically Account number is row and for columns I have year (2015, 2016 and 2017) and month (3,6,9,12) it is in the below format, thus slightly different than yours. I wonder if that makes a difference compared with yours?:

Sum of Amount			Period Year	Period Month
			2,015	2,015 Total	2,016		2,016 Total	2,017		2,017 Total	Grand Total
Account GL	Account Number	Account Name	12		3	12		3	6
10000 - Assets	10002	Asset - Cash	627,507.78	627,507.78	(133,762.24)	(39,504.92)	77,296.43	(102,026.70)	(101,441.51)	(203,468.21)	501,336.00
	10003	Asset - Accounts Receivable	15,894.87	15,894.87	(17,155.42)	832.42	(8,658.77)	(3,759.14)	(1,810.38)	(5,569.52)	1,666.58
	10004	Asset - Prepaid Expense	55,427.37	55,427.37	93,656.97	(6,789.58)	4,809.06	(164.44)	29,220.36	29,055.92	89,292.35
	10006	Asset - Building/Land	9,301,947.73	9,301,947.73	4,679.75	(186,662.25)	(1,011,891.22)	6,889.35	5,960.00	12,849.35	8,302,905.86
	10008	Asset - Accumulated Depreciation	(1,618,609.59)	(1,618,609.59)	(39,487.03)	154,436.70	35,975.64	(35,167.67)	(35,167.67)	(70,335.34)	(1,652,969.29)
10000 - Assets Total			8,382,168.16	8,382,168.16	(92,067.97)	(77,687.63)	(902,468.86)	(134,228.60)	(103,239.20)	(237,467.80)	7,242,231.50
20000 - Liabilities	20001	Liabilities - Accounts Payable	(71,152.00)	(71,152.00)	(203.40)	38,090.96	11,785.46	(7,040.08)	14,455.96	7,415.88	(51,950.66)
	20002	Liabilities - Deferred Income	(23,659.97)	(23,659.97)	(2,314.22)	(1,000.00)	(265.93)	(1,421.98)	(59.93)	(1,481.91)	(25,407.81)
	20003	Liabilities - Mortgage	(562,033.87)	(562,033.87)			562,033.87				0.00
	20004	Liabilities - Other Payable	(357,380.06)	(357,380.06)	5,553.13	2,157.89	36,305.96	4,297.67	5,759.59	10,057.26	(311,016.84)
20000 - Liabilities Total			(1,014,225.90)	(1,014,225.90)	3,035.51	39,248.85	609,859.36	(4,164.39)	20,155.62	15,991.23	(388,375.31)

<tbody>
</tbody>

Anything else you can think of? Could you do a test with a simpler table but with the above fields? (Year, month as columns and values as what I want to extract).


Thanks,

Luis

 

[Marcelo Branco]

Sorry, i have to leave now. Maybe someone else can help you

M.

 

[Matador_24]

Hello,

I think I might have found one part of the issue, I changed to

Dim LngAccount As [B]String
[/B]

I would think that the source data of that field is text not number as I thought. The issue is that I need value type as I will use also rules for '<than or="">' eventually that I will not be able to use with text. The issue is that it seems that for that code, the variable must be text and not value that is why i think It was showing me error when the account number as Long....

I am also surprised that the code worked for you if it was defined as Long.

How can make the code accept the Long value instead of string?</than>