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: Jacob Skaria on 25 Dec 2009 23:49 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 > > >
From: Jacob Skaria on 26 Dec 2009 00:47
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 > > > |