Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with SaveAs prompt
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with SaveAs prompt
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.xl s" myFileName = Application.GetSaveAsFilename(InitialFileName:=myF ileName, _ filefilter:="Excel files,*.xls") If myFileName = False Then 'user hit cancel Exit Sub End If juggler wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with SaveAs prompt
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with SaveAs prompt
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. juggler wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
save prompt for user exit, but no save prompt for batch import? | Excel Discussion (Misc queries) | |||
Unexpected SaveAs prompt for workbooks with pivot tables | Excel Programming | |||
SaveAs without prompt | Excel Programming | |||
More help with SaveAs | Excel Programming | |||
Help with SaveAs | Excel Programming |