Save values and formatting but not links

[cphenley]

I would like to save a file with multiple sheets while retaining th
current values and format, but not the links so I can minimize fil
storage space and load time.

I wrote an add in that creates a new sheet. Is is possible to modif
this to save in a file format without links, or is there another way
can approach this?

Thanks in advance for any input.

Sub SelectNewFile()
Dim MyFileName As String, Fdate As String, NewName As String, awb A
Workbook, BackupFileName As String, i As Integer, OK As Boolean
If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
Set awb = ActiveWorkbook
If awb.Path = "" Then
Application.Dialogs(xlDialogSaveAs).Show
Else
BackupFileName = awb.Name
OK = False
On Error GoTo NotAbleToSave
If Dir("I:\Pyro-Process reports\SIC-2\" & BackupFileName
<> "" Then
Kill "I:\Pyro-Process reports\SIC-2\" & BackupFileName
End If
With awb
Application.StatusBar = "Saving this workbook..."
.Save
Application.StatusBar = "Saving this workboo
backup..."
.SaveCopyAs "I:\Pyro-Process reports\SIC-2\"
BackupFileName
OK = True
End With
End If
NotAbleToSave:
Set awb = Nothing
Application.StatusBar = False

If Range("B4").Value = "" Then
Range("B4").Value = InputBox("Please enter the new date"
"Date")
Else
Range("B4").Value = Range("B4").Value + 1
End If
Range("B4").NumberFormat = "mm-dd-yy;@"
Fdate = Format(Range("B4"), "mm_dd_yy")
MyFileName = "SIC_2_" & Fdate & ".xls"

ActiveWorkbook.SaveAs "I:\Pyro-Process reports\SIC-2\" & MyFileNam
' Save as new file using date
Calculate
' Refresh data


End Su

 

[Jim Rech]

You forgot to mention what version of Excel you have. If it's Excel 2002 or
2003 you can use the Workbook BreakLink method to break links to other
files. You can check this out in VB Help.

Otherwise, there no file format that eliminates links. I guess you'd have
to a Find, perhaps searching for ":\" and change each cell found to its
value. I don't have any code that does this though.

--
Jim Rech
Excel MVP

|I would like to save a file with multiple sheets while retaining the
| current values and format, but not the links so I can minimize file
| storage space and load time.
|
| I wrote an add in that creates a new sheet. Is is possible to modify
| this to save in a file format without links, or is there another way I
| can approach this?
|
| Thanks in advance for any input.
|
| Sub SelectNewFile()
| Dim MyFileName As String, Fdate As String, NewName As String, awb As
| Workbook, BackupFileName As String, i As Integer, OK As Boolean
| If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
| Set awb = ActiveWorkbook
| If awb.Path = "" Then
| Application.Dialogs(xlDialogSaveAs).Show
| Else
| BackupFileName = awb.Name
| OK = False
| On Error GoTo NotAbleToSave
| If Dir("I:\Pyro-Process reports\SIC-2\" & BackupFileName)
| <> "" Then
| Kill "I:\Pyro-Process reports\SIC-2\" & BackupFileName
| End If
| With awb
| Application.StatusBar = "Saving this workbook..."
| Save
| Application.StatusBar = "Saving this workbook
| backup..."
| SaveCopyAs "I:\Pyro-Process reports\SIC-2\" &
| BackupFileName
| OK = True
| End With
| End If
| NotAbleToSave:
| Set awb = Nothing
| Application.StatusBar = False
|
| If Range("B4").Value = "" Then
| Range("B4").Value = InputBox("Please enter the new date",
| "Date")
| Else
| Range("B4").Value = Range("B4").Value + 1
| End If
| Range("B4").NumberFormat = "mm-dd-yy;@"
| Fdate = Format(Range("B4"), "mm_dd_yy")
| MyFileName = "SIC_2_" & Fdate & ".xls"
|
| ActiveWorkbook.SaveAs "I:\Pyro-Process reports\SIC-2\" & MyFileName
| ' Save as new file using date
| Calculate
| ' Refresh data
|
|
| End Sub
|
|
| ---
|
|

 

[Dave Peterson]

If your links are in worksheet formulas and you can change all the formulas to
values, maybe just copy|paste special values would work for you (for each new
sheet).

And for those difficult to find links:
Get a copy of Bill Manville's FindLink program:
http://www.bmsltd.ie/MVP/Default.htm