Extract Date From Filename For New Tab
May 9, 2014
Every week we produce a report that has the filename: Master_BER_Pending Revokes Report_YYYY-MM-DD.xlsm
Some of the information from this workbook needs to be pasted into a new tab on a different workbook. The new tabs naming convention is simply "DD MM". Currently I've got a command prop that asks the user to enter it in manually. See below
VB:
Dim newsh As String
newsh = InputBox(Prompt:="Enter new tab name", Title:="Tab Name", Default:="e.g. '05 05'")
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
ActiveSheet.Name = newsh
The user gets the information for the month and day from the filename of the report that was produced. What I need is the DD and MM to be extracted from the filename and used to name the new sheet.
View 6 Replies
ADVERTISEMENT
Jul 21, 2014
I have excelfilename starts like abc - usa sales- 1-1-2014. have to extract usa from the filename and insert as a column in excel with columnname 'country'.
View 1 Replies
View Related
Aug 1, 2006
Am working on a procedure to open all files in a folder and copy some data from the files into another workbook (master). I have this piece of the code. However, I also need to get the filename (from which data was copied) and paste this into an adjacent cell in the master (so I know which file each data point came from).
View 8 Replies
View Related
Jun 18, 2014
I have a filename 'filename-12-14-2014' where i need to extract date information from the filename and insert a new heading in the file 'date' in the first column and write the extracted date till the end (until data exists). I can write the new column name but i could not write date in all the fields. code i wrote is below
this is for extracting date from the filename. the 4th lines throws me the error?
(1) LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
(2) With Range("A2:A" & LastRow)
(3) x = Split(s, "- ", 2)
(4) .Value = Left(Mid(x(1), InStr(x(1), "-") + 1), InStr(Mid(x(1), InStr(x(1), "-") + 1), ".") - 1)
(5) .NumberFormat = "mm/dd/yy"
for adding the date in the first column. This works fine
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
s = ActiveWorkbook.Name
Range("A2").Select
View 7 Replies
View Related
Jun 3, 2009
I picked up a bit of code to create a CSV with a | delimiter. so that I could save the file with a date added onto the filename. i.e. myfile060309.csv.
View 4 Replies
View Related
Nov 13, 2013
I have filename as filenamex - filenamey-11-13-2013. I want to get only the date from the filename.
View 3 Replies
View Related
Aug 28, 2009
I need to add a date to the existing filename it is being saved as. Currenntly the filename is saved as follows:
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To x
y = Cells(a, 12)
Workbooks.Add ("C:Documents and SettingsDesktopTemplate.xlsx")
ActiveWorkbook.SaveAs FileName:=y
Next a
I want the filename to stay the same as above but add "-r-"a number entered into a textbox representing the revision and then the date the file was saved in the following format dd/mm/yy.
The form containing the text box has been scripted and works fine. The string entered in the textbox currently resides in the Active sheet being saved in cell "E4".
Below is a completed Filename. Note the RED is what i need to add.
2138-035-PW-COM01-RCS-001-r-1-28/Aug/09
View 9 Replies
View Related
Jun 25, 2009
I have a folder full of sales reports, all of the files are given a consistant format e.g. 09_wk50. I have a macro that do various calculations in each book. I now need a calculation based on the report date.
I am able to get the file name in the sheet so that I am just left with the value above (09_wk50) but I need to be able to get an actual date from that value.
I need to be able to get the monday of the week given. for the example given i would want the date 13/04/2009 because of week 50 in 2009, the 13th is the Monday.
View 4 Replies
View Related
Oct 29, 2009
I searched this forum to find a macro that will do what I'm describing below. There seems to be quite a number of macros doing almost what i want, but most require a macro to be run, filename to be entered, etc...
I want the macro to automatically append the current date to the filename when it is saved. It must save it in the same directory where the file was opened from, and the name must be exactly the same as before, except that it must remove the previous date appended to the filename, if there was one appended.
I don't want to run the macro for this to happen, the normal File Save command must do the trick...
Thus c:my documentsTest.xls will become c:my documentsTest 29102009.xls; and
c:my documentsTest27102009.xls will become c:my documentsTest 29102009.xls
View 9 Replies
View Related
Mar 22, 2007
I’m trying to save a new workbook with the current date included at the end of the filename, in the format yyyy-mm-dd.
I took a guess at the following but it doesn’t work - the filename comes out as 'Report39163' Can anyone help please?
ActiveWorkbook.SaveAs Filename:="\UsersDataTemp" & "Report" & Format(Date, yyyy-mm-dd) & ".xls")
View 9 Replies
View Related
Aug 6, 2013
I have a csv file named "BB31_2013-08-01". I would like to open this file via vba and this is not my only file. I also have another similar fileames such as "BB31_2013-08-08", therefore, I try to create some function like yyyy, mm & dd where I can modifie it easily for next file.
Here is my code:
VB:
'change weekly
yyyy = "2013"
mm = "08"
dd = "01"
Workbooks.Open Filename:= _
"C:Documents and Settings1314228DesktopTest ProgressBB31& yyyy &-& mm &-& dd.csv"
[Code] .....
View 3 Replies
View Related
Dec 24, 2008
Below is a bit of a big macro i'm writing but I'm not sure how to edit my code to append the date after the filename. Then i'll need to refer to the exact filename later in the code?
View 2 Replies
View Related
Aug 9, 2012
I want to create a macro that when executed changes the name of "File1" to "File1 - Edited 8-9-2012". With the date being today's date (variable). This file is going to be emailed to someone who will then execute this macro.
Is there a way to prompt the Save As window with the updated file name so the user can save the file where ever he/she wants?
View 9 Replies
View Related
Jul 28, 2007
Is there a way to save a file and have it automatically put today's date in the file name?
Example: original File name = test.xls
desired file name = test072807.xls, or test.072807.xls, or test.07 28 07.xls
So, I open the file, do whatever, and then click save.
When I click save, it does one of the above, given that today is 7 28 07.
View 9 Replies
View Related
Aug 11, 2008
I have a spreadsheet that from a button I want to run a macro that will input todays date, the value in cell A1 as the filename into a default dialog box that is at a default file path. I have been trying to do this for several hours and can not completely get it done.
View 9 Replies
View Related
Mar 9, 2014
i have several styles to handle mentioned under different rows separately.
all the styles have their various raw materials inward date mentioned under different column of their respective rows from column Q to Y
now i need to 1. extract the latest date of any particular raw material which can be in any of column from Q to y columns ACCORDING TO DIFFERENT STYLES IN A Particular row automatically that is AC
View 1 Replies
View Related
Feb 11, 2013
I'm on excel 2010 and I have a small group excel files I open everyday. Most of the files are static in name and location. I've got a macro created to open those files, which works fine with workbooks.open and the file path.
There are two report files I want to incorporate into my macro of workbooks to open. The files are created weekly and the files names have the following format: "Report Name (YYYY-MM-DD).xlsm". I don't want to use the file's last modified date because older files may get edited after the more recent ones are created. The files are also not always created on the same day, so the solution needs to be flexible enough to not refer to a specific day of the week or anything.
Macro open an excel file based on the latest date found in filename.
View 9 Replies
View Related
Mar 27, 2013
Extract data with differing date formats that I need to convert to something consistent that I can format as a date.
This is an example of data.... all with general format at the moment.
2/28/2013 2:48:53 PM
1/16/2013 10:48:50 AM
12/17/2012 11:59:49 AM
I have used this formula to extract the date portion, but I can't get this to then format as date. How do I convert this to the julian date, so I can then apply a date format?
=LEFT(G9,SEARCH("/",G9)+7)
(The day portion of this date always has a leading zero).
View 9 Replies
View Related
Jan 17, 2010
I have two questions regarding date format and hope you can provide input.
1) say 01/01/2010 displays as Jan-10 and i need a new column to state it as JAN. What function should i use to achieve it?
2) i need to state the difference between first day and last day of the month. What function should i use to achieve it?
View 2 Replies
View Related
Mar 21, 2014
I have a template file that I import data in to, which is then saved with the current date. This works, but I want to automate the import of the data, but ensure I don't duplicate the load.
Therefore, I have a tab 'FileLog' that I record the filename (variable by date) in column A and Creation timestamp in column B.
How do I obtain the filename and creation timestamp of the import data so that they can be entered?
View 11 Replies
View Related
Jul 7, 2006
I am trying to display the Filename in a cell on my worksheet. However, when I enter the =Cell("filename"), it provides the full path (C:/Documents......Cost Summary.xls) How can I display only the filename without the path?
View 3 Replies
View Related
Jul 22, 2009
Once a week I recieve a data file from another department and their report shows the period starting as well as ending but in text format all in one cell.
How can I extract the two dates to show the srart date in one cell and the end date in another. The example that would arrive in cell A1 is:
Reporting Period: 01/01/2009 00:00 to 07/05/2009 23:59
The hours and minutes do not matter.
View 9 Replies
View Related
Jul 8, 2014
In cell A1 i have a drop-down list.
In this list are the following:
London [S] (14/02/2013)
Rome [W] (23/10/2013)
Tokyo [W] (17/01/2014)
Berlin [A] (24/02/2014)
...and so on...
In cell B1 I would like to extract the currently selected city name and the [?] (e.g. "London [S]")
In cell C1 i would like to extract the currently selected date (e.g. (14/02/2013)
View 6 Replies
View Related
Dec 19, 2009
I need to extract the date from the following file name format:
C:Documents and SettingsRezDesktopExcel forumQueue Performance Phone Daily15_Dec_2009.csv
i have macro running where this file path gets save as a string and i can paste this string in a cell, is there a way i can use a few extra line of code to extract only the date from this string and paste only the date? or the other way for this would be use formula to convert this to a date.
View 5 Replies
View Related
Jul 2, 2010
What I want to do is extract the date only from a string of text in a cell and put it in the cell next to it. The date is not alway in the same position within the text string and although similiar, the text string can differ. There are around 6000 lines in the sheet, I've pasted 4 as an example below.
THE BIG COMPANY LTD 15312178 COLLECTION 14.12.09
The Small Company Ltd 19073864 COLLECTION
DATE 17/05/10 511438 511437 511436 ORD LE
The Medium Size Company 19080948 COLLECTING WEDS 26/05/10
The Very Good Company 19149554 COLL DATE:28.06.2010 175 PEA PARK ROAD Z7 9AB
View 9 Replies
View Related
Jul 17, 2013
I want to extract date from string My dates are going to increase every 9 days. as you can see in table given bellow. And goes till Wk52
Wk1-> 02-Jan-11
02-Jan-11
Wk2-> 09-Jan-11
09-Jan-11
Wk3-> 16-Jan-11
16-Jan-11
Wk4-> 23-Jan-11
23-Jan-11
Wk5-> 30-Jan-11
30-Jan-11
View 2 Replies
View Related
Dec 26, 2013
Any formula that can extract from a time stamp text string, which looks as follows: 7/8/2013 7:10:33 AM.
The tricky bit is saying anything before the first "/" represents the month, anything between the 1st and 2nd "/" represent the date, and the 4 digits after the second "/" represents the year.
View 9 Replies
View Related
Aug 7, 2007
I have data resembling the following
Col A
Sun
Sun
Sun
Sun
Col B
12/08/2007
13/08/2007
14/08/2007
15/08/2007
Col C
A
B
C
D
Is there a formula where I can extract the entire row for the most recent date, in this case Row 4 containing Sun,15/08/2007,D would be returned.
View 9 Replies
View Related
Dec 15, 2008
I have some dates with text, I want to extract dates from the text. Here is an example.
Sheet1
AB1ORIGINALI WANT2PREVIOUSLY CAPITALIZED 3Capitalize 09/05 per FMS 9/30/20054Capitalization date 11/05 11/30/20055PREVIOUSLY CAPITALIZED 6Capitalize 12/05 per FMS 12/31/20057Capitalize 08/05 per FMS 8/31/20058PREVIOUSLY CAPITALIZED 9Capitalize 09/05 per FMS 9/30/200510Capitalize 12/05 per FMS 12/31/200511CAPITALIZE 12/2006 12/31/200612PREVIOUSLY CAPITALIZED 13PREVIOUSLY CAPITALIZED 14Capitalize 09/05 per FMS 9/30/200515Capitalize 03/05 per FMS 3/31/200516Capitalize 12/05 per FMS 12/31/200517PREVIOUSLY CAPITALIZED, CAPITALIZE 12/06 12/31/2006
Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Jul 31, 2007
I've had a question which i can't solved cause of poor knowledge. Example of my issue: cel A1 are filled with a lotnumber like 107610618. as 107=year 2007 61=code of product 06=month 18=runnumber. in cel B1 i would like to have a few characters of that lotnumber visible. all that i want to show in cel B1 = month & year example 0607. Then when this is done in cel C1 i would like to convert this characters into june-07.
View 6 Replies
View Related
