Invoke Save As box

bhavesh78 · #1 (**permalink**) 02-22-05, 18:29

I have a standard excel template which is a read-only file (with company logo etc.). All excel worksheets are based on this basic template.
When a buttom is pressed on a form it opens this template. How can I have excel to popup the Save As box as soon as the templete is opened. I know that the code should be in workbook_open, but what should I write to invoke the Save As box.

Thanks.

~BS

savbill · #2 (**permalink**) 02-22-05, 21:13

Quote:

Originally Posted by bhavesh78

I have a standard excel template which is a read-only file (with company logo etc.). All excel worksheets are based on this basic template.
~BS

Pretty simple really. Just set the macro to run of your button to this code. One thing to note: You should save the actual template as a template File with the '.xlt' extension. You must save it through the Excel Save dialog to do this (just changing the name won't make it a template) Once in a template you do not need to explicitly open it in read only. Just use the ' Workbooks.Add Template' method. It's better to do this way because if the user cancels the saveas dialog without saving when they go to save the file it will promt for a name to save to, just like saving a new workbook will do.

Code:

Sub OpenTemplateSave()
  Dim fPath As String
  Dim fFile As String
  
    fPath = Application.TemplatesPath
    fFile = "CompanyInvoice0.xlt"
      
    ChDir "C:\Documents and Settings"
    
          
    Workbooks.Add Template:=fPath & fFile
    Application.GetSaveAsFilename , , , "Save a New Company Invoice"
End Sub

bhavesh78 · #3 (**permalink**) 02-22-05, 22:00

The template I have has a .xnv extension (from some other add-in) and I cannot save it as .xlt file.

Code:

fileSaveName = Application.GetSaveAsFilename( _
    fileFilter:="nVision Layout Files (*.xnv), *.xnv")

I am using above to invoke Save As box, but I want to know how to Save the file once the box is invoked as the save button does not work.

Thanks.

~BS

DavidCoutts · #4 (**permalink**) 02-23-05, 04:45

GetSaveAsFilename only returns the filepath and doesn't actually save anything

youll need to invoke the saveas method of the workbook but i have no idea what your FileFormat would be for the nVision Layout Files

bhavesh78 · #5 (**permalink**) 02-23-05, 12:35

I tried the following code(Excel 2002):

http://blogs.msdn.com/vsto/archive/2.../15/59022.aspx

But code fails at--- If DialogResult.GetType() Is Type.GetType

Is this because of version difference of Excel or do I need to install some library?

Thanks.

~BS

bhavesh78 · #6 (**permalink**) 02-23-05, 14:45

I found a way to do this at http://www.tech-archive.net/Archive/...4-08/9595.html

Code is:

Code:

Sub test() 
Dim NameAk As String 
Dim NewName As Variant 


NameAk = Sheets(1).Name & ".xls" 
NewName = Application.GetSaveAsFilename( _ 
    InitialFileName:=ActiveWorkbook.Path & "\" & _ 
    NameAk, FileFilter:="Excel Workbooks (*.xls), *.xls") 


If NewName <> False Then 
    If Dir(NewName) <> "" Then 
        Select Case MsgBox("File Exists. Overwrite ?", vbYesNoCancel + 
vbQuestion) 
            Case vbYes 
                Application.DisplayAlerts = False 
                ActiveWorkbook.SaveAs Filename:=NewName, 
FileFormat:=xlWorkbookNormal 
                Application.DisplayAlerts = True 
            Case vbNo 
                Do 
                NewName = Application.GetSaveAsFilename( _ 
                    InitialFileName:=ActiveWorkbook.Path & "\" & _ 
                    NameAk, FileFilter:="Excel Workbooks (*.xls), *.xls") 
                    If NewName = False Then Exit Sub 
                Loop Until Dir(NewName) = "" 
                ActiveWorkbook.SaveAs Filename:=NewName, 
FileFormat:=xlWorkbookNormal 
            Case Else 
                Exit Sub 
        End Select 
    Else 
        ActiveWorkbook.SaveAs Filename:=NewName, 
FileFormat:=xlWorkbookNormal 
    End If 
End If 
End Sub

Thanks for help though.

~BS.

DavidCoutts · #7 (**permalink**) 02-24-05, 04:54

Quote:

Originally Posted by bhavesh78

I tried the following code(Excel 2002):

http://blogs.msdn.com/vsto/archive/2.../15/59022.aspx

But code fails at--- If DialogResult.GetType() Is Type.GetType

Is this because of version difference of Excel or do I need to install some library?

Thanks.

~BS

The blog you were looking at there was using visual studio tools with excel
id like to have a play with that myself but alas i dont have the cash too

if id realised all you wanted to save was your excel book i could of told you how to do that i was just unsure of the nVision Layout Files (*.xnv), type of file

All the best
Dave

Internet Services

Web Development

Digital Marketing

Consumer Tech