I am using the following to compile data into a master workbook
With Application.FileSearch
.NewSearch
.LookIn = SrchDir
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
.LastModified = msoLastModifiedAnyTime
.Filename = "*.xls"
If .Execute > 0 Then
For Each vaFileName In .FoundFiles
' If vaFileName = Exclude Then GoTo n ' maybe something like this ?
ProcessData vaFileName
n: Next
and then processing each file, and adding it to the master. This is run every time a user opens the workbook. The number of workbooks in this folder is fairly large and more are added every day. What I would like to do is, compile a list of file names that have already been processed (I can handle that part) and then exclude them the next time the master book compiles from that folder. so if the master was run September 16th, all the data up to that point is already in the master and a list of those files is on a sheet in the book, now if the book is opened again on October 1st, it will look at the list first and only add the data from Sept. 17th through Oct 1st.
The file names I'm dealing with are named by date ie: 9-16-06.xls. The folder they're in is on a server so I can't just kill the directory after processing so that only new files are there, other people may need access to them. I think this may be handled at the "If Execute > 0" part of the code but am not adept enough to know how to do it. Possibly set "Execute" as a variant and have it cycle through the list
I'm trying to get a count of the number of workbooks in a directory and it keeps returning 0 when there are three WBs in the directory. What am I doing wrong? Here is my code.
With Application.FileSearch .LookIn = "C:Documents and Settingsdt64864DesktopTesting" .Filename = "*.xls" .FileType = msoFileTypeExcelWorkbooks .Execute MsgBox (.FoundFiles.Count) End With
I would like to exclude blanks from the source of a validation list, I have found a really neat solution for vertical list in this topic, I have modified it, but I have missed something, the formula is not working.
I have a dynamic named range which allows me to have an automatically growing drop down list in B1 as more cities are added in column A. however, my workbook will have N/A errors to begin with before cities are added.
is there a way for my drop down list in B1 not to include the N/A errors and only the cities? of course when the next N/A error turns into a city, i would like the B1 drop down list to recognise this and include it.
i have many excel workbooks in a folder i want a macro that will get the names of all the files and make the file names so extracted as a hyperlink to open the files.
I want to get a list of all the *.xls files from [URL] .... using VBA in Excel. I can quess some of the file names and I have some code which copies the file, so if I have a list I could get them all.
I am trying to use a sub to count files that some one has helpd me out with in the past. It was working but for some reason now it is not. I always have 0 returned as the number of files in the selected location (SourcePath), which is a filthy lie as there are files in there. The idea is that all .xls and all .xml files are deleted, and then if the folder is empty, that is also deleted, but as the Count_Files sub is always returning 0 the code is trying to delete the folder, which causes an error as it has files in it.
I can't seem to find Zip Files with FileSearch..I'm trying to search for and copy specific zip files from my temporary internet files folder to another folder. But it doesn't find them. It works with any other type of file. But it just won't find Zip files. And it's not because it's the temporary internet files folder, because it finds OTHER typs of files in the same folder, just not zip files.
I Have also tried using "*.zip" - Does not find any files, when I know they are there. And Have tried "*.*" DOES find hundreds of files... I have also tried serching in a Standard Non Sytem Non Hidden Type of folder. DOES NOT find zip files.
Even more maddening, It worked yesterday.....I already tried system restore.
Sub testing2() Call GetFromTemp("Filename.zip") End Sub
Public Function GetFromTemp(Fname As Variant) Dim i 'Copies Downloaded Zip file from Temporary Internet Files to Downloaded Database FilesTemp 'Fname is Filename.zip you want to search for in Temporary Internet Files
I understand that Microsoft left Filesearch out of VBA for Office 2007 because it was buggy. Sadly, however, I still need it. It would help if they placed a comment in VBA help that stated that it was discontinued. As it is, they make it look as if Filesearch is still available for use. OK, I'll stop ranting now.
After reading several earlier posts, apparently I have to use the Dir function. I learn visually and cannot figure out how to use the function from the VBA help file since there are no examples. Does anyone have an example of code using the Dir function to insert filenames in an array? Once I see the code, I'm sure I can adapt it to suit my needs.
When I noticed that FileSearch was missing after I updated to Office 2007, I decided to recreate the class and save it as a file to import anytime I needed to use it. I tried to use as little code as possible, so if there is some property you would need to add then you may have to add it yourself, but this will at least give you most of the funcitonality of the original Class without having to update your existing code too much.
You have to just reference the class and then it can still be used in a with block or however you are accustomed. Here are the two class files that I have, the first I named FileSearch (go figure):
Dim pLookIn As String Dim pSearchSubFolders As Boolean Dim pFileName As String Public FoundFiles As New Collection
Public Property Get LookIn() As String LookIn = pLookIn End Property Public Property Let LookIn(value As String) pLookIn = value End Property Public Property Get SearchSubFolders() As Boolean LookIn = pSearchSubFolders...................................
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.........................
I have a folder C:My Documents that contains excel & Pdf files. Is it possible to list all the files by name contained in this folder in a excel spreadsheet i.e one filename one cell?
If sDirectory = "" Then If iColumn = 1 Then sPath = "C:PensionDataPensionPath" ' Set the path, must end with backslash Else sPath = "C:PensionDataWorkBench" ' Set the path, must end with backslash End If Else If Right(sDirectory, 1) <> "" Then sPath = sDirectory & "" Else sPath = sDirectory End If End If
sFileName = Dir(sPath, lstAttr) ' Retrieve the first entry. lDataRow = 1 lRows = 1
This works on my PC and it works on other PCs. However, the one PC that it should and must work on, it does not. There is no file listing created.
I have duplicated the files and the folders on my PC and the files in the directories are listed. However, on that one PC, there is no file listing.
I have a folder that has a bunch of other folders in it, each containing many files. Is there an easy way to get a file directory into excel, so that I now would have a spreadsheet that tells me what all the folders are and what files are in each one?
short example: main folder = mystuff subfolder1 = my pix, contains pic1 and pic2 subfolder2 = otherstuff, contains otherstuff1 and otherstuff2
Can excel find these things and give me a list indicating that the 'mystuff' folder contains these two folders, and also list the two files under each folder?
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.
Before upgrade to Microsoft 2007 this code worked well (for 3 years). I marked it well with big space & comment where it gets hung up. The line says: With Application.Filesearch. All is well up to that point. I have another program that seems to have a problem when it comes to "With Application" as well. It must have to do with the upgrade, because my programs have been used daily, and it was right after the upgrade that it got buggy. All of the users were upgraded to Microsoft 2007 at the same time.
Sub Rpitracking() 'Collects data from records Application.ScreenUpdating = False
Dim MyCandidate Sheets("PI Tracking").Activate Set Level = Range("I5:I100") Sheets("PI Tracking").Range("J5:Z100").Select Selection.ClearContents 'Clears info pulled from records Sheets("PI Tracking").Range("5:100").Font.ColorIndex = 0 'Colors all rows black Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic Range("ID").Offset(1, 0).Select Selection.QueryTable.Refresh BackgroundQuery:=False 'Application.Calculation = xlAutomatic For Each Cell In Level Cell.Select If Cell.Value <> "" Then 'Checks to see if Application date us there If Cells(Cell.Row, 9).Formula <> "" Then 'Checks to see if app date is there MyCandidate = Cells(Cell.Row, 2) & " " & Cells(Cell.Row, 1)
I have a problem with getting the list of files in subfolders. this code is writing the names of files in a folder to column "A". But it is just "target" folder. But i need to get the name of all files not just under the target folder but also subfolders of target and even subfolders of subfolders of target
VB: Dim FSO As Object Dim SourceFolder As Object Dim FileItem As Object [code]....
I have several .mp3 files in a directory that I'd like to rename based on a list.
Currently I have a sheet with two columns starting at "A2" and "B2"
Column A contains the file path and old name (without extension) ie: C:RenameFromListOldName_01 Column B contains the file path and new name (without extension) ie: C:RenameFromListNewName_01
I've modified the code below (obtained here on these forums) to work with mp3 files, which renames them, but it also corrupts my files.
VB: Sub Rename_Files_from_List() Dim oFiles As Range, fPath As String Set oFiles = Range("A2", Range("A65536").End(xlUp)) fPath = Range("D2").Value
I have a long list of .xls files. I have to open each file, copy a few columns of data and paste it into a single xls file. I can write a macro to do this, the only problem I have is how do I get the macro to open all the xls files with different names ? I am thinking something like sorting the files by date and then let the macro open files from the oldest to the most recent one, but is there a command to do that ?
Sub Openfile() Dim wkbOne as Workbook Set wkbOne = Application.Workbooks.Open(Filename:=Worksheets("Sheet1").Range("A1") & Worksheets("Sheet1").Range("B1") 'where "A1" is the path where the file located and "B1" is the file name.' End Sub
When I run this it will work, but if I change to below it won't open all file in range, what's wrong with this?
Sub Openfile() Dim wkbOne as Workbook Set wkbOne = Application.Workbooks.Open(Filename:=Worksheets("Sheet1").Range("A1:A10") & Worksheets("Sheet1").Range("B1:B10") End Sub
I am looking for a macro that will list all subfolders (all levels) within a directory and count files in each of those subfolders. No file names, just number of files contained in each folder.
