Access Create Excel Pivot Table Data Source
 

I am currently using an Access Frontend with Tables to create several
Excel Pivot tables and charts. Everything is working great but I was
wanting to simplify the code a little if possible. Here is the code
snippit I am looking at changing:

MyDataSetConn = "ODBC;DSN=MS Access Database;" & _
"DBQ=" & datasetpath & "\" & datasetDB & ";" & _
"DefaultDir=" & datasetpath & ";" & _
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout=5)"

With newbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = MyDataSetConn
.CommandType = xlCmdSql
.CommandText = "SELECT " & _
"IPO_History_tbl.IpoDate, " & _ etc.

The Datasetpath and datasetDb are located in the Access file that this
code is running from. I don't like to hardcoat the paths. I do
suppose that I could get the file path but I would like to go one step
further. Can the .PivotCaches.Add, Point to itself? Why go through
the ODBC connection when the Data is in the current Access file.
Could I do something like this :

Get rid of the "MyDataSetConn =" section and

With newbook.PivotCaches.Add(SourceType:=internal)
.CommandType = xlCmdSql
.CommandText = "SELECT " & _
"IPO_History_tbl.IpoDate, " & _ etc.

Any help would be greatly appreaciated.

Thank you,

Darren