Create Input Box / Save File As

**Smythe78** · 12-17-2009, 12:19 PM

Hi there. First post so bear with me!

I am looking for some macro code to create an Input box which will prompt the user to enter a specific date (not todays date so NOW function is not appropriate). This will in turn cause the file to be saved in a specific folder with the date becoming the name of the saved file.

Does this make sense? Any help greatly appreciated

**Leith Ross** · 12-17-2009, 01:29 PM

Hello Smythe78,

Ceud mìle fàilte!
Welcome to the Forum!

Are the files Excel workbooks? Can you give and example of the name before and after?

**Smythe78** · 12-17-2009, 02:42 PM

Hi Ross!

Thanks for reply. It is indeed Excel files I am working with. It uses just one sheet in the workbook. When I recieve the file it is called something along the lines of SEI Inspector Credit Report 17.12.2009 and I would like to save it as something along the lines of '17.12.2009 SEI Report'.

This is to be the beginning of a much larger macro but I thought I would ease myself in gently!

Is your name anything to do with the Leith here in Edinburgh?

**Leith Ross** · 12-17-2009, 09:06 PM

Hello Smythe78,

Sorry for the delay. The furnace broke last night and I have had to deal will the repair people coming and going all day. I am still not sure what you need. This macro will open the "SaveAs" dialog and let the user choose the folder and file. Have a look at this and let me know if this is close to what you are after.

As for my name, you're right. My family emigrated from Scotland about 150 years ago. The name has been passed down through the generations. Here in the states there about 4000 people with Leith as a first name or surname.

Sub RenameFile()

  Dim FileFilter As String
  Dim FileName As Variant
  
    FileFilter = "Workbooks (*.xla;*.xls;*.xlt),*.xla;*.xls;*.xlt," _
               & "Text Files (*.txt;*.csv),*.csv;*.txt,All Files (*.*),*.*"
    
    FileName = Application.GetSaveAsFilename(FileFilter:=FileFilter)
    If FileName = False Then Exit Sub
    
    'Other code to validate the new name and rename the file.
    
End Sub

**Smythe78** · 12-18-2009, 04:45 AM

Nae bother. You dont want a broken furnace at this time of year!

Thanks for that. That code works great but not quite what I am looking for.
To be more specific I suppose I would like an input box for user to enter date because I dont want to give the user a choice of what the file is called or where it is stored.
So for example I would like the Input box to say 'Please enter date of Report' and once the date has been entered in box and OK has been clicked , the macro would then go on to Save As in a pre-specified folder. (for purpose of example folder would be C:\Chambers-Smith\documnets\work)

Andy