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.
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'.
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).
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?
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.
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.
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.
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
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.
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?
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?
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.
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
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.
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).
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?
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?
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
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)
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.
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
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.
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.
