Error 1004 - item could not be found in OLAP cube when using variables instead of hard codes

Question

I need to copy data from a pivot table in an Excel file, which links to an external data source. The difficulty is to select the period in the pivot field.

The macro should start with an InputBox for the user to input the date, so that the macro can select the month for further handling.

YearMonth = InputBox("Input Year & Month of the report, e.g. 202007", "Input Year & Month")
yr = Left(YearMonth, 4)
mth = Right(YearMonth, 2)

      If mth = "01" Then longmth = "JAN"
      If mth = "02" Then longmth = "FEB"
      If mth = "03" Then longmth = "MAR"
      If mth = "04" Then longmth = "APR"
      If mth = "05" Then longmth = "MAY"
      If mth = "06" Then longmth = "JUN"
      If mth = "07" Then longmth = "JUL"
      If mth = "08" Then longmth = "AUG"
      If mth = "09" Then longmth = "SEP"
      If mth = "10" Then longmth = "OCT"
      If mth = "11" Then longmth = "NOV"
      If mth = "12" Then longmth = "DEC"

The name of the PivotField is "Year". This filter is for selecting the year, which breaks down into quarters, which then break down into months (as you can see in the pic below)

As I was not sure how to code this to select the correct month, I tried to record the Macro by selecting only "2020 AUG" for reference. Below is the code recorded:

    ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Time].[Year]"). _
        VisibleItemsList = Array("")
    ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Time].[Quarter]"). _
        VisibleItemsList = Array("")
    ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Time].[Month]"). _
        VisibleItemsList = Array("[Time].[Time].[Month].&[2020 AUG]")

I thought I could make a variable (ExactDate below) so that the selection criterion is based on the entry in the InputBox (e.g. 202008 --> Convert to "2020 AUG", and so on)

'Select the relevant month and year on the pivot table
    ExactDate = yr + " " + longmth
    
With ActiveSheet.PivotTables("PivotTable1")
    .PivotFields("[Time].[Time].[Year]").VisibleItemsList = Array("")
    .PivotFields("[Time].[Time].[Quarter]").VisibleItemsList = Array("")
    .PivotFields("[Time].[Time].[Month]").VisibleItemsList = Array("[Time].[Time].[Month].&[ExactDate]")

It throws

"Run-time error '1004':
The item could not be found in the OLAP Cube."

In the Debug, this code is highlighted

.PivotFields("[Time].[Time].[Month]").VisibleItemsList = Array("[Time].[Time].[Month].&[ExactDate]")

I replaced the variable ExactDate with hard code (any year and month such as the following):

.PivotFields("[Time].[Time].[Month]").VisibleItemsList = Array("[Time].[Time].[Month].&[2019 SEP]")

And it works.

The text is the same; the only difference is fixed value vs assigning a variable.

Answer 1

For the sake of answering the question of why double quotes and ampersand are required, here is my complete answer:

In VBA you declare variables by using the Dim statement (at least is the recommended way).

So in your case, this line should be present as a good practice:

Dim ExactDate as String

Then, literal strings should be surrounded by double quotes.

In your case, like this:

"[Time].[Time].[Month].&["

In order to concatenate two strings or a string and a variable you can use the ampersand sign &

So, finishing the example in your code, the string that is going to filter the VisibleItemsList is going to be:

Array("[Time].[Time].[Month].&[" & ExactDate & "]"

As ExactDate represents a string, concatenation is going to work.

Hope the explanation is clear.