How to delete Connections from Excel

[erick-flores]

Hello all

I have a Workbook that has 4 pivot tables. Each PT has its own ODBC
connection.

I have a button that sends the workbook via email, but I want to
remove/delete all the connections from the PivotTables because I dont
want the email receiver to be able to refresh the data.

Any ideas? I am using Excel 2003.

Thanks in advance!

 

[Michael]

Rather than sending the original file, send a version where you have copied
and pasted values only.

 

[erick-flores]

Well....I am aware of that option. But I want to make this automatic
because I will be sending this file every month and I dont want to be
copy-paste every time. I know I can create a macro but there are some
functions in the worksheet that will get lost if I do that...thats why
the best option (I think) is just to send the file but get rid off the
connections. Thanks anyways.

 

[timmg]

Hello all

I have a Workbook that has 4 pivot tables. Each PT has its own ODBC
connection.

I have a button that sends the workbook via email, but I want to
remove/delete all the connections from the PivotTables because I dont
want the email receiver to be able to refresh the data.

Any ideas?    I am using Excel 2003.

Thanks in advance!

You can choose not to save the data with the Pivot Table - it's an
option in the wizard. The net effect is that the user has a snapshot
of the pivot table at the point you saved it.

If you want the user to have the option to manipulate the data you may
wish to either attach the data to the workbook or maybe create a
compainion db (such as Microsoft Access) that could allow the user to
control the pivot table while you control the data.

Good luck.

Tim Mills-Groninger

 

[erick-flores]

Well I put the following code in my send email button

With Destwb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False

just paste everything and that made the connections to go away. Kinda
like what Michael told me.

Thanks.

 

[Michael]

You can still do the copying and pasting values (By a sub-routine) and not
loose the formulas you want to keep, just set each section in ranges, some of
them wil paste values the others will paste formulas.