Linking Excel to Access DB

[KC21supra]

Hello all,

So i was given a report in excel and a copy of a database from a coworker who was recently let go. The excel file is just a couple of sheets that has a pivot table in each of them. However, these pivots are linked to a table in the Access database. When i went in to the report to refresh the pivots, it is trying to connect to my old coworkers database instead of the copy that i own. Does anyone know how to update the excel file so that it takes from my copy of the Access DB and not my coworkers old one?

Thanks in advance,
KC

 

[GaryPanic]

you need to do it from the excel end
after that I am lost ....

 

[boblarson]

Which version of Excel and Access?

 

[boblarson]

Got this from a post on Mr. Excel:
http://www.mrexcel.com/forum/showthread.php?t=293796

First go to the VBA window in Excel and paste this into the Immediate Window:

debug.print activesheet.pivottables(1).pivotcache.connection

Then hit enter and it should return a string. Modify the path to the database in that string and then use this below (replacing "edited string" with the string you edited) and hit enter:

activesheet.pivottables(1).pivotcache.connection="edited string"

 

[malaydash]

Got this from a post on Mr. Excel:
http://www.mrexcel.com/forum/showthread.php?t=293796

First go to the VBA window in Excel and paste this into the Immediate Window:

debug.print activesheet.pivottables(1).pivotcache.connection

Where is this Immediate window. I am also facing a similar problem. I opened the VBA window but was not sure where to paste the code. Please help.

 

[JANR]

the immediatewindow should be at the buttom of your screen when you are in the VBA editor.

If not the go to menu:

View -> Immediate Window

JR

 

[malaydash]

Thanks JANR

I got the immediate window. But can you please suggest what should be the code if I am linking Access Data to Excel File as "Table" & not as "Pivot Table", because when I am running the code it is showing the following error:-

Run-time error '1004'
Unable to get the PivotTables property of the Worksheet class

Any suggestion please

Thanks & Regards

 

[Rx_]

Immediate Window:
ActiveWorkbook.Connections(1).Name = "BE_ver2010_retire 2-7"

or to find out what it is:
? ActiveWorkbook.Connections(1).Name

B.T.W. in the Immediate Window the "?" is a shortcut for Debug.Print

Subroutine Version:

With ActiveWorkbook.Connections("BE_ver2010_retire 2-7")
  .Name = "BE_ver2010_retire 2-7"
  .Description = "Give it a name here"
End With
ActiveWorkbook.Connections("BE_ver2010_retire 2-7").Refresh

See Attachment:
-point at the first cell in you datarange
-Choose DATA menu
-choose Properties
-Select search - choose 2nd tab on next property