Excel : popup a save as box

[AJ_UK]

Hi.
On my excel project, I am trying to create a macro which allows a user to save a spreadsheet.
WHat happens, is that i do not want the user saving the actual excel program (because it is a template), so I have created a 'Save' Macro, which copys the relevant information that they would want to save, and then creates a new workbook, and pastes the stuff in there.
Then, i want this workbook to popup a 'Save as' box.
THe reason why i can't do it, is because when i go to record a macro, it doesn't let me stop the macro when the 'save as' box is up. It only lets me stop it when i have either saved, or when I have clicked cancel.

So basically, i need the code to popup a save as box in excel, haha

Thanks for your help guys.

-AJ

[mattbrown]

If you create a new document from a template in Excel you can save that document directly without fear of the template being damaged. That is the whole point of templates.

I would imagine you could use workbook.save or a similar method to invoke the save dialog

[AJ_UK]

Yes Matt, you are right.

But thats not what I am talking about.
The whole reason I create a new workbook is so that I CAN save that easily, without saving over the template (i will have the template protected anyway).

But i want the user to be able to specify what they want to save the file as.
I'm basically a beginner in all this anyway, so I don't know how do anything.

I used your advice and Excel Help to discover ActiveWorkbook.Save
However, the problem with that, is that after I have created the new workbook, when the activeworkbook.save code is parsed, instead of showing the user the SAVE AS box, it automatically saves the workbook as something like book3.xls.
I want the user to be able to specify what they want to save it as, which obviously means the SAVE AS box.
So does anyone know how to do this?

Cheers,
AJ

[mattbrown]

Interesting

try this code

  fileSaveName = Application.GetSaveAsFilename( _
    fileFilter:="Excel Files (*.xls), *.xls")
  If fileSaveName <> False Then
    ThisWorkbook.SaveAs fileSaveName
  End If

[AJ_UK]

nice one matt.
solved it now
cheer.