ActiveWorkbook.SAVEAS for *.xls

[Faraz A. Qureshi]

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.

 

[Jacob Skaria]

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

 

[Faraz A. Qureshi]

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.

 

[Jacob Skaria]

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]

Few points..

-- You can use pass the value direct to the argument like
Application.InputBox("SELECT",Type:=8).Text
-- You have not mentioned the error
--While generating error let me know what range have you selected
--What is the content or the cell value(s) of the range you have selected

If you can get back with the above info I can probably recreate the issue...

If this post helps click Yes

 

[Faraz A. Qureshi]

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!

 

[Jacob Skaria]

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