Excel pivot table automation

[Ray Cacciatore]

I'm writing VBA code in Access that generates a pivot table in Excel. The
problem is that the datasource has more than 65535 lines so I cannot
programmatically copy the data into a separate worksheet and use that
worksheet as the pivot table's datasource.

So I decided to program it so that my code also generates a separate Access
file with a table that has the data for the pivot table's datasource. This
allows for unlimited data for the pivot table.

But the problem is that my code hard-codes the path to the data file and
Excel reads it as, say "C:\MyFolder\MyData.mdb"

Here's my partial code:
---------------------------------------------------
With objBook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array("ODBC;DSN=MS Access Database;DBQ=" &
db.Name & ";DefaultDir=" & Application.CurrentProject.Path &
";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array("SELECT * " & Chr(13) & "" & Chr(10) &
"FROM `" & Application.CurrentProject.Path & "\Effectifs_Données`.Effectifs
Effectifs")
.CreatePivotTable TableDestination:=objSheet2.Range("A4"),
TableName:="Table croisée"
End With
--------------------------------------

This works fine when the user opens the pivot table right away since both
the excel file and the data file are in the current folder.

Problem:
But what if my user takes both files and places them in another folder. The
hard-coded path is now invalid and the user cannot refresh the pivot table.

I need to replace the last line where it says
"Application.CurrentProject.Path". I need to change it so that Excel keeps
looking into the folder where the .XLS file is located no matter where it is
in a user's folder structure.

What is it?

Thanks in advance

Ray