vba to save xlsm as xlsx and prompt for part of file name

[hinesgg]

I have code that saves my file as xlsx properly, but I need to modify the file name with user input each time.

' Save workbook as XLSX file and append date</SPAN></SPAN>
ActiveWorkbook.SaveAs Filename:="C:\Users\aurgaiha\Documents\Playground\Amanda\Daily VMI Report" & Format(Now, " mm-dd-yyyy"), FileFormat:=51</SPAN>

but I need to have the user modify the file name each time to include the time of the last database update.

The code above results in the filename Daily VMI Report 12-18-2013.xlsx but I need for the name to read Daily VMI Report 12-18-2013 as of 9am (or 12pm, or 2pm, or 4pm, etc.)

Can I include an Input Box to modify the file name before the file is saved?

 

[Andrew Poulsom]

Can't you just use?

Format(Now, "mm-dd-yyyy h AM/PM")

 

[hinesgg]

Can't you just use?

Format(Now, "mm-dd-yyyy h AM/PM")

Sorry but that won't work. The data is exported from a mainframe database, and I need to include the time of the last database update, not the time the xlsx file was created.

 

[Andrew Poulsom]

OK, try:

    Dim Answer As String
    Answer = InputBox("What time?")
'   Save workbook as XLSX file and append date
    ActiveWorkbook.SaveAs FileName:="C:\Users\aurgaiha\Documents\Playground\Amanda\Daily VMI Report" & Format(Now, " mm-dd-yyyy") & " " & Answer & ".xlsx", FileFormat:=51

 

[hinesgg]

Worked like a charm. Thanks for your help.

 

[DeeDawg]

Is there a similar solution that prompts for part of a file name to open files?

 

[hinesgg]

If you have a question, this site requires that you post it as a new question and not add it to this thread. I found that out the hard way

 

[DeeDawg]

OK. Thanks