Help with SaveAs prompt

[juggler]

Greetings and Happy New Year,
I have been perusing this forum the last few weeks trying to
address a specific problem. I have used the search function and found a
lot of good information, but nothing that addresses my requirements.
I am trying to find an efficient way to 1) prompt a user to enter
a file designation and 2) save the file in a specific folder. This is
the code I've written so far, minus all the steps leading to the
prompt...

ActiveWindow.SelectedSheets.PrintPreview

Dim Message, Title, Default, NAME
Message = "Enter the SHOE number(s) to identify the folder" 'Set
prompt
Title = "Folder Identification" 'Set Title
Default = "Latest Inspection" ' Set Default
'Display message, title, and default values.
NAME = InputBox(Message, Title, Default)


ChDir "H:\CMM REPORTS"
ActiveWorkbook.SaveAs Filename:="H:\CMM
REPORTS\V125D0\SHOES\Cavities\(NAME).xls", _
FileFormat:=xlText, CreateBackup:=False

I thought that by placing (NAME) in the SaveAs string that it would
take the information from the Message Box and input it to the Filename,
but that is not the case. Any suggestions?
Some interesting weather here in New England, this first working day
of ' 06....hope y'all are staying warm.

 

[Dave Peterson]

first msgbox won't return a string that you can use.

Maybe you could use:

Dim myFileName as String
myfileName = inputbox(Prompt:="What's the name?")

if trim(myname)="" then
exit sub
end if

....

ActiveWorkbook.SaveAs Filename:="H:\CMM REPORTS\V125D0\SHOES\Cavities\" _
myfilename & ".xls", _
FileFormat:=xlText, CreateBackup:=False

=======

Even better you may want to look at application.getsaveasfilename:


Dim myFileName As Variant

myFileName = "H:\CMM REPORTS\V125D0\SHOES\Cavities\suggestednamehere.xls"

myFileName = Application.GetSaveAsFilename(InitialFileName:=myFileName, _
filefilter:="Excel files,*.xls")

If myFileName = False Then
'user hit cancel
Exit Sub
End If

 

[juggler]

Thanks for the reply, Dave. Running both of these has generated some
unexpected results. Now I end up with a blank file (worksheet)
"myFileName" and, once I save the file, I have no idea where it
goes.......it is not in the location the path would indicate.
Also, once I enter data into the text box and click OK all my
information disappears.......I did not want to close the worksheet once
I saved it.
Does it matter that the file I am getting the data from is open? I
get a prompt that states it is being modified and I am asked if I want
to open it as a Read ONLY. I click yes and the macro works fine up
until the point where I am tryin to save.....then I loose everything.
I appreciate the input.

 

[Dave Peterson]

I think you made a mistake in your code.

It sounds like you did something like:

ActiveWorkbook.SaveAs Filename:="H:\CMM REPORTS\V125D0\SHOES\Cavities\" _
"myfilename" & ".xls", _
FileFormat:=xlText, CreateBackup:=False

With quotes around "myfilename".

If that's not it, you'll want to post the code you used.

And while you're testing, you can always use:

msgbox activeworkbook.fullname

to double check what you did.