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


Modified Transpose Function OR Macro?

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

File Date Last Modified From Cell Value

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

Date Modified Of Text .txt File

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

Open File Depending On Name And Date Modified?

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.

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

Showing File Last Modified Date In A Cell?

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

Checking File Created And Modified Dates

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

Open Latest File By Date Modified

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

Excel 2010 :: Function For Last Modified Date Of Cell?

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.

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

Displaying Last Modified Date And Time Of File In A Cell

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

Adding Date Modified To PDF File Extracting Macro

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

Modify/Edit File Properties (Date Modified) Of Workbook

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

VBA Count File Which Modified Date Matches Fixed Date

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

Function FileCountDate(Path As String, FileType As String) As Long

Dim strTemp As String
Dim lngCount As Long

strTemp = Dir(Path & FileType)
Do While strTemp ""

[Code] ......

However I got a #VALUE! error message with this formula:


In the TEST folder there are 4 pdf files so it should return the value of 4.

View 2 Replies View Related

Function Returns Value To Cell As Single - Function Is Defined To Return Double

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


View 9 Replies View Related

Macro To Save Worksheet As New File Then Return To Original File

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

Date Function- Open A File, Make An Exact Copy And Save It Under A New File Name

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:= _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

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

Macro/function To Take Data From Source File Into Base File

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

DATE Function & IF Function (return A TRUE Or FALSE Based On A Date In A Cell)

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:


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

Function Won't Return Value

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

Using Function To Return A Value

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

Function Return 0 Value

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

Return Function As An Argument

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

Return No Value If The Function Evaluates To An #REF

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

IF Function To Return A False Value

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

Error In Return Value From Function

Dec 26, 2012

I have following snippet of code. it is very simple.

Sub GetDatafromXmlToTemp()
Dim str As String
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.

str = ColLetter

View 2 Replies View Related

Function VAL To Return Long Value?

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

Function / Macro To Return Above Row

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?

Expected results


View 4 Replies View Related

Is There A Function To Return >3 (text) As 3

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

Return Variable From Function

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

If Function To Return One Of 3 Results

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

Copyrights 2005-15, All rights reserved