Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not copy code from template
Hello.
I have a template that has code attached to it as follows: Private Sub Workbook_Open() Application.DisplayAlerts = True UpdateLinks = xlUpdateLinksAlways Call openfile Application.AskToUpdateLinks = False 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 = 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 What this does is when the user double clicks the template icon, the template opens and then the open dialog opens so the user can select a file to take values from to populate the template. I don't want an open diaglog box to open when the user saves it as a .xls file, which means I have to get rid of the code somehow. Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not copy code from template
Private Sub Workbook_Open()
Application.DisplayAlerts = True UpdateLinks = xlUpdateLinksAlways if thisWorkbook.Path = "" then Call openfile Application.AskToUpdateLinks = False End Sub Might be what you want. -- Regards, Tom Ogilvy "Cathy W" wrote in message ... Hello. I have a template that has code attached to it as follows: Private Sub Workbook_Open() Application.DisplayAlerts = True UpdateLinks = xlUpdateLinksAlways Call openfile Application.AskToUpdateLinks = False 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 = 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 What this does is when the user double clicks the template icon, the template opens and then the open dialog opens so the user can select a file to take values from to populate the template. I don't want an open diaglog box to open when the user saves it as a .xls file, which means I have to get rid of the code somehow. Any suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not copy code from template
Thank you soooooo much! That worked wonderful! If you lived in Newfoundland,
I would bake you some cookies! Thanks again and have a great Christmas! Cathy "Tom Ogilvy" wrote: Private Sub Workbook_Open() Application.DisplayAlerts = True UpdateLinks = xlUpdateLinksAlways if thisWorkbook.Path = "" then Call openfile Application.AskToUpdateLinks = False End Sub Might be what you want. -- Regards, Tom Ogilvy "Cathy W" wrote in message ... Hello. I have a template that has code attached to it as follows: Private Sub Workbook_Open() Application.DisplayAlerts = True UpdateLinks = xlUpdateLinksAlways Call openfile Application.AskToUpdateLinks = False 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 = 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 What this does is when the user double clicks the template icon, the template opens and then the open dialog opens so the user can select a file to take values from to populate the template. I don't want an open diaglog box to open when the user saves it as a .xls file, which means I have to get rid of the code somehow. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening a template .xls with VBA code | Excel Programming | |||
Code to copy range vs Copy Entire Worksheet - can't figure it out | Excel Programming | |||
Adding new worksheet from template in code | Excel Programming | |||
Excel template-run code on open | Excel Programming | |||
Check my code to use template? | Excel Programming |