Excel 2003, how to change a Workbook Connection

dragon_1973

New Member
Joined
Mar 26, 2009
Messages
4
Hi All... I have an 2003 XLS file with a pivot table connected to a SQL server database. When the file was created the user created it with a normal un & pw. He has since left the company, and we would like to change this Excel to use Integrated Authentication.

In 2007, I can open the file, Click on data --> connections --> properties --> definition and edit the ADO for the connection... changing it to an SSPI connection. HOWEVER, I now cannot save this as Excel 97-2003 format or it gives a compatibility error with the pivot chart.

So, back on a machine running 2003, I want to do the same thing, but for the life of me I can't figure it out. What I can figure out how to do is create a new pivot chart creating a new connection, that is now integrated authentication... but doing this wipes out all the setup that has been done over the years to the existing pivot chart.

How can I update the connection for the data for the pivot chart? I can change the SQL, and everything else, but I can't seem to change the data connection! grrrrrr.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
This is the error that gets generated when I try to save from 2007:

<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=451 border=0><COLGROUP><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 16493" width=451><TBODY><TR style="HEIGHT: 38.25pt" height=51><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 1pt solid; BORDER-LEFT: black 1pt solid; WIDTH: 338pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" width=451 height=51>A PivotTable in this workbook exceeds former limits and will be lost if it is saved to earlier file formats. Only PivotTables that are created in Compatibility Mode will work in earlier versions of Excel.</TD></TR></TBODY></TABLE>

but... I can't figure out either, in 2007, how to create a PivotTable in "Compatibility Mode".

Thanks
 
Upvote 0
Wow... 3 days later and 36 pages in on the forum. No one has any hints on how I can resolve this issue? Or, is it sooo simple no one wants to point out out how silly I look. I can take it! :)

My problem (I think) is that in Excel 2003, I don't have the menu item for the workbook connections. Not sure where it's hiding, or how to get it.
 
Upvote 0
Figured it out... not elegant, but this method works. Save the file as XML, open the XML in a text editor (or xml editor like XML Marker), find the "Connection" node, and replace the ADO connection string with one that uses SSPI. Save the XML, open it in Excel then save as workbook again.
 
Upvote 0
Does not work for me.

Saving an XL03 WB as xml instead of xls strips out all VBA and lots of other stuff. This is not a solution.

I tried to alter the connection string using VBA, but it turns out that the "CommandText" property cannot be modified.

I need to change the http to https in many WBs. Any other ideas?

**** Penny
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top