Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Templaste SaveAs retains existing name
I have a template called PkLstTemplate1.xlt saved in the template
directory. A user opens this template by clicking on a sortcut to it. When the template opens it has a name of PkLstTemplate11 (no .xls extension) VB code in Workbook_Open prompts the user for information and successfully saves the file using the ActiveWorkbook.SaveAs command. For example say the code saved the file as XYZ.xls After the save has completed, the activeworkbook SHOULD be XYZ.xls, but it is still PkLstTemplate11. If the user clicks on File-Save as and gives it a new name the active workbook becomes this new name as normal. What am I missing? Why does the active sheet not take the name of the file it just saved? The code in this template used to work flawlessly every time. For some reason it stopped working one day! If I step through the code while the template is open it works correctly, but if I open the template by doubleclicking on the filename that will invoke the workbook_open code, it will do the saveas and revert back to the original filename PkListTemplate1 (no extension). Code as follows: Private Sub Workbook_Open() Dim strContainerNumber As String Dim strContainerShipDate As String Dim Slashcheck As Variant Dim CloseWorkbook As String On Error GoTo Errorhandler strContainerShipDate = InputBox("Enter container ship date. Use the format yyyy-mm-dd.") Slashcheck = InStr(strContainerShipDate, "/") If Slashcheck < 0 Then MsgBox ("Slashes (/) not allowed in date.") Workbook_Open End If strContainerNumber = InputBox("Enter the container number", "Save As") If Trim(strContainerNumber) < "" And Trim(strContainerShipDate) < "" Then ActiveWorkbook.SaveAs "\\sg-2k\server\" & strContainerShipDate & "_" & strContainerNumber & ".xls" MsgBox ("This file is saved as the file name " & strContainerShipDate & "_" & strContainerNumber & ".xls") End If Exit Sub Errorhandler: MsgBox ("You cannot use /,~,`,*, and several other characters in the date. You file is not saved. Please close the sheet and try again") Exit Sub End Sub Thanks, Brent |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - retains dates that are not in the source data | Excel Discussion (Misc queries) | |||
Formatting worksheets, existing and new, in existing workbooks | Excel Discussion (Misc queries) | |||
The Template Wizard retains the original location for my database | Excel Discussion (Misc queries) | |||
Template Save As retains existing name!! | Excel Programming | |||
Macro that retains password. | Excel Programming |