Software

Ask something
Discussion Locked

Saving Values W/O Links in Excel

0 helpful
5 replies

by randy.etheridge

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Sirs:

I am experiencing the following problem.

In our company, there are server computers at various locations around
country that have real time data gathered from mechanical machines. I
can start a GUI ?front end? of the data gathering program on my remote
machine which basically maps and logs in, and allows me links to the
server data. While logged in, I can take an Excel ?97 spread sheet that
I have made with links to the data server and it updates in real time.
When I save the Excel Spread sheet, it will save the data at that
instant.

Problem:
I save the data under a date to have a report of that date. When I
reopen a saved file Excel ask me if I want to update. I Know to say no
or my report is wiped out with new data, and since this file is past on
to others some one along the line can do the same.

Question:
Can I save this spread sheet some way as to get only the values and not
the links as there are a hundred links making deleting links on at atime not an option?

Randy Etheridge
Randy.Etheridge@Williams.Com
Phone (318) 649-6432 Ext. 233

5 replies

by don christner

Randy,
copy and use the paste special function. When you paste special, a popup asks what you want to paste, select to paste the value. In this way you are pasting the actual numbers, not linking to the data.

Don

by sankulpune

Hello Randy,

Type the following code in a module in the VBA code window of 'Personal.xls'. This ensures that the procedure is available to all the workbooks.

The assumption here is that a linked cell contains a formula like this:
='c:\My Documents\[trial.xls]Sheet1'!$A$1

Sub SaveWoLinks()
' Code by SanganakSakha 3/11/01
' No data validation or error trapping
Dim intKounter As Integer

For intKounter = 1 To ActiveSheet.UsedRange.Count
If ActiveSheet.UsedRange.Cells(intKounter).HasFormula = True Then
If InStr(1, ActiveSheet.UsedRange.Cells(intKounter).Formula, "[") > 0 Then
ActiveSheet.UsedRange.Cells(intKounter).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
End If
End If
Next
ActiveWorkbook.Close
' Close the workbook
Application.Quit
' Close application
End Sub

Please note that you may need to do minor modifications depending on Excel settings.

With best compliments,

SanganakSakha,
Simple And Effective Solutions To Small Problems, No QuickFixes.

This discussion is closed to new replies.