save as

[fgarcia90]

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

[Zack Barresse]

Hi there, welcome to the board!

Take a look at this code...

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

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

[fgarcia90]

i've put your code and worked almost perfect:


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


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 :)

[Zack Barresse]

To specify a starting path, add this line right before the "Application.GetOpenFilename" command at the top...
ChDir "C:\PathGoesHere\"
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...
sName = VBA.Right(vName, VBA.Len(vName) - VBA.InStrRev(vName, Application.PathSeparator))
sName = VBA.Left(sName, VBA.InStrRev(sName, ".") - 1)

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

[fgarcia90]

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



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


:(

[Zack Barresse]

You would need to add a line of code right above the "ChDir" if you're going across different drives...

ChDrive "F:"

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

sFilter = "TITLE (*.xlsm), *.xlsm"

[fgarcia90]

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?


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

[Zack Barresse]

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.