Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

inputbox for helping create a unique excel file name. vba

Posted on 2012-09-06
5
Medium Priority
?
398 Views
Last Modified: 2012-09-06
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
0
Comment
Question by:Fordraiders
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 38373846
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

0
 
LVL 12

Expert Comment

by:Seaton007
ID: 38373865
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
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 38374059
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
0
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 38374089
Then:

Public Sub NewNameSaveAs()
   Dim oldwkbk As String,sInput as string
   'Workbook.SaveAs ...
   set oldwkbk = workbooks.open("C:\Program Files\Oss\Export\Cross\Export_Crs_KS-Data_Collection_Template_D-50_.xls")
   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


or without even opening the file

Public Sub NewNameSaveAs()
   Dim sInput as string
   
sinput=inputbox("Enter file name here")

    filecopy "C:\Program Files\Oss\Export\Cross\Export_Crs_KS-Data_Collection_Template_D-50_.xls"  "C:\Program Files\Dm\" & sinput & "_Export_Crs_KS-Data_Collection_Template_D-50_.xls"

End Sub

Open in new window

0
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 38374222
ThANKS
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question