I Need a Method of Saving a Filename with the Current Date embedded into it and then saving the file

Robert, where do you get the date information from?

BarrieBarrie Davidson

Hey Barrie,

The date that is incorporated into each filename is usually taken from the first day of each work week. (Whereas this week the date would refer to Monday, October 22, 2001.. so the date format in the filename would be "20011022". Though it doesnt have to be exactly this date. Since the report is run typically on 'that' day anyways, then using "Today()" as the date function would work perfect. I guess the real question is...Is there a way to use the "Today()" function within a cell, and yet append or prepend additonal characters before and/or after the "date"? (something to the effec tof Reg 01_CBA_Wk_(Today ().xls? Obviously this is not the way to go..but is there an equivalent that might work?

Thanks for reading through my rambles! Robert

Robert, I think this will work for you. Try it and let me know.

Sub Save_Files_With_Date()
' Written by Barrie Davidson

Dim File_Names As Variant
Dim File_count As Integer
Dim Active_File_Name As String
Dim Counter As Integer

File_Names = Application.GetOpenFilename("Text Files (*.txt), *.txt", , , , True)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
File_count = UBound(File_Names)
Counter = 1
Do Until Counter > File_count
Active_File_Name = File_Names(Counter)
Workbooks.OpenText FileName:=Active_File_Name, Origin:=xlWindows _
, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), _
Array(20, 1), Array(40, 1), Array(60, 1))
Active_File_Name = Left$(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".xls") - 1)
Active_File_Name = Active_File_Name & Format$(Now, "yyyymmdd") & ".xls"
Active_File_Name = Application.GetSaveAsFilename(Active_File_Name)
ActiveWorkbook.SaveAs (Active_File_Name)
ActiveWindow.Close
Counter = Counter + 1
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Regards,
BarrieBarrie Davidson

Hello again Barrie,

I have just incorporated your code into the associated macro(s). It looks like it just might work. However, before I proceed any further, I'd like to ask exactly which typ of .txt file your code is referring to? Is this a text file that contains a listing of filename-related text? Just wanted to clarify (I'm still somewhat of a novice at VBA, so I am attempting to interpret the best I can!)

Thank you for your help..

Robert

Sorry, too quick on the copy

Robert, change the line that reads

File_Names = Application.GetOpenFilename("Text Files (*.txt), *.txt", , , , True)

to

File_Names = Application.GetOpenFilename("Microsoft Excel Files (*.xls), *.xls", , , , True)

I was too quick on my copying!! I had some code very similar to your request except that request was to open text files.

Sorry
BarrieBarrie Davidson

Ah, few more questions,....

Ah, that worked! Ok, a few more questions... Can I get Excel/VBA to change/browse to a specific directory (in this case, on a server) when the "Save Filename As" dialog box appears? also...

I may not have gotten this far yet, but will this procedure loop for every file (11 seperate files..each differing with a seperate Region Number (ie, 03) within the filename)...where each file genrates the slightly differed filename (again differing by only a Region Number (O3, etc.)within the overall name). Each file is located on the local hard drive.

Sorry for any inconvenience!

Thanks again,

Robert , change the line that reads

Re: Ah, few more questions,....

To answer your questions:

1. Can I get Excel/VBA to change/browse to a specific directory (in this case, on a server) when the "Save Filename As" dialog box appears?

A - you can insert the following code just before the line that reads "Active_File_Name = Application.GetSaveAsFilename(Active_File_Name)":

ChDrive "L"
ChDir "L:\Saved Data"

ChDrive "L" 'Use this to change the drive to your network drive (L in this case) if your original files are on a different drive
ChDir "L:\Saved Data" 'Use this to change your directory to the appropriate directory

2. will this procedure loop for every file (11 seperate files..each differing with a seperate Region Number (ie, 03) within the filename)...where each file genrates the slightly differed filename (again differing by only a Region Number (O3, etc.)within the overall name)?

A - Yes

Hope this helps you out.
Barrie

PS - No inconvenience on my end. I plan on adding your question to my website since I think it's a question that a lot of people would find useful (thanks).

Barrie Davidson

Re: Ah, few more questions,....

Thanks! Well your first suggestion, regarding the directory change, worked perfectly. However, it doesnt look as though the process is looping...whereby I am given a prompt to select additional files to save and save to?

Your assistance is greatly appreciated.

Thanks,

Robert

Re: Ah, few more questions,....

Robert, the macro will allow you to select more than one file at the beginning. Try selecting two files when prompted to open a file. Note, you will be prompted each time the file is saved under a new name.

Let me know if this solves it for you.

BarrieBarrie Davidson

Had to Leave for the day! Have one last question!

Hello again Barrie,

Thanks again for all your help. I had to leave abruptly yesterday and could not complete the 'assignment' I have one last question. How would I go about pointing the "Open" dialog box to point to a specific directory directly? (in this case it would actually be a "shared" drive on another users computer/workstation. ... for example, the name of the 'computer' would be "bsmith" so I would access that machine from my networked workstation with "\\bsmith\directory\subdirectory)

My questions just won't stop I know!

Thanks again,

Robert

Re: Had to Leave for the day! Have one last question!

Just use the ChDrive and ChDir commands before the GetOpenFileName command line.

Regards,
BarrieBarrie Davidson