Problem with Pivot Tables

[zaphod2003]

Dear All,

I have written a work sheet and macros which pulls in some data from an external system, wraps the whole thing around a pivot table and the proceeds to perform some analysis on this pivot table to generate a series of figures for management.

The problem I have is that I scan the Pivot table looking for the word "Grand Total" and then use this to establish the end of the pivot table (There are more than one pivot on the worksheet so I can't simply scan from the bottom upwards).

This works perfectly when I'm running the application on an English based worksheet, but fails miserably when it's running under a non english language.

My first thought was to identify what language that Excel was using, with something like this:

[FONT=Courier New]dim lCountryCode as long<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT]
[FONT=Courier New]lCountryCode = Application.International(xlCountryCode[/FONT][FONT=Courier New])<o:p></o:p>[/FONT]
<o:p>[FONT=Courier New] [/FONT]</o:p>
[FONT=Courier New]Returns a numeric value based on the language version being used:<o:p></o:p>[/FONT]
[COLOR=#282828][FONT=Courier New]Arabic 966 (Saudi Arabia)
Czech 42 ([URL="http://www.xtremevbtalk.com/showthread.php?t=268789"][COLOR=blue][FONT=Courier New]Czech Republic[/FONT][/COLOR][/URL])
Danish 45 (Denmark)
Dutch 31 (The Netherlands)
English 1 (The United States of America)
Farsi 98 (Iran)
Finnish 358 (Finland)
French 33 (France)
German 49 (Germany)
Greek 30 (Greece)
Hebrew 972 (Israel)
Hungarian 36 (Hungary)
Indian 91 (India)
Italian 39 (Italy)
Japanese 81 (Japan)
Korean 82 (Korea)
Norwegian 47 (Norway)
Polish 48 (Poland)
Portuguese (Brazil) 55 (Brazil)
Portuguese 351 (Portugal)
Russian 7 (Russian Federation)
Simplified Chinese 86 (People's Republic of China)
Spanish 34 (Spain)
Swedish 46 (Sweden)
Thai 66 (Thailand)
Traditional Chinese 886 (Taiwan)
Turkish 90 ([URL="http://www.xtremevbtalk.com/showthread.php?t=268789"][COLOR=blue][FONT=Courier New]Turkey[/FONT][/COLOR][/URL])
Urdu 92 (Pakistan)
Vietnamese 84 (Vietnam)<o:p></o:p>[/FONT][/COLOR]

And then have a variable which records this and uses a

SELECT...CASE

option to identify what Language Excel is running under.

The problem I have is two fold:

(1) Simply put, would this work? Would the variable

lCountryCode

return the true language of the users version of Excel (i.e. how the word Grand total would be displayed on the pivots), or would it check the regional settings on the users PC and assume that if the user was in Belgium that their Excel version might be in French or whatever?

(2) I have been googling for what the words Excel would use in other languages might be but have been unsuccessful so I'm struggling to code the next part.

My thought here was that there must be a better way that this but anyone got any suggestions on how I might identify where the bottom line of a pivot table is (i.e. where the grand total is) regardless of the language use.

Any suggestions welcome.

Thanks and Regards
John

 

[zaphod2003]

Anyone?

 

[Tom Urtis]

First, you might find this post relevant regarding country codes:
http://www.mrexcel.com/forum/showthread.php?t=161422

But really, that might not determine the actual language being used.

It seems to me, whatever "Grand Total" is represented by in spoken language string text display, it would exist in the leftmost, bottom most corner of the pivottable. This would tell you that location:

Sub testpt()
Dim LastRow&, FirstCol&
With ActiveSheet.PivotTables(1).TableRange2
LastRow = .Rows.Count + .Row - 1
FirstCol = .Column
End With
MsgBox _
"The pivot table's last row is " & LastRow & "." & vbCrLf & _
"The pivot table's first column is " & FirstCol & "." & vbCrLf & _
"The address of the first row and last column is " & Cells(LastRow, FirstCol).Address & ".", , "FYI"
End Sub

 

[Norie]

John

How exactly are you trying to find the 'Grand Total'?

A pivot table has various properties/objects that you can access through VBA.

I don't think there is one specifically for totals, could be wrong though, but you can certainly get the range of the pivot table.

And perhaps from that you could get what you are looking for, regardless of language.
Something like this perhaps.

Option Explicit
Sub GetGrandTotal()
Dim pvt As PivotTable
Dim pvtRng As Range
Dim GrdTotal
    Set pvt = ActiveSheet.PivotTables(1)
    
    Set pvtRng = pvt.TableRange1
    
    MsgBox pvtRng.Cells(pvtRng.Rows.Count, pvtRng.Columns.Count)
    
End Sub

 

[zaphod2003]

Thanks Norrie,

I was simply scanning the cells until I found the words Grand Total. I didn't realise you could do it this way!

If this works a thousand thanks! I've been racking my brains how to get around this problem

Regards
John

 

[zaphod2003]

Thanks Tom,

Your reply:

First, you might find this post relevant regarding country codes:
http://www.mrexcel.com/forum/showthread.php?t=161422

But really, that might not determine the actual language being used.

It seems to me, whatever "Grand Total" is represented by in spoken language string text display, it would exist in the leftmost, bottom most corner of the pivottable. This would tell you that location:

Sub testpt()
Dim LastRow&, FirstCol&
With ActiveSheet.PivotTables(1).TableRange2
LastRow = .Rows.Count + .Row - 1
FirstCol = .Column
End With
MsgBox _
"The pivot table's last row is " & LastRow & "." & vbCrLf & _
"The pivot table's first column is " & FirstCol & "." & vbCrLf & _
"The address of the first row and last column is " & Cells(LastRow, FirstCol).Address & ".", , "FYI"
End Sub

I guess gives me exactly what I am looking for, will let you know how I get on with it.