Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    78

    Question Unanswered: Invoke Save As box

    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

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    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
    ~

    Bill

  3. #3
    Join Date
    Dec 2004
    Posts
    78
    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

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  5. #5
    Join Date
    Dec 2004
    Posts
    78
    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

  6. #6
    Join Date
    Dec 2004
    Posts
    78

    Thumbs up

    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.

  7. #7
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •