asked on

inputbox for helping create a unique excel file name. vba

excel 2003 vba..

The following code helps create a unique file name using date and time.

What I need:

I need the information from an inputbox to go in front of the information below.for the filename. (less the date stuff)..

Thanks
fordraiders

Public Sub NewNameSaveAs()
Dim oldwkbk As String
Dim newdate As String
'Workbook.SaveAs ...
oldwkbk = ThisWorkbook.Name
Application.DisplayAlerts = False
'Use the Date
newdate = Format$(Date, "yyyy-mm-dd")
ActiveWorkbook.SaveAs "C:\Program Files\Dm\" & newdate & "_" & oldwkbk
Application.DisplayAlerts = True
End Sub

nutsch

Try this update to your code.

Thomas

Public Sub NewNameSaveAs()
   Dim oldwkbk As String
   Dim newdate As String, sInput as string
   'Workbook.SaveAs ...
   oldwkbk = ThisWorkbook.Name
   Application.DisplayAlerts = False
   'Use the Date
   newdate = Format$(Date, "yyyy-mm-dd")
sinput=inputbox("Enter file name here")
   ActiveWorkbook.SaveAs "C:\Program Files\Dm\" & newdate & "_" & sinput & "_" & oldwkbk
   Application.DisplayAlerts = True
End Sub

Open in new window

If you don't want the date at all, use:

Public Sub NewNameSaveAs()
   Dim oldwkbk As String,sInput as string
   'Workbook.SaveAs ...
   oldwkbk = ThisWorkbook.Name
   Application.DisplayAlerts = False
sinput=inputbox("Enter file name here")
   ActiveWorkbook.SaveAs "C:\Program Files\Dm\" & sinput & "_" & oldwkbk
   Application.DisplayAlerts = True
End Sub

Open in new window

Seaton007

Here is an example:

Public Sub NewNameSaveAs()
	Dim oldwkbk As String
		
	' Get new file name prefix
	strPrefix = InputBox(Prompt:="File name prefix please.", _
		Title:="Enter File Name Prefix", Default:="Prefix")
	If strPrefix = "Prefix" Or 	strPrefix = vbNullString Then
		Exit Sub
	Else
		
		' Workbook.SaveAs ...
		oldwkbk = ThisWorkbook.Name
		Application.DisplayAlerts = False
		ActiveWorkbook.SaveAs "C:\Program Files\Dm\" & strPrefix & "_" & oldwkbk
		Application.DisplayAlerts = True
	End If
End Sub

Open in new window

Here is some more information on InputBox: http://www.ozgrid.com/VBA/inputbox.htm

Fordraiders

ASKER

guys, as usual , I have not explained myself correctly.

Sorry,
I'm wanting to (no visual) to open this workbook.
Workbooks.Open _
Filename:="C:\Program Files\Oss\Export\Cross\Export_Crs_KS-Data_Collection_Template_D-50_.xls"

put the input box here..
then do a filesaveas on the workbook
"Export_Crs_KS-Data_Collection_Template_D-50_.xls"

The code I posted is something I use for a simliar routine...

Thanks
fordraiders

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

ThANKS