Try the below...Note that there is a function to allow the user to select the
folder
Dim strFolder As String
Dim strFile as String
strFolder = GetSelectedFolder
strFile = Application.InputBox("SELECT", Type:=8).Text
If Trim(strFile) <> "" Then
ActiveWorkbook.SaveAs strFolder & strFile & ".xls", FileFormat:=-4143
ActiveWorkbook.SaveAs strFolder & strFile & ".xlsx", FileFormat:=51
End If
Function GetSelectedFolder() As String
Dim objShell As Object, objTemp As Object
Set objShell = CreateObject("Shell.Application")
Set objTemp = objShell.BrowseForFolder(0, "Select folder", 512)
If Not objTemp Is Nothing Then GetSelectedFolder = objTemp.Self.Path
End Function
PS: Alternatively you can use the below to display the save as dialogue
ActiveWorkbook.SaveAs Filename:=Application.GetSaveAsFilename. Thanks for
your comments; but those comparisons are overstated. Anyway happy to help you
with my very limited knowledge..
If this post helps click Yes
---------------
Jacob Skaria
"Faraz A. Qureshi" wrote:
> Yes!
>
> xlNormal was the keyword! Following code is working great saving the
> currently format in both the formats:
>
> Sub SAVEAS()
> Dim rng As Range
> Set rng = Application.InputBox("Select The Cell With The Desired Name.", _
> , , , , , , 8)
> ActiveWorkbook.SAVEAS "D:\New Folder\" & rng.Text & ".xlsx"
> ActiveWorkbook.SAVEAS "D:\New Folder\" & rng.Text & ".xls", _
> FileFormat:=xlNormal
> End Sub
>
>
> However, any idea/reference as to select the desired folder?
>
> Thanx again pal!!!!
>
> U simply are an MMMVP I have found to be most valuable after CHIP & MAX!
> --
> Best Regards,
>
> Faraz
>
>
> "Jacob Skaria" wrote:
>
> > Does the below work for you...
> >
> > Dim strFile as String
> > strFile = Application.InputBox("SELECT", , , , , , , 8).Text
> > If Trim(strFile) <> "" Then
> > ActiveWorkbook.SAVEAS "D:\New Folder\" & strFile & ".xls",
> > FileFormat:=xlNormal
> > End If
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "Faraz A. Qureshi" wrote:
> >
> > > Sorry Jacob,
> > >
> > > But again the error/failure message of Runtime error pops up. Sure would
> > > have used a String but it is the cell values on multiple sheets I want to
> > > use, instead of copying, inserting a new name and saving each sheet
> > > separately.
> > > --
> > > Best Regards,
> > >
> > > Faraz
> > >
> > >
> > > "Jacob Skaria" wrote:
> > >
> > > > Try
> > > >
> > > > ActiveWorkbook.SAVEAS "D:\New Folder\" & _
> > > > Application.InputBox("SELECT", , , , , , , 8).Text & ".xls", _
> > > > FileFormat:=xlExcel9795
> > > >
> > > > I would rather assign the string to a variable and then use the variable in
> > > > save as.
> > > >
> > > > If this post helps click Yes
> > > > ---------------
> > > > Jacob Skaria
> > > >
> > > >
> > > > "Faraz A. Qureshi" wrote:
> > > >
> > > > > I want to save a file in the 2003 version in the name of value of a cell.
> > > > > What might be wrong with:
> > > > >
> > > > > Sub SAVEAS()
> > > > > ActiveWorkbook.SAVEAS "D:\New Folder\" & _
> > > > > Range(Application.InputBox("SELECT", , , , , , , 8)).Text & ".xls", _
> > > > > FileFormat:=xlExcel9795
> > > > > End Sub
> > > > >
> > > > > Your comments and guidance shall be appreciated.
> > > > > --
> > > > > Best Regards,
> > > > >
> > > > > Faraz
Similar Threads
