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
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
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
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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![]()
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.![]()
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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]
![]()
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]
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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]
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.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables