Hi everyone!
I’m currently “translating” a macro that uses pivot tables from Excel 2003 to Excel 2007 and I’m having problems to handle dates in the format “dd mmm” due to the English-spanish translations of this particular format.
Backgrounds and problem:
1. Both versions of Excel have the same language settings:
Application.LanguageSettings.LanguageID(msoLanguageIDExeMode) = 1033 (English)
Application.LanguageSettings.LanguageID(msoLanguageIDHelp) = 1033 (English)
Application.LanguageSettings.LanguageID(msoLanguageIDInstall) = 3082 (Spanish)
Application.LanguageSettings.LanguageID(msoLanguageIDUI) = 1033 (English)
Application.LanguageSettings.LanguageID(msoLanguageIDUIPrevious) = 1033 (English)
Application.International(xlCountryCode) = 1 (English)
2. The original macro creates a Pivot Table with one RowField known as “Date” in the format “dd mmm”.
3. This format in the original macro handles the dates in spanish, this means that it is shown, for example, “21 Ene” for the date 21/01/2011 (corresponding to the spanish date “21 de Enero de 2011” instead of “21 Jan”, the format in English of date “January 21st, 2011”).
4. Once I have the Pivot Table the macro hides some dates by using an array “días(i)” containing the dates to hide.
5. In Excel 2007 by reviewing the contents of this array I can see that the dates are under the format “dd mmm” in Spanish (just as desired!), nevertheless when applying the routine:
For i = 14 To Cells(Rows.Count, 1).End(xlUp).Row - 7
With ActiveSheet.PivotTables("Summary2").PivotFields("Date")
.PivotItems(días(i)).Visible = False
End With
Next i
The macro returns the Run-time error ‘1004’: Unable to get the PivotItems property of the PivotField class. This is generated because the macro tries to hide the date in english, for example, “21 Jan” (January 21st) instead of “21 Ene” (“21 de enero” in Spanish) in spite of having the dates in Spanish in the array!!!!
Any clue about this problem?
Any help is highly appreciated!
Gerartus
I’m currently “translating” a macro that uses pivot tables from Excel 2003 to Excel 2007 and I’m having problems to handle dates in the format “dd mmm” due to the English-spanish translations of this particular format.
Backgrounds and problem:
1. Both versions of Excel have the same language settings:
Application.LanguageSettings.LanguageID(msoLanguageIDExeMode) = 1033 (English)
Application.LanguageSettings.LanguageID(msoLanguageIDHelp) = 1033 (English)
Application.LanguageSettings.LanguageID(msoLanguageIDInstall) = 3082 (Spanish)
Application.LanguageSettings.LanguageID(msoLanguageIDUI) = 1033 (English)
Application.LanguageSettings.LanguageID(msoLanguageIDUIPrevious) = 1033 (English)
Application.International(xlCountryCode) = 1 (English)
2. The original macro creates a Pivot Table with one RowField known as “Date” in the format “dd mmm”.
3. This format in the original macro handles the dates in spanish, this means that it is shown, for example, “21 Ene” for the date 21/01/2011 (corresponding to the spanish date “21 de Enero de 2011” instead of “21 Jan”, the format in English of date “January 21st, 2011”).
4. Once I have the Pivot Table the macro hides some dates by using an array “días(i)” containing the dates to hide.
5. In Excel 2007 by reviewing the contents of this array I can see that the dates are under the format “dd mmm” in Spanish (just as desired!), nevertheless when applying the routine:
For i = 14 To Cells(Rows.Count, 1).End(xlUp).Row - 7
With ActiveSheet.PivotTables("Summary2").PivotFields("Date")
.PivotItems(días(i)).Visible = False
End With
Next i
The macro returns the Run-time error ‘1004’: Unable to get the PivotItems property of the PivotField class. This is generated because the macro tries to hide the date in english, for example, “21 Jan” (January 21st) instead of “21 Ene” (“21 de enero” in Spanish) in spite of having the dates in Spanish in the array!!!!
Any clue about this problem?
Any help is highly appreciated!
Gerartus
