Consulting

Results 1 to 8 of 8

Thread: save as

  1. #1

    save as

    how can in "commandbutton click" can call the save as option and filter with *.xlsm and after put de name of the file in userform3 textbox?

    Thanks in advance

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hi there, welcome to the board!

    Take a look at this code...

    [vba]Private Sub YourCommandButton_Click()

    Dim vName As Variant
    Dim sName As String
    Dim sPath As String
    Dim sFilter As String

    sFilter = "Excel File's (*.xlsm), *.xlsm"

    vName = Application.GetSaveAsFilename(, sFilter)

    If TypeName(vName) = "Boolean" Then

    'user pressed cancel

    Else

    sName = Right(vName, VBA.Len(vName) - VBA.InStrRev(vName, Application.PathSeparator))
    sPath = VBA.Left(vName, VBA.Len(vName) - VBA.Len(sName))

    Me.YourTextBox.Value = sPath & sName

    End If

    End Sub[/vba]

    Some things to note about this code. The routine is tied to the userform object named "YourCommandButton". Change as desired. The filter is fully customizable, it's just important you leave the comma between the file extensions in the parenthesis and those that follow - adding other file extensions can be done but must be semi-colon separated and done in unison. For example if you wanted to add xlsb file formats the filter would then become "Excel File's (*.xlsm; *.xlsb), *.xlsm; *.xlsb". If, however, you have files which are read-only, you'll get an error generated.

    You will also notice I have two variables set to grab the file name and another to grab the file path. These would be useful if you wanted to open the file, as you should first check if the file is open (using the name), and if it isn't open then go ahead and open it (using the path and name).

    Edit: Also, the text box in the code I named "YourTextBox", you would need to change that to the name of your textbox.

    HTH

  3. #3
    i've put your code and worked almost perfect:

    [vba]
    Private Sub CommandButton3_Click()
    Dim vName As Variant
    Dim sName As String
    Dim sPath As String
    Dim sFilter As String
    sFilter = "Excel File's (*.xlsm), *.xlsm"
    vName = Application.GetSaveAsFilename(, sFilter)
    If TypeName(vName) = "Boolean" Then
    'user pressed cancel
    Else
    sName = Right(vName, VBA.Len(vName) - VBA.InStrRev(vName,
    Application.PathSeparator))
    sPath = VBA.Left(vName, VBA.Len(vName) - VBA.Len(sName))
    form_principal.Produto.Value = sName
    End If
    End Sub
    [/vba]

    but I wanted the dialog box of the saves as to be in a specified folder
    and in the text box appears the extension of the file... just wanted the name... sorry to be a pain in the ass

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    To specify a starting path, add this line right before the "Application.GetOpenFilename" command at the top...
    [vba] ChDir "C:\PathGoesHere\"[/vba]
    If you don't want the file extension to appear, add a line of code right after the "sName" line of code. Here are the two lines of code, the second one is new, so don't double the first one, it's only to show you where it goes...
    [vba] sName = VBA.Right(vName, VBA.Len(vName) - VBA.InStrRev(vName, Application.PathSeparator))
    sName = VBA.Left(sName, VBA.InStrRev(sName, ".") - 1)[/vba]

    Not a pain in the butt at all. Hope this helps.

  5. #5
    sorry but it does not go to the directory and does not filter...

    [VBA]

    Private Sub CommandButton3_Click()
    Dim vName As Variant
    Dim sName As String
    Dim chdir As String
    Dim sFilter As String

    sFilter = "*.xlsm"
    chdir = "F:\Alualpha sync\Ficha de Produto\Template\testes de save"
    vName = Application.GetSaveAsFilename()

    If TypeName(vName) = "Boolean" Then
    'user pressed cancel
    Else
    sName = VBA.Right(vName, VBA.Len(vName) - VBA.InStrRev(vName, Application.PathSeparator))
    sName = VBA.Left(sName, VBA.InStrRev(sName, ".") - 1)

    form_principal.Produto.Value = sName
    form_principal.Show
    End If
    End Sub
    [/VBA]


  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    You would need to add a line of code right above the "ChDir" if you're going across different drives...

    [vba]ChDrive "F:"[/vba]

    Also, your filter isn't setup like I had it, it should be something like this...

    [vba]sFilter = "TITLE (*.xlsm), *.xlsm"[/vba]

  7. #7
    Sorry but is still does not work...
    is goes to the last folder viewed in explorer and also does not save anything, it stays in the same excel file thas as the initial code...

    I have the code in a module, is that ok?

    [vba]
    Option Explicit
    Sub SaveDocumentAs()
    Dim vName As Variant
    Dim sName As String
    Dim chdir As String
    Dim sFilter As String
    sFilter = "TITLE (*.xlsm), *.xlsm"
    ChDrive "F:"
    chdir = "F:\Alualpha sync\Ficha de Produto\testes_saves\"
    vName = Application.GetSaveAsFilename()
    If TypeName(vName) = "Boolean" Then
    'user pressed cancel
    Else
    sName = VBA.Right(vName, VBA.Len(vName) - VBA.InStrRev(vName, Application.PathSeparator))
    sName = VBA.Left(sName, VBA.InStrRev(sName, ".") - 1)
    form_principal.Produto.Value = sName
    End If
    End Sub[/vba]

  8. #8
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Well if you want to save, you should specify that. I didn't pick that up from your initial post. So you'd have to add that line. I'm not entirely sure what you're doing, so I can't really give you more. Are you changing the path/name in the text box? Are you wanting to save that? Please explain your process.

Posting Permissions

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