Saving File with names from Text Box's

[Brian]

Is it possible to set the File Save as File Name from User Form Text Box Names?
Example: These are text Box Names.
"Spec TEO_No_1 CLLI_Code_1 CES_No_1 TEO_Appx_No_2 .xls"

If possible the save screen comes up with the name below already assigned,
but the user can pick the directory. I would like for the file name to look
something like this: Spec 2HCC201200 ATLNGACS 403711 00

I have the following code that Joel helped me with but I get a Compile
Error: Method or data member not found

' Save Engineering Spec 11 Control Button

Private Sub Save_Engineering_Spec_11_Click() (Error Message is Here)

Folder = "c:\Tech\"
Set bk = ThisWorkbook
bk.SaveAs Filename:=Folder & TEO_No_1.Value
bk.SaveAs Filename:=Folder & CLLI_Code_1.Value
bk.SaveAs Filename:=Folder & CES_No_1.Value
bk.SaveAs Filename:=Folder & TEO_Appx_No_2.xls.Value

End Sub

 

[joel]

I think I found one error

bk.SaveAs Filename:=Folder & TEO_Appx_No_2.xls.Value

You can't have a period in this line befroe the XLS.

Excel will not let you put a period in the Textbox Name. I just trie
and it gave me an error. Try the code without the last SaveA
statement.

You want to create a string like this


FName = "Spec " & TEO_No_1.vales & _
CLLI_Code_1.value & " " & _
CES_No_1.value & " " & _
TEO_Appx_No_2.value & ".xls"
bk.SaveAs Filename:=Folder & FName


Notice I put spaces between each of the entires. I have a space at th
end of "Spec

 

[Jacob Skaria]

You can use either one of the below two approaches

'Build the filename and assign that to a variable and use the
GetSaveAsFileName dialog to display the default filename and allow user to
browse the folder
Sub Macro1()
Dim strFile As String

strFile = "Spec " & TEO_No_1.Text & CLLI_Code_1.Text & _
CES_No_1.Text & TEO_Appx_No_2.Text & ".xls"

bk.SaveAs Filename:=Application.GetSaveAsFilename(strFile)
End Sub

OR

'The below approach allows the user to select the folder and do not allow to
change the filename. The below makes use of a function GetSelectedFolder() .

Sub Macro2()
Dim strFile As String, strFolder As String

strFile = "Spec " & TEO_No_1.Text & CLLI_Code_1.Text & _
CES_No_1.Text & TEO_Appx_No_2.Text & ".xls"
strFolder = GetSelectedFolder

bk.SaveAs Filename:=strFolder & "\" & strFile
End Sub

Function GetSelectedFolder() As String
Dim objShell As Object, objTemp As Object
Set objShell = CreateObject("Shell.Application")
Set objTemp = objShell.BrowseForFolder(0, "Select folder", ssfPROFILE)
If Not objTemp Is Nothing Then GetSelectedFolder = objTemp.Self.Path
End Function

 

[Brian]

Here is what I pasted into the code.

I am getting a Compile Error: Expected End Sub
---------------------------------------------------------
' Save Engineering Spec 11 Control Button

Private Sub Save_Engineering_Spec_11_Click() (Error Message is here)
Sub Macro1()
Dim strFile As String

strFile = "Spec " & TEO_No_1.Text & CLLI_Code_1.Text & _
CES_No_1.Text & TEO_Appx_No_2.Text & ".xls"

bk.SaveAs Filename:=Application.GetSaveAsFilename(strFile)

End Sub

 

[Brian]

Like this:

Compile Error: Expected End Sub

' Save Engineering Spec 11 Control Button
Private Sub Save_Engineering_Spec_11_Click() (Error Message)

Sub Macro1()
Dim strFile As String

FName = "Spec " & TEO_No_1.Value & _
CLLI_Code_1.Value & " " & _
CES_No_1.Value & " " & _
TEO_Appx_No_2.Value & "xls"

bk.SaveAs Filename:=Folder & FName

End Sub

 

[Jacob Skaria]

Dim strFile As String

strFile = "Spec " & TEO_No_1.Text & CLLI_Code_1.Text & _
CES_No_1.Text & TEO_Appx_No_2.Text & ".xls"

ActiveWorkbook.SaveAs Filename:=Application.GetSaveAsFilename(strFile)

 

[Jacob Skaria]

Brian, try

Private Sub Save_Engineering_Spec_11_Click()
Dim strFile As String

strFile = "Spec " & TEO_No_1.Text & CLLI_Code_1.Text & _
CES_No_1.Text & TEO_Appx_No_2.Text & ".xls"

bk.SaveAs Filename:=Application.GetSaveAsFilename(strFile)

End Sub