Function To Return Last Modified File
Dec 22, 2008
I don't really know VBA more than just piecing together other people's work (so please dumb down your responses as much as possible).
So I would like to have a function that displays a filename within a cell. The filename I need to display will start with "cost" and have a date at the end (not always the same number of characters). I need to search a folder for all files that contain that text and return the last modified one.
I found this VBA code that returns the last modified file, but only filters based on the extension (which I would like to keep in the code as well).
View 11 Replies
ADVERTISEMENT
Oct 14, 2009
I am trying to perform the following, but am unsure if a macro is needed, or if there is a way to do it by possibly modifying the transpose function. Here are the steps required:
1 - copy a selected range of cells from a single row in one worksheet (i.e. D3:AE3).
2 - paste the data from those cells into a selected range of cells from a single column (i.e. A1:A14) in a separate worksheet BUT skip the cells from D3:AE3 that were blank instead of having them create blank cells in the column of the target worksheet
3 - Need to maintain a reference back to the original row of cells so that if their values change (i.e. D3 from source), the corresponding values in the new column (i.e. A1 in target) are auto updated.
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 8, 2007
I need to be able to find out which of two .txt files were most recently modified, the two txt files are of fixed name so I can hard code these.
View 2 Replies
View Related
Aug 6, 2014
I have this code, it's working to open the last modified file but how can I implement that it also opens a certain file name.
examples
Test1 150126.xls
Test1 150128.xls
Test1 150140.xls
the numbers are the time the file is saved, is it possible to also look at file name:text1 and then look at the last modified file So only fill in the file name and the macro opens file: Test1 150140
[Code] .....
View 5 Replies
View Related
Sep 21, 2009
if there's any sort of script which allows the file's last modified date to be shown in a worksheet?
View 5 Replies
View Related
Apr 17, 2007
Is there a way I can check an excel workbook's created and last modifed dates using a macro in that same workbook?
View 3 Replies
View Related
Feb 29, 2008
I have manged to search for the last updated file on a folder but i want to use the filename as the source for a copy and paste. it all seems to work bar the linking of the 2 processes the error message 'run time error 9 subscript out of range' is the file is already open should it just not use the same variable to copy the files?
Option Explicit
Sub Get_Availability_Data()
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Dim wsTo As Worksheet, wsFrom As Worksheet
Dim myDir As String, fn As String, a(), n As Long, myFile As String
Dim myDate As Date, temp As Date
myDir = "C:BarkingEMCIN"
fn = Dir(myDir & "*.csv")
Do While fn <> ""
temp = CreateObject("Scripting.FileSystemObject").GetFile(myDir & "" & ................................................
View 9 Replies
View Related
Feb 14, 2012
What I wanted was to a function, say, "=LastModifiedDateOf(CELL)", where CELL, is a parameter that indicates the cell I want to monitor. If the value of such cell ever gets changed, the cell containing the function has its value updated to the current date.
E.g.
A1 = "AA"
A2 = "=LastModifiedDateOf(A1)" -> "10/03/2011 10:30:32"
-- Make an update:
A1 = "BB"
A2 = "=LastModifiedDateOf(A1)" -> "14/02/2012 12:15:11"
I'm not quite a super user in Excel, but this function would be very very useful for me.
ps. I'm using Office 2010
View 4 Replies
View Related
Jul 11, 2009
How can display the last modified date and time of my excel file in a particular cell - Say in cell E1.
This is the same value that we get when we Right Click excel file->Properties->General and look at the label "Modified".
View 8 Replies
View Related
Jul 3, 2014
Below Macro which I am using to extract .PDF files. Now, I also want to see the Date moified while extracting the .PDF files. Hence, adding date modified to this macro.
Sub get_pdf_name()
Dim FR As Long, sh As String, FPath As String, FName As String
sh = Sheets("Sheet2").Name
FR = Sheets(sh).Range("A" & Rows.Count).End(xlUp).Row + 1
[Code] .....
View 1 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
Jan 9, 2014
I've written a Function which will count the number of files in a folder which the modified date equals a fixed date
For example, here, I want the file count which modified date = 01.01.2014
Code:
Function FileCountDate(Path As String, FileType As String) As Long
Dim strTemp As String
Dim lngCount As Long
Application.Volatile
strTemp = Dir(Path & FileType)
Do While strTemp ""
[Code] ......
However I got a #VALUE! error message with this formula:
=FileCountDate("C:Usersjohn.doeDesktopTEST","*")
In the TEST folder there are 4 pdf files so it should return the value of 4.
View 2 Replies
View Related
May 14, 2013
Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?
Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double
[Code]....
View 9 Replies
View Related
Mar 14, 2014
I have a macro which successully saves a worksheet as new file to another file path....(below)...but I can't figure out how to close this new file and return to the original file...
Dim myPath As String, fName As String
myPath = Sheets("Date").Range("C8").Text
fName = Sheets("Date").Range("C9").Text
Sheets("Sage CSV File").Copy
With ActiveWorkbook
.SaveAs Filename:=myPath & fName
End With'
View 2 Replies
View Related
Jun 4, 2009
I'm working on the following
Workbooks.Open Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-" & Ucase(Format(DateAdd("y", 0, Date)), "YYYY-MM-DD")&".XLS"
ChDir "D:CommondataIBMmain"
ActiveWorkbook.SaveAs Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-NAFTA.XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Date: 2009-06-03
What I'm trying too do is open a file, make an exact copy and save it under a new file name.
My problem is in the date formula the day is not always the same. In the sample case it's 03 at other times the day will change.
is there a way too get this too work irregardles of what the day might be?
View 9 Replies
View Related
Jun 24, 2006
I have a base excel file for summarizing some data, the problem is that the data comes from a different excel spreadsheet. What I want to do is make a function that pulls the data from another spreadsheet into my base file. It would be easy if it were just one excel sheet, but this job would require where the data is pulled from a data file which has many modified versions.
Can anyone tell me how to do this? The files with the data will be structured the exact same but with different data entered in. I just want a button so I can click the file I want the data from and have it show up on my summarizing base file.
View 9 Replies
View Related
Dec 8, 2009
I am trying to return a TRUE or FALSE based on a date in a cell.
for example:
if cell A1 = 07/11/2009
I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.
I have tried stuff like:
=IF(A1=TODAY()-14,"True","False")
but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
View 6 Replies
View Related
Dec 29, 2006
I'm trying to get a "simple" function to return a number and not the #NAME?error!
I'm writing the following vba code. It's located in the sheet1 vba code area.
On the spreadsheet itself, on sheet1, column C, there is data from row 1 down to row 55. I have tried formatting the Column as General, text,etc.
Function Asset() As String
Asset = Range("C1").End(xlDown).Row
End Function
When I type into the spreadsheet =Asset() , I get the #NAME? instead of the number 55.
When I step through in debug mode, Asset shows Asset="55" , but my function doesn't return it!
View 9 Replies
View Related
Oct 21, 2012
I am trying to use a function to look at the value in one cell and return a value to the cell with the functional equation. I think I use the "IF" Function. But it is a little more complicated than I can do. In my sheet, there is a number in D3. I want to create an equation in D4 that evaluates the value in D3. If D3 is between 0 and 100, I want the equation in D4 to return the value 1; If D3 is greater than 100 but less than or equal to 200, I want D4 value to be 2; if D3 greater than 200 but less than or equal to 300, D4 return 4. I would like this to continue in increments of 100 until D4 equals 10.
View 1 Replies
View Related
Apr 8, 2009
Is it possible to add some code that will make the function return 0 if it would otherwise return #VALUE!.
Without getting to involved here is the main piece of code.
Public Function FixedCosts(RscNum As Integer, InstYear As Long, CalcYear As Long, MW As Double)
FixedCosts = numMonths * MW * Adjust * (CapCarryCostMonthly + OtherFixedMonthly + TransCap) / 1000
View 9 Replies
View Related
Jan 23, 2008
When I try a MATCH function, I substituted the range through the ADDRESS function. But, it returns #VALUE error. When I manually typed the same range address, it produces the results. I've been behind this more than 8 hours still I can resist the heat on my cool head! But, I thought you coolest head guys need some heat to warm up for to-day's challenge.
View 9 Replies
View Related
Feb 25, 2009
I am using an array formula like this in cells A2:H100 of a worksheet:
=INDEX('Source Data'!$A$2:$Q$5000,SMALL(IF(('Source Data'!$I$2:$I$5000>=$O$1)*('Source Data'!$I$2:$I$5000<=$P$1)*('Source Data'!$M$2:$M$5000>0),ROW('Source Data'!$A$2:$Q$5000)-ROW('Source Data'!$A$2)+1,ROW($Q$5000)+1),ROW()-1),1)
The function must be present in all 99 rows to accommodate potential data, but there is rarely enough data to populate the entire sheet, leaving #REF! errors in some cells. Putting aside that using this many array formulae is sloppy programming, how do I craft the function above to return no value if the function evaluates to an #REF! error?
View 4 Replies
View Related
Jun 5, 2009
I am trying to use the IF function to return a false value if the combined sum of a row is not the same as either of the 1st 2 cells. If that makes any sense!
This is the formula I would like to use. =IF(SUM(g3:g9)=g1 or g2 then y if true and x if false. I thought it would be easy but I can't figure out how to do it
View 2 Replies
View Related
Dec 26, 2012
I have following snippet of code. it is very simple.
Code:
Sub GetDatafromXmlToTemp()
Dim str As String
Sheets("sheet1").Select
desc = WorksheetFunction.Match("FAMILY_ID", Rows("1:1"), 0) ' gives column number for ex 4 for "D"
[Code] .....
I am getting error "Argument not optional" in line.
Code:
str = ColLetter
View 2 Replies
View Related
Jun 30, 2013
The function "VAL" will return the value of a numerical string as a double. Is it possible to get a similar function that will return the value as a long.
View 5 Replies
View Related
Jul 25, 2013
New to creating macro's but looking for a macro or a function to get the below results. The first 2 columns are givens and looking to return the 3rd column. Is there any code that can do this?
Hierarchy
Path
Expected results
000000010HIER_NODE
[Code]....
View 4 Replies
View Related
May 8, 2008
I have a cell with a text string >3, and would like it to return the number (not text) 3. I could use Right but since the number will not only be single digit I don't think this will work.
View 9 Replies
View Related
Jan 28, 2009
I've searched for an example of function that returns a variable, however, I'm unable to find one other than the one just has return variable on the end. I was wondering what I have wrong in the following code. This is placed in a module to make it available to all my worksheets.
Public Function findLR(Worksheet, Column) As Integer
LR = Worksheet.Cells(Rows.count, Column).End(xlUp).Row
return LR
End Function
View 9 Replies
View Related
Nov 22, 2007
I am looking for a formula that will yield the following: IFAH9>AI9 return the word Green, IFAH9=AI9 return the word Blue, IFAH9<AI9 return the word Yellow. If AI9 is blank stay blank.
View 3 Replies
View Related
