Retrurn Date Portion Of File/Workbook Name To Cell
Sep 27, 2006
I am using Windows XP and Excel 2003. The filename I want to open is called "MMO Activity Report 25-09-06.xls". Part of the filename as "MMO Activity Report" will never change but "dd-mm-yy" will change constantly. I want it to show in active worksheet cell A1 in format YYYY.MM.DD ie. 2006.09.25. I type in Sheet1, cell A1 as "=filedate()" but it shows me "#VALUE!"
Function filedate()
Application.Volatile
Filename = Mid(Right( ActiveWorkbook.Name, 21), 1, 9)
filedate = Format(DateValue(Mid(Filename, 1, 2) & "-1-" & Mid(Filename, 4, 2) & "-1-" & Mid(Filename, 7, 2)), "yyyy.mm.dd")
End Function
View 6 Replies
ADVERTISEMENT
Mar 21, 2013
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies
View Related
Jun 29, 2013
I have a range of workbooks that I need to access when closed. I therefore want to use INDEX rather than INDIRECT.
This non-dynamic INDEX reference works perfectly:
=INDEX('[AAA 100.xlsx]Sheet 1'!$A$1:$F$20;1;1)
In the future, however, there will be a lot of reference files and both portions of the name will change - I might have ABC 123, DEF 345, etc. These name portions will be stored in cells. Cell A19 might contain "AAA" and Cell A20 might contain "100".
I am trying in my ignorance to get something like this to work:
=INDEX("'["&A19&" "&A20&".xlsx]Sheet 1'!$A$1:$F$20";1;1)
But I just get "#VALUE!".
Stepping through the evaluation, it seems the final value to be evaluated is this:
INDEX("'[AAA 100.xlsx]Sheet 1'!$A$1:$F$20";1;1)
The outermost double quotes therefore seem to be causing the problem, but if I remove them I get "#REF!".
View 4 Replies
View Related
Aug 21, 2013
I have a workbook with a master data list including member names, member locations, member phone numbers, and various items checked out or on loan to members. I then have multiple sheets breaking down the data for quick reference. So for example Name, Member #, Location, Phone, Item A, Item B, Item C, Item D etc... I need to be able to have all the Members that are in certain cities displayed in it's own worksheet. I have played with the aggregate function, if and functions,... and I am dying. This is for a motorcycle club to keep track of who has ordered what, how much they owe, how much they've spent etc.
View 1 Replies
View Related
May 28, 2014
Transferring a portion of a date from one excel to another. On the file attached, the 1st column lists the date, and the second column needs to show the date in text format.
3-Jan-14 —> “January”
How this can be done?
Attached File : Month Problem.xlsx
View 4 Replies
View Related
Dec 12, 2012
I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.
I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.
Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.
Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy
[code]....
View 4 Replies
View Related
Nov 3, 2009
I am trying to import some csv files so I can combine them, but am having probs with the filename and location.
Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With
With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................
View 5 Replies
View Related
Aug 7, 2008
I have a report which I have written a macro to reformat, I then want to copy the sheet (Remake) containing the report into a new workbook as the only sheet in the new workbook. I want the sheet name to be changed from "Remake" to "Duty to Consider" and I want the new workbook to save itself automatically as "Duty to Consider - Month/Year" - in this case it would be "Duty to Consider - August 2008".
I think that a Pop Up box which lets me input the Month and Year of the report I am working on would be useful in naming the new workbook, if this box popped up when I click the button which runs my Macro that'd be great. Here is the code I am using to reformat my report. This works fine I just need the changes I mentioned. Application. ScreenUpdating = False 'this will turn on screen updating, making the macro run faster
'This will select the DTC Tab and remove rows 1-4
Sheets("DTC").Select
Rows("1:4").Select
Range("A4").Activate
Selection.Delete Shift:=xlUp
'This will add a filter to the data to remove duplicate rows
Range("A1").Select
Range("A1:J1307").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'This will copy the data on the DTC tab and copy it into the Remake Tab
Cells.Select.....................
View 2 Replies
View Related
Apr 8, 2008
I want to amend the ' Date Modified' for a spreadsheet. I have just made changes to it - so the 'Date Modified' shows today's date and time. Is there a way of changing the 'Date Modified' so it shows as yesterday's date?
View 3 Replies
View Related
Feb 26, 2009
I'm trying to sort a column of email addressees to determine how many have the extension .ac - basically I need to know how many student addresses are present in the column.
View 9 Replies
View Related
Aug 9, 2012
I have an Excel 2007 workbook with several sheets, and various cells in several of the sheets have column headers with a description in the same cell enclosed in curly brackets, e.g. Header{Description}. I would like to to keep the column headers in their respective cells, but move (cut) the descriptions along with the brackets to the respective cell comments.
View 1 Replies
View Related
Jul 26, 2013
I was wondering whether it is possible to have excel ignore the text portion of a given cell. Or alternatively, have excel add in units (or specified text) after you input an initial value so that the cell can be used in a formula.
I have noticed that if I have cell A1 as 1 min and autofill, excel will recognize the number value and still put 2 min in A2, 3 min in A3, etc. However I can not use a function or any formula as the result will be #Value or another error.
I am looking for a way to be able to use the cells in formulas, and I do not want to put the text portion of the cells into another row/column.
View 3 Replies
View Related
Nov 16, 2006
I have made something similar before and we were able to make it work... I tried the same code, but it is not working now. Basically in cell a1 I have
Auction Type: Auction, Auction End Time: 11/15/2006 10:00:00 AM(MST), Asking Price/Current Bid: $10.00, Number of Bids: 0, Description: , Traffic: 28
and in cell b1 I need it to only display $10.00 in cell c1 I need 0 (for number of bids)
View 9 Replies
View Related
Jun 2, 2014
write a macro to copy a set of sentences in an Excel Cell.
Excel cell contains set of sentences under 2 categories.
OK Points:
* ----------
* ----------
* ----------
Improvement Points:
* ----------
* ----------
* ----------
I need to copy the sentences under the heading improvement points. Enclosed trial excel sheet for further ref.
View 1 Replies
View Related
Jan 12, 2010
I have a column of data that contain miscellaneous comments in each of the cells in column 'A'. This is a sample of the text in one of those cells:
The quick brown fox jumps over the lazy dog.
Is there a way to programmaticly search each cell in col 'A' to look for any occurrence of the string 'fox jumps over' and highlite in RED only that string occurrence?
So in the end the above cell would now be:
The quick brown fox jumps over the lazy dog.
View 9 Replies
View Related
Jul 5, 2012
A1=www.google.com/testing/123/1234.jpg
A2=www.yahoo.com/
I need a formula to copy testing/123/1234.jpg to www/yahoo.com/
End result: A2 = www.yahoo.com/testing/123/1234.jpg
In reality, any data after www.google.com/ needs to copy to the end of www.yahoo.com/
View 4 Replies
View Related
Mar 22, 2009
I am trying to organize it in a way that I can have all my purchases added based on my text criteria of certain purchase... Ill try to be as clear as possible. I have already organized it in a way that when I paste new months statement in my Excel file it sorts it by date, newest on the top, and I made Conditional Formatting by color on column D which states the Description of purchase. Starbucks appears in description cell but it is followed by number and location making each Starbucks cell in column D different in name. So its hard to use Exact phrase formula to combine the amount for that purchase.
My question is: Is there a formula that find a PORTION of text in a D column and automatically adds the price to the right of it together in one cell. There is a formula that looks for the whole text in the cell but that doesn't work when my name has always some extra numbers at the end of the word Starbucks
View 2 Replies
View Related
Feb 7, 2007
Is it possible to have the name of a workbook referenced in a cell via a formula or VBA in that workbook? I'm trying to extract the date that is in the filename, and input that in all cells in column N that have data in column M. I know I'll have to do some manipulating, but if I could somehow automate getting the filename input into the workbook, I might be able to muddle my way through it.
View 2 Replies
View Related
Aug 23, 2006
how I can use the contents of a cell (in this case D2 and its a number) as a file name and then save in a specified location. I am using a template to create invoices and then wish to save them all in one folder. Each invoice will have a different invoice number so there will not be any problems with duplications.
View 3 Replies
View Related
Aug 24, 2006
I have a cell that is pulling data from a separate file, no problem there. However, I want the name of one of the folders within that path to change monthly (eg. by monthly name). How can I make a file path incorporate a cell link within a filepath (eg. instead of it saying 'Aug 06' I want it to refer to cell A1 which reads 'Aug 06').
View 2 Replies
View Related
Sep 15, 2006
I need to be able to save a workbook by running a marco and it save the file automatically by pulling what ever the value of cell A1 is.
Ex: I want to run the macro and it save my excel workbook in C:Documents as (Value of Cell A1) What code would you put into Visual Basic.
View 3 Replies
View Related
Apr 9, 2008
I need to reference a cell to another cell in a different workbook (not opened), however the name of this workbook contains a variable in its name. Something like:
='C:[Week" & variable &".xls]R23'!$D$3
where variable is the number of the week. I have tried using a cell name instead of a variable: ='C:[Week=($B$3).xls]R23'!$D$3
and I get a dialog box to update values by selecting a file.
View 4 Replies
View Related
Oct 10, 2011
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies
View Related
Feb 23, 2010
I am continuing to struggle with my first attempt at a macro, and wonder if someone would mind taking a look to see where I have gone wrong? All I am trying to do is create file copying tool where the user can define the source and destination directory in workbook cells, and assign the macro to a button to complete the copy of all files from source to destination.
The macros do this:
Source and Location directory defined in Sub Archive1.
These are passed to file copy macro Sub ArchiveScript.
This macro checks for presence of destination directory, ensures that the directory backslash is in place, specfies the variables fn and fn2 to make sure the filecopy command copies all files in the directory, and then carries out the filecopy function itself.
However, I am getting two errors from the code that I can't figure out (I am still very much a beginner!)
- Invalid procedure call or element on fn = dir() at the bottom
- The copy still works, but only seems to copy one file from the source to the destination, and not all of them.
View 6 Replies
View Related
Sep 12, 2006
I created a new workbook that contains a list of additional design requirements than our old checklist. These requirements are answered by placing a 1, 0 or .5 in the cell next to the requirement (1 = yes, 0 = no and .5 for half credit). I can't simply go to the old workbook/worksheet and copy the results into the new workbook/worksheet as the rows of requirements don't match up since some requirmements have been deleted and new ones added between the old and new checklists.
I created a command button in the new checklist and what I want it to do is:
- open a dialog box that lets me select the old checklist file (these are all excel files, but with different names).
- go the the proper worksheet in the old file (DFT Checklist) and copy a column of results from the old checklist ("DFT Checklist" worksheet) and paste them into the proper cells of the new checklist ("DFT Checklist" worksheet).
- The macro would know (i'll have to tell it) which requirements and cells are still valid answers between the old checklist and the new one. It would disregard requirements that are no longer in the new checklist.
- Once complete, it would close the old checklist without saving.
View 4 Replies
View Related
Feb 8, 2008
I'm trying to get Mac OS X Excel VBA to understand this code however the ChDir reference and the filepath seem to be causing problems. I would like to specify in the code where the file should be stored but I did try removing the filepath section altogether but to no avail.
Option Explicit
Sub SvMe()
'Saves filename as value of A1 plus the current date
Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("A1").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit your PC, including USER NAME
ChDir _
"C:Documents and Settings USER NAME Desktop"
ActiveWorkbook. SaveAs Filename:=newFile
End Sub
View 2 Replies
View Related
Sep 24, 2007
I have a report that is collated from several other workbooks. The source workbooks follow the same naming convention throughout the year and so I have managed to concatenate the file path to bring through the text of the filepath and cell reference however cannot work out how to get the value of the cell. For example, I have the file path in A1, file name in B1 and sheet and cell referernce in C1.
I am using {=(A1&B1&C1)} which gives me R:CONFIDENTIALREPORT FOLDER[REPORT 24.09.07.xls]Sheet1!$D$1. To make matters slightly more difficult, the source reports would be closed. Is there an easy way or am I going to have to do this in VBA.
View 4 Replies
View Related
May 3, 2012
how to write a macro in excel to rename a file with a date in a cell (A1) from that file?
View 5 Replies
View Related
Feb 26, 2013
I have a workbook that column C has computer names starting from C4 to C83. I need to get the Datelastmodified of a log file that robocopy is writting to the following path. \Server1folder1WorkSheetName\%computername%somefile.log. The Datelastmodified needs to be entered in column K for each computer name in column C. One last thing would also be great is if the file does not exist it would write a error value.
View 1 Replies
View Related
Jan 26, 2007
Is it possible to save a file with a date taken from a cell? For example:
I have an Excel file opened, in the cell A1 there is a date: 12.04.2003 (or any other date format). I want an Excel to save this document, but after I check the date of creation (modification) of the file under Windows, I want to receive the result 12.04.2004 (or any other, that was in the cell "A1" before pressing SAVE button).
I imagine, that there would have to be some external script running, that would change the system date for a moment, just after detection of pressing SAVE command.
View 9 Replies
View Related
