Saving File with names from Text Box's
in [Programming]
|
Prev: Problem copying table data as a picture onto chartsheet
Next: Charts that are Off-Screen Disappear after update
From: Brian on 25 Dec 2009 22:04 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
From: joel on 25 Dec 2009 22:31 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/showthread.php?t=165141 [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
From: Jacob Skaria on 25 Dec 2009 22:45 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 >
From: Brian on 25 Dec 2009 23:14 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 > >
From: Brian on 25 Dec 2009 23:34
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/showthread.php?t=165141 > > [url="http://www.thecodecage.com"]Microsoft Office Help[/url] > > . > |