+ Reply to Thread
Results 1 to 5 of 5

Prompt for file name

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    09-08-2010
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    125

    Prompt for file name

    Hi, The code below will save a xlsm file as a xlsx file using the same name. I need it to prompt me with the name highlighted in blue so I can change it slightly if needed before saving.

    Can anyone help, thanks in advance.


    Sub NewFile()
    '
    ' NewFile Macro
    '
    ' Keyboard Shortcut: Ctrl+n
    '
        Dim sName As String
        
        With ThisWorkbook
            If Not .Saved Then Exit Sub
            sName = .FullName
            sName = Left(sName, InStrRev(sName, ".")) & "xlsx"
            Application.DisplayAlerts = False
            .SaveAs FileName:=sName, FileFormat:=xlOpenXMLWorkbook
        End With
    End Sub
    Last edited by ccpsc; 05-09-2012 at 03:34 AM.

  2. #2
    Forum Contributor
    Join Date
    12-28-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    280

    Re: Prompt for file name

    You could just use the Application.FileDialog to do this e.g:

     With Application.FileDialog(msoFileDialogSaveAs)
            .InitialFileName = ThisWorkbook.FullName
            If .Show = -1 Then
                .Execute
            Else
                Exit Sub
            End If
    End With
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  3. #3
    Forum Contributor
    Join Date
    09-08-2010
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Prompt for file name

    Thanks Firefly that worked. What code do I need to turn off file type warning.

  4. #4
    Forum Contributor
    Join Date
    09-08-2010
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Prompt for file name

    Thanks Andy that did it.

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,462

    Re: Prompt for file name

    You need to use the GetSaveasFilename dialog.

    Sub X()
        Dim sName As String
        Dim vntFilename As Variant
        
        With ThisWorkbook
            If Not .Saved Then Exit Sub
            sName = .FullName
            sName = Left(sName, InStrRev(sName, ".")) & "xlsx"
            vntFilename = Application.GetSaveAsFilename(sName, "Excel Workbook (*.xlsx),*.xlsx")
            If Not vntFilename = False Then
                Application.DisplayAlerts = False
                .SaveAs Filename:=vntFilename, FileFormat:=xlOpenXMLWorkbook
            End If
        End With
    End Sub
    Cheers
    Andy
    www.andypope.info

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1