Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File with names from Text Box's
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File with names from Text Box's
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 tried and it gave me an error. Try the code without the last SaveAs 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 the end of "Spec " -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165141 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File with names from Text Box's
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 -- Jacob "Brian" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File with names from Text Box's
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 "Jacob Skaria" wrote: 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 -- Jacob "Brian" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File with names from Text Box's
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 "joel" wrote: 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 tried and it gave me an error. Try the code without the last SaveAs 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 the end of "Spec " -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165141 Microsoft Office Help . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File with names from Text Box's
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 "Brian" wrote: 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 "Jacob Skaria" wrote: 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 -- Jacob "Brian" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File with names from Text Box's
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 -- Jacob "Brian" wrote: 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 "Jacob Skaria" wrote: 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 -- Jacob "Brian" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seeing Text File Names when Saving Excel Files | New Users to Excel | |||
Seeing existing file names when saving | Excel Discussion (Misc queries) | |||
Look up Values from other file based on combo box's option | Excel Discussion (Misc queries) | |||
Saving file names in a different color | Excel Discussion (Misc queries) | |||
Saving file names in a different color | Excel Discussion (Misc queries) |