Saving Values

[Guest]

Hello. I have a template that I populate from another workbook using
formulas. As any template, when you double click the template it renames it
appends a 1 to the end of the filename and treats it as a .xls file. I need
code that will change all formulas to values and need to know where this code
goes in the template so that the forumlas in my template don't get
overwritten with values. I also need to know how to save this file with just
the values and no code behind.

Hope someone can help.

Cathy

 

[Tom Ogilvy]

In the beforesave event, check if the ThisWorkbook.Path = "". If it does,
this is a workbook. Now, do a SaveCopyAs to make a copy of that workbook
named as you wish (named as the workbook of record).

Open that copy and do a
cells.copy
and then
cells.pastespecial xlvalues
of all sheets in the workbook. then use code from Chip Pearson's site to
remove all code in the copy

http://www.cpearson.com/excel/vbe.htm

then save that copy as the workbook of record. Now you want to destroy
thisworkbook, so close it without saving

cancel = true
ThisWorkbook.Saved = True
ThisWorkbook.Close SaveChanges:=False

this would be the approach I would examine.

 

[Guest]

Hi Tom. Thanks once again for the response. I have a bit of trouble though
understanding the code for this. I have a template and when that is opened
it calls the open dialog box for the user to select a file to get values
from.. The user then selects a file from a default location and the template
grabs the values generated by the formulas. It is at this point when all the
values have been filled that I would like to run the GETSAVEASFILENAME
function to have the user save the file with no formulas or no code. That
way when they open it the next time it is simply a spreadsheet that they can
send as an attachment through e-mail. Can you help me out? I am going to
paste all my code so you can have a look....thanks again...hope you can help.

Cathy

Private Sub Workbook_Open()
Dim pathStr As String
pathStr = "K:\Individual Directories\Woodford, Cathy\Daily Report"

Cells(1, 1).ClearContents

Application.DisplayAlerts = True
UpdateLinks = xlUpdateLinksAlways


If ThisWorkbook.Path = "" Then
Call openfile


End If

'Call openfile
UpdateLinks = xlUpdateLinksAlways


End Sub

Private Sub openfile()

Dim sDailyReport As String
Dim vFileName As Variant


ChDrive "K"
ChDir "K:\Individual Directories\Woodford, Cathy\Daily Report"
fileToOpen = Application.GetOpenFileName("Excel Files (*.xls), *.xls")

If fileToOpen <> False Then
Workbooks.Open FileName:=fileToOpen
Else
MsgBox "User Clicked Cancel, Exiting"
Exit Sub
End If


ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMaximized

Sheets(1).Cells(1, 1).Value = Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\"))))
Sheets(2).Cells(1, 1).Value = Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\"))))
Sheets(3).Cells(1, 1).Value = Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\"))))
Sheets(4).Cells(1, 1).Value = Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\"))))
Sheets(5).Cells(1, 1).Value = Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\"))))


End Sub

'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
'Cancel As Boolean)

'Dim ws As Worksheet

'For Each ws In Worksheets
'With ws.Cells
'.Copy
'.PasteSpecial xlPasteValues
'End With
'Next ws

'Application.CutCopyMode = False

'End Sub

 

[Tom Ogilvy]

Since you don't have any sheet level code, you can do this:

Private Sub Workbook_Open()
Dim pathStr As String
pathStr = "K:\Individual Directories\Woodford, Cathy\Daily Report"

Cells(1, 1).ClearContents

Application.DisplayAlerts = True
UpdateLinks = xlUpdateLinksAlways


If ThisWorkbook.Path = "" Then
Call openfile


End If

'Call openfile
UpdateLinks = xlUpdateLinksAlways


End Sub

Private Sub openfile()

Dim sDailyReport As String
Dim vFileName As Variant
Dim fName as Variant
Dim bk as Workbook


ChDrive "K"
ChDir "K:\Individual Directories\Woodford, Cathy\Daily Report"
fileToOpen = Application.GetOpenFileName("Excel Files (*.xls), *.xls")

If fileToOpen <> False Then
set bk = Workbooks.Open( FileName:=fileToOpen)
Else
MsgBox "User Clicked Cancel, Exiting"
thisworkbook.close Savechanges:=False
Exit Sub
End If


ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMaximized

Sheets(1).Cells(1, 1).Value = Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\"))))
Sheets(2).Cells(1, 1).Value = Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\"))))
Sheets(3).Cells(1, 1).Value = Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\"))))
Sheets(4).Cells(1, 1).Value = Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\"))))
Sheets(5).Cells(1, 1).Value = Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\"))))

' code that updates sheets.

ThisWorkbook.Worksheets.copy
For each sh in Activeworkbook.worksheets
sh.copy
sh.PasteSpecial xlValues
Next
' get name for file
fName = Application.GetSaveAsfilename( _
filefilter:="Excel Files (*.xls), *.xls", _
title:="Select name for this file"
if fName = False then
bk.close
ActiveWorkbook.Close Savechanges:=False
thisWorkbook.Close SaveChanges:=False
else
ActiveWorkbook.SaveAs fName
bk.Close SaveChanges:=False
ThisWorkbook.Close SaveChanges:=False
End if
End Sub

 

[Guest]

Thanks for the code Tom....I'm getting an error when I run it though. The
error is
Run-time error '1004'
PasteSpecial method of Worksheet class failed.

Any ideas? Also, just a question about the code...why does it create 2 new
books? I can understand 1.

Thanks again...we'll get it working yet! :~)

Cathy

 

[Guest]

Tom, I figured it out with no GetSaveAsFilename box. When I used to do that
it would never save it where I said. It was like the Save As wasn't actually
saving. Any ideas on that one? I have it now so that it doesn't reproduce a
new book it automatically changes them to values in the .xls that is created
when you open a template. It would be nice if I could get the save as box to
come up with a predefined save as name and then all they have to do is press
save.

Thanks for the help. Let me know if you have any ideas on the save.

Cathy

 

[Tom Ogilvy]

For each sh in Activeworkbook.worksheets
sh.copy
sh.PasteSpecial xlValues
Next

should have been

For each sh in Activeworkbook.worksheets
sh.cells.copy
sh.cells.PasteSpecial xlValues
Next

 

[Guest]

My project is done now Tom. Thank you so much for your help and have a good
Christmas!

Cathy