Help with SAVEAS
 

I need someones urgent help. Below is what i am trying to accomplish.
I want to send one of the sheets in the excel to a specific email
address. The sheet name should be constant. So I was saving the sheet
first as xyz.xls and then sending this excel. I want to give option to
the user to select the filepath.

I tried using GetSaveAsFilename but in this i want to hide the cancel
button and not let the user edit the filename. May I request someone
to help.

Thank you.

Help with SAVEAS
 

Uthra,

Try this code, below.

HTH,
Bernie
MS Excel MVP


Option Explicit

Sub PickFolder()
Dim FolderChoice As Variant
Dim Chosen As Boolean
Dim FileName As String
FileName = "This is the file to email"

Chosen = False
While Not Chosen
FolderChoice = BrowseForFolder
If VarType(FolderChoice) < vbBoolean Then Chosen = True
Wend

'Then use SaveAs like this
FileName = FolderChoice & "\" & FileName & ".xls"
Msgbox "I will now save " & FileName & "!!!!"
ActiveWorkbookSaveAs FileName

End Sub

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level

Dim ShellApp As Object

'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0

'Destroy the Shell Application
Set ShellApp = Nothing

'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename. All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select

Exit Function

Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False

End Function




wrote in message
...
I need someones urgent help. Below is what i am trying to accomplish.
I want to send one of the sheets in the excel to a specific email
address. The sheet name should be constant. So I was saving the sheet
first as xyz.xls and then sending this excel. I want to give option to
the user to select the filepath.

I tried using GetSaveAsFilename but in this i want to hide the cancel
button and not let the user edit the filename. May I request someone
to help.

Thank you.

Help with SAVEAS
 

Dear Uthra

Another way is to use Application.FileDialog() option..The below code allows
to select a folder and you can validate the selection. Alternatively if you
are looking for Save as use Application.FileDialog(msoFileDialogSaveAs)

Dim strFolderPath As String
' Open the file dialog
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
If .SelectedItems.Count = 0 Then MsgBox "Please select a folder": Exit Sub
strFolderPath = .SelectedItems(1)
End With


If this post helps click Yes
---------------
Jacob Skaria


" wrote:

I need someones urgent help. Below is what i am trying to accomplish.
I want to send one of the sheets in the excel to a specific email
address. The sheet name should be constant. So I was saving the sheet
first as xyz.xls and then sending this excel. I want to give option to
the user to select the filepath.

I tried using GetSaveAsFilename but in this i want to hide the cancel
button and not let the user edit the filename. May I request someone
to help.

Thank you.

Help with SAVEAS
 

Thanks a million.... This really helped and the code worked :)