Application.FileSearch To Obtain Last Modified Date
Mar 4, 2010
I've been trying to get this to function correctly. The following code does pull out a list of all filepaths, but what I want to to is access the properties of the file.
Basically I select a customer from a combo box, and from this the code does a vlookup to determine what folder the reports are stored in. The list is then entered onto the worksheet 'FilesInReportFolder'. There is some commented out code as I've ben trying to come up with a simple way to remove the 'xls' from the end of the string, but din't worry about that.
I've found a website that suggests adding .name to the lineto get the name instead of the path, but what I really want is be able to pull out other details about the file.
Here's my
Private Sub ComboBox1_Change()
Combobox1value = ComboBox1.Value
Dim rng As Range
Dim RowNo As Integer
Dim NoOfFiles As Integer
Dim foundfilepath As Integer
Dim FilePath As String
I have created a macro that must extract data from other workbook. It opens the workbook, with an exist, and isopened control, and then open it. Itīs running well, but i found while making another macro, that if the file direction is "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls", then i cant run the exist control. Next you have the code im using. In the first version, i had .LookIn = ThisWorkbook.Path. Now Iīve tried:
1. Dont put .LookIn, and fName is "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls" 2. .LookIn = "http://www.xxx.com:8080/" .FileName = "Challange%20Cost%20Proposal%20Sheet.xls"
Both are failing, it says, the file doesnt exist. If i try directly WorkBooks.Open "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls", it opens correctly, but i would have a control, because the original file name could change, and the macro would crash if does.
Function OpenWorkBook(fName) As Integer If Exists(fName) Then If (IsOpen(fName) = False) Then Workbooks.Open (fName) OpenWorkBook = True Else MsgBox "The WorkBook " & originWorkBookName & " is already open." OpenWorkBook = -1 End If Else.........................
It seems to me that Application.FileSearch is not working with XP SP3.
I have a code that used Application.FileSearch and it works perfectly at home. But when I use in the office, it does not work. When I click on HELP menu, the one in the office has a Microsoft Excel 2003 SP3 whereas the one at my home does not have the SP3.
I'm trying to use Application.FileSearch in a procedure to open files in a folder one by one and then do some stuff. It works fine when I developed it on my Windows machine but won't work on the Mac platform. The culprit instruction seems to be the
Application.FileSearch instruction, and I've also tried what I think may be a Mac equivalent Application.FileFind, but I get the 'Object doesn't support this action' error.
So I wrote a simple procedure below just to test the .FileSearch instruction but this fails in the same way.
Any ideas anyone? I've commented out lines which relate to Windows syntax.
I am trying to search a folder that is selected by the user for all .jpg files, and then place all those pictures onto a certain spreadsheet within the excel file. It also formats the pictures. Following is the Private Sub BatchProcessThumb2x3() Msg = "Select a file containing the photos you want to insert." Directory = GetDirectory(Msg) If Directory = "" Then Exit Sub If Right(Directory, 1) "" Then Directory = Directory & ""
On Error Resume Next With Application.FileSearch .NewSearch .LookIn = Directory .Filename = "*.jpg" .SearchSubFolders = False .Execute
' Select begining range Worksheets("Thumbnail (2x3)").Select Range("B4").Select ' Loop through all files and process them
I have a macro running in Excel 2003 that uses application.filesearch. I discovered through another post that this is not included in Excel 2007. A replacement was suggested and I cannot get it to work. Can someone show me how to replace application.filesearch in my macro with the replacement code? I'm a real novice and can't figure out what I'm doing wrong. Or if you have a better solution I'm open for that as well.
Here's the original that works in 2003, the suggested replacement code will follow.
Sub GoGetData() Dim lCount As Long Dim wbResults As Workbook Dim wbCodeBook As Workbook
I have a piece of code that was written by someone else (namely Phil_V, from these very forums) that only works in versions of Excel before 2007. I was just hoping that someone could update the code for me?
I know it's a lot to ask, but I simply don't have the expertise to do it myself I really appreciate any help received.
I wrote a macro to list all the excel files from a directory and its sub-directories to an excel sheet. It is working in excel 2003 but shows error (object does not support this action) in 2007. Actually I have copied almost this entire macro from excel 2007 help only. Can somebody modify this to use in both the versions of excel?
In the following thread, richphillips wonders about Application.Filesearch Application.filesearch In Office 2007
Is there a replacement for this? I have several macros that use Application.Filesearch and I would like it to work not only with Excel 2003 but also with Excel 2007. This is the function
Function CreateFileList(FileFilter As String, _ IncludeSubFolder As Boolean) As Variant ' returns the full filename for files matching ' the filter criteria in the current folder Dim FileList() As String, FileCount As Long CreateFileList = "" Erase FileList If FileFilter = "" Then FileFilter = "*.*" ' all files Debug.Print CurDir strFolder = BrowseForFolderShell(, , , 0) If strFolder = "" Then MsgBox "You Cancelled" Exit Function End If............
I have been trying to process Excel files in a directory with the following
Sub FindExcelFiles() Dim foldername As String Dim FSO As Object Dim fldr As Object Dim file As Object Dim cnt As Long foldername = "c:usersseagreendesktopTuesdayFeb102009week ending feb 7 2009 esting2" Set FSO = CreateObject("Scripting.FilesystemObject") Set fldr = FSO.GetFolder(foldername) For Each file In fldr.Files If file.Type Like "*Microsoft Office Excel*" Then cnt = cnt + 1 End If Application.StatusBar = "Now working on " & ActiveWorkbook.FullName DoSomething ActiveWorkbook Next file Set file = Nothing Set fldr = Nothing Set FSO = Nothing Range("A1").Value = cnt End Sub Here's the stub for the subroutine that's being called:
Sub DoSomething(inBook As Workbook) 'Massage each workbook 'Debug.Print "Hello" Debug.Print ActiveWorkbook.FullName End Sub I am using Excel 2007. I found out I cannot use Application.Filesearch as Microsoft has dropped this method for 2007. My problem now is that I just see "Now working on c:usersseagreendesktopTuesdayFeb102009week ending feb 7 2009 esting2file1.xls written six times in the immediate window.
I am trying to determine in code whether a file exists in a certain location. This seems to be very slow even in a folder which contains just one file. Is there any way to speed up this process? Three seconds seems like a long time especially given that I will have to loop through this Sub many times. The files I am looking for are CSV so I can't use msoFileTypeExcelWorkbooks unless I can modify what file extensions this looks for. I only started dabbling with VBA a few weeks ago so it is entirely possible I'm barking up the wrong tree and should be using another method to acheive my aims.
Sub CISORTEST() With Application.FileSearch .NewSearch .LookIn = "C:TEMP" .SearchSubFolders = False .Filename = "MYCSVFILENAME" .MatchTextExactly = True .FileType = msoFileTypeAllFiles
Found code to find files and return results.. edited to my liking(barely.. i have no VBA knowledge).. it works on the PC I use. This is intended to be on a shared folder across a network for others to use, and it is not returning any results on the other PCs. This computer has Office 2003.. the rest have 2000. I can NOT figure out the problem. I have read in searches that Application.Filesearch is not reliable, but I do not know how to change it to any other type of coding Attachment 26214
Option Explicit
Sub SrchForMSDS() Dim i As Long, z As Long, Rw As Long Dim ws As Worksheet Dim y As Variant Dim Fil As String, FPath As String y = Application.InputBox("Search for file(s) named:", "MSDS Search") If y = False And Not TypeName(y) = "String" Then Exit Sub Application. ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "\My-SbscompanyMSDS Database" ' \#.#.#.#companyMSDS Databse or My-Sbs .SearchSubFolders = True .Filename = y................
I have an excel spreadsheet with multiple file names (including file path) listed in a column. I have to update other files using these 'underlying' files. So each cell has the file path and file name (i.e. Q:FolderFile Name.xls). I would like to have the adjacent cells indicate the last modified date for each file. This saves me time from looking for the last modified date manually across different folders in my servers to see if the 'underlying' file has been updated, which I would need in order to update my other files.
So this function would be something like "get last modified date based on file path". I will need step by step instructions as I am not very familiar with VBA.
Example:
A B 1 Q:Folder1File name1.xls 2/10/2012 2 X:Folder2File name2.xls 12/1/2011
I am sure this is easy but i cant for the life of me find the code for this:
I am trying to have a MSG Box popup after opening a file and it displays the date the current file was last modified with an "OK" button on it to close the box.
I have a number of excel files in a directory that require me to take action after a file has been saved. What I want to do is create a macro that will look to each file and retrieve its 'modified date', then put that into a spreadsheet. What would be ideal is to create a User Defined Function to look at the last modified date of a file in the cell next to it (I have the files names in a spreadsheet as a hyperlink).
I want to know if there is a way to have a cell beside a series of cells that will change its date (and time) when any of the cells in the series is changed.
On a single row, I have four materials that total to a fifth cell. The 6th cell in that row, I would like to have a "date last modified" that would change if any of the first five cell's value is changed.
I have a pivot table for which I load data from a .csv file. Is it possible to include a timestamp on when the .csv file was last loaded. If I do it above the actual pivot table, it just shows when the pivot table was refreshed, but not when the .csv file was last loaded.
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.
I copied this snippet from the net and it has been working like a charm. I would like to add DateLastModified to it, but I can't get it to work, I'm still kind of new to more complex VBA codes. Here is the code...
Code: Private Sub CommandButton1_Click()
Dim cell As Range Dim selcell As Range Dim Value As String Dim Folder As Variant Dim a As Long
I am trying to find a way for an automatic date stamp to be populated into a field in a row when any other cell contained within the same row is changed or modified. I am hoping that there is an easy solution to this. I have tried searching for this and found a few related posts but I don't know how to modify the code to work for me and when I try to do it, it doesn't work. I have attached an example. I would like column B to populate when I modify any field in the row with the date it is being modified.
I wish to create a macro that looks in a specific directory "D:Records" . It finds the files which are modified after a date "January 01, 2012" in this directory (pdf files). Then it imports these files names into a worksheet.
