PDA

View Full Version : Solved: Programmatically add code to Workbook_Open



moa
01-04-2007, 06:22 AM
I am copying one sheet to a new workbook and want to either suppress the "Update/Don't update" message whenever the new workbook is opened or find a way of copying the sheet so it looks exactly the same in the new Workbook but only shows the values (not linked).

Any suggestions?

Charlize
01-04-2007, 07:27 AM
Use the paste special feature.
PasteSpecial Paste:=xlPasteValues
Will paste only values and no formulas (ie. the resulting value of a formule). First you have to say what you want to copy. You can't copy and paste in one commandline with this (At least I believe it can't be done).

Charlize

moa
01-04-2007, 08:03 AM
Thanks, I need the sheet to look exactly the same (real purdy). Does paste special get rid of all formatting and such?

Might just paste the values into specific cells after copying the whole sheet over. Anyway, here's what I'm doing...


Private Sub btnSaveAs_Click()

Dim sPath As String
Dim dlgSaveAs As FileDialog
Dim fileName As Variant

sPath = ActiveWorkbook.Path

fileName = Application.GetSaveAsFilename(InitialFileName:=sPath _
& "\*.xls", Title:="Save Your Price Confirmation")

If fileName <> False Then
Sheets("Price Request").Copy
With ActiveWorkbook.Sheets(1)
.btnReturn.Visible = False
.btnSaveAs.Visible = False
.SaveAs fileName
.Parent.Close
End With
End If

End Sub


Also, is there a way to stop the "Update" dialog from popping up and have it not update? "application.AskToUpdateLinks = False" hides the dialog box but updates the links.

lucas
01-04-2007, 08:15 AM
Sub SaveSales()
Application.ScreenUpdating = False
Worksheets("Sales").Copy
ActiveWorkbook.SaveAs "F:\Temp\Sales.xls"
ActiveWorkbook.Close
Application.ScreenUpdating = False
End Sub

lucas
01-04-2007, 08:17 AM
In thisworkbook module I sometimes use this:
Private Sub Workbook_Open()
ActiveWorkbook.SaveAs FileName:="F:\Temp\BOM.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.Run "checkname"
End Sub

lucas
01-04-2007, 08:18 AM
the second saves the entire workbook. use the first in the workbook_open to just save the one sheet.

moa
01-04-2007, 08:24 AM
Don't think that's what I need but thanks.

I need to use the SaveAs dialog box.

moa
01-04-2007, 08:41 AM
The heading of this thread is misleading. When I wrote it I was thinking of adding code to the workbook_open procedure of the new workbook after creating it so that I could suppress the "Update" dialog box but I have changed my mind. I will mark this thread solved asI am going to try something else

lucas
01-04-2007, 09:44 AM
Saveas dialog:
[Sub ParseSaveAsName()
Dim sFileName As String
'Show the open dialog and parse the selected _
file name to the String variable "sFileName"
sFileName = Application.GetSaveAsFilename
'They have cancelled
If sFileName = "False" Then Exit Sub
ThisWorkbook.SaveAs sFileName
End Sub

moa
01-04-2007, 09:53 AM
Cheers lucas but I'm already doing that (see my second post).

Now I'm looking into pasting using pastespecial with Paste:=xlPasteValuesAndNumberFormats. I think this is what I need now, hopefully I will get it sorted so that I can keep the appearance of the sheet.

lucas
01-04-2007, 10:09 AM
With Cells
.Select
.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
will replace all formula with values.

moa
01-05-2007, 03:30 AM
Went with this 'cos it's all I could get to work how I wanted it to. Not sure if it's the prettiest code I have ever written.

Thanks for your time lucas and Charlize.

Private Sub btnSaveAs_Click()

Dim sPath As String
Dim fileName As String

sPath = ActiveWorkbook.Path + "\" + "abook.xls"
fileName = Application.GetSaveAsFilename( _
InitialFileName:=sPath, Title:="Save Your Price Confirmation")

If fileName <> "False" Then
Sheets("Price Request").Copy
With Sheets(1)
.Unprotect myPassword
.Range("A1:G67").Copy
.Range("A1:G67").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.btnReturn.Visible = False 'Hide the return to userform button
.btnSaveAs.Visible = False 'Hide the "Save This Sheet" button
.Protect myPassword
.SaveAs fileName
.Parent.Close
End With
End If
End Sub

Aussiebear
01-05-2007, 09:46 PM
Glen, Where is it pasteing to?

johnske
01-05-2007, 10:01 PM
It looks like you're only wanting to get rid of the formulas in the range A1 to G67.

Seeing it's such a small range you can do that more simply by replacing
.Range("A1:G67").Copy
.Range("A1:G67").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
with...
With .Range("A1:G67")
.Value = .Value
End With

moa
01-06-2007, 07:21 AM
Nice. Will use that.

moa
01-06-2007, 07:28 AM
AussieBear,

Its pasting to the new sheet in the new workbook. I copied the sheet, creating a new workbook, then I want to get rid of formulas from the sheet so I am pasting the range to itself minus formulas.

Did you have another idea? I just thought it was simpler than creating a new workbook and copy/pasting the old sheet to the new but I havnt even tried that.