Saving a File

[glassishalfempty]

I would think that this would be easy to do, but I cannot figure it out. I have searched the boards as well, but I couldn't find the answer.

Basically what I am doing is a macro that copies info out of one workbook into a new workbook and doing some manipulation of that data. The problems comes when I want the user to save the new workbook. I want the user to (1) enter the name that the new workbook is to be save by; and (2) allow the user to choose the folder that they want to save it in (everyone's elses question was to limit the user to a specific folder).

The code below is what I am using. Entering the file name is OK (step #1 above), but as you can see it always saves the new workbook in the "M:\Offices\" folder (step #2). Actually, for Step #2, I just want to "lead" the user to this main folder ("Offices") and then make the user to choose one of the Offices' subfolders (the actual office names) that the file belongs in before saving? Thank you.

FileName1 = InputBox("Please input filename", "Filename")
ChDir "M:\Offices\"
ActiveWorkbook.SaveAs Filename:="M:\Offices\" & FileName1, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

 

[Andrew Poulsom]

Check out the GetSaveAsFilename method in VBA Help.

 

[glassishalfempty]

Thank you for your help, but I cannot get it to work properly. This is what I have

myFilename = InputBox("Please enter the filename in the standard format", "Filename")
myWorkbookToBeSaved = myFilename & ".xls"
fileSaveName = Application.GetSaveAsFilename(myWorkbookToBeSaved, "Micosoft Office Excel Workbook (*.xls), *.xls")

when I run the macro, the Save as Box box appears with my name from the Input box with the extension xls and the proper Save As Type. I choose my folder that I want to save in and press SAve. It appears to save, but what actually happnes is that nothing is saved. The open workbook still has the generic name (e.g., Book1) at the top and not the name from the Input box. It is also not in the folder I chose.

I am pretty sure that the issue is the "Micosoft Office Excel Workbook (*.xls), *.xls". I have done some googling and found various alternatives (e.g., "Excel (.xls), *xls"), but none work. I used the longer name in this example because that is what is in the actual Save As box when you do it manually from the File menu.

Where have I gone? Thank you in advance.

 

[ChrisM]

The GetSaveAs... command doesn't actually save anything, it just returns a file name. You need to use the .SaveAs command (from your first post) to actually save the workbook.

 

[Andrew Poulsom]

GetSaveAsFilename just prompts for a name. It doesn't actually save the file. You have to use what it returns in the SaveAs method. Example:

fileSaveName = Application.GetSaveAsFilename(myWorkbookToBeSaved, "Microsoft Office Excel Workbook (*.xls), *.xls")
If fileSaveName <> False Then
    ActiveWorkbook.SaveAs FilenamefileSaveName, FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False 
End If