Use MsgBox to get filename and save .xls file to pre-defined path

  • Thread starter Thread starter Bill Burlison
  • Start date Start date
B

Bill Burlison

I would like to use a msgbox and ask a user to input a filename then,
when they hit ok, save the file as "C:\Folder1\Folder2\TheirInput.xls"

I would also like to test for no entry and alert then require entry
again if they don't put information in there.
 
You can't do that with a Msgbox; you need an Inputbox.

Retry:
Filename = Inputbox("Please enter the file name here")
if Filename<>"" and Filename<>"False" then
Activeworkbook.saveas "C:]FOlder1\Folder2\" & Filename & ".xls"
Else
goto Retry
Endif
 
This snippet will ask the user to provide a file name, check if for validity
as a string, prevent them from entering a blank space and shut down the
procedure if they choose to cancel the process. You can work with it to suit
your purposes.

Do
fileName = Application.InputBox("ENTER A BRIEF TITLE FOR THE
NEW FILE." & Chr(10) & Chr(10) & "PLEASE RESTRICT TO NO MORE THAN TWO
WORDS.", "NEW FILE NAME", Type:=2)
fileName = StrConv(fileName, vbProperCase)
newFile = YourPath & fileName & ".xls"
If fileName = False Or fileName = "" Or fileName = " "
Then
Response = MsgBox("YOU MUST ENTER A FILE NAME FOR
THE OR CLICK CANCEL TO EXIT THE PROGRAM.", vbRetryCancel, "INVALID ENTRY")
End If
If Response = 2 Then
TitleScrn
Exit Sub
End If
Loop While Response = 4
 
Use this one. The other one had a reference to a procedure that you don't
need and would probably cause a runtime error.

Do
fileName = Application.InputBox("ENTER_ A BRIEF TITLE FOR THE_
NEW FILE." & Chr(10) & Chr(10) & "PLEASE_ RESTRICT TO NO MORE THAN TWO_
WORDS.", "NEW FILE NAME", Type:=2)
fileName = StrConv(fileName,_ vbProperCase)
newFile = YourPath & fileName & ".xls"
If fileName = False Or fileName = "" Or fileName = " "_
Then
Response = MsgBox("YOU MUST_ ENTER A FILE NAME
OR CLICK_ CANCEL TO EXIT THE PROGRAM.", vbRetryCancel,_ "INVALID
ENTRY")
End If
If Response = 2 Then
Exit Sub
End If
Loop While Response = 4
 
Thanks you two. I have learned a bunch about msgbox in trying to do
this. Guess I can start on input boxes now. Your help will make the
start easier! Much asppreciated.
 
Back
Top