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 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 practicing my VBA and can't get this practice code to work, the syntax looks good but all it does is set the current cell to 23. and I want it to keep going up the column until it find a cell with any value and then change it to say 23. if the value is empty it should keep going up.
Sub chngevalue()
If ActiveCell.Value Is Nothing Then
ActiveCell.Offset(-1, 0).Select
Else
ActiveCell = 23
End If
End Sub
I did check the internet and my reference books and wasn't able to find a clear reason.
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.
View 8 Replies View RelatedI 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 want delete row E&F depends upon blank cells in range of F:F column...though vba
View 6 Replies View Relateddeletes a row if it finds a specified value in a specified column (in this instance, "NB" in column E). However, it is very slow and some end users are complaining about the amount of time it takes to run. Here's what I'm using at the moment:
View 5 Replies View RelatedI have a macro that needs to walk down a list of values and when it finds breaks in the values, it will insert a formula for a calculation. The problem I'm having is getting the code to loop correctly until it finally finds the value "End" when it should stop (when I play around with the code, sometimes I can get it to continue the loop, but it blows past "End" and then it experiences an error because it can't end.
Sheets("Master").Select
Range("B1").Select
ActiveCell.Offset(1, 0).Select
AssetIDStartRange = ActiveCell.Address
X = 0
Do
ActiveCell.Offset(1, 0).Select
X = X + 1
Loop Until ActiveCell.Value ""
SortCriteriaName = ActiveCell.Value
ActiveCell.Offset(-1, 1).Select
ActiveCell.Formula = "=SUMIF($B13:$B5000," & """" & SortCriteriaName & """" & ",$H$13:$H$5000)"
ActiveCell.Offset(0, -1).Select
If ActiveCell.Value "End" Then....................
I'm tying to finds the most recent X or O. Then takes the price on that day and compares it to the current price and based on the difference either higher or lower puts out an X if the current price is higher and an O if the current price is lower by the Half StartData - however when i get to about 6 IF statement it freeze up and it wont give me the X or O's ...
View 9 Replies View RelatedFilesearch......
Just been upgraded to 2007 and computer says no......
Iv only been VBA'ing for around 6 months and really am still very new to it.....
The bit that goes wrong is:
The following code works fine in Excel 2003 but returns the following error in Excel 2007:
Run time error '445'
Object does not support this action
Sub List_Files()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
.LookIn = "C:DepartmentsInventory"............
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'm looking to have a row at the top of a worksheet which I can type in, so that only the rows below which contain that information will show up. For example, say I have the following 3 rows, 2 columns each:
Cat Feet
Cat Head
Dog Feet
I'd like to have an additional row so that if I typed in "Cat" only the "Cat Feet" and "Cat Head" rows would show up. Likewise, if I typed in "Head" in the proper column only ""Cat Head" would show up.
On Error GoTo importError
For Each b In Range("names")
If b = FILE.Sheets("Sheet2").Range("e3") Then
ThisWorkbook.Activate
ThisWorkbook.Sheets("Sheet2").Select
b.Row.Value = n
For Each c In Range("dates")
If c = FILE.Sheets("Sheet2").Range("e5") Then
ThisWorkbook.Activate
ThisWorkbook.Sheets("Sheet2").Select
c.Column.Value = m
ActiveCell = nm
Set Targ = ActiveCell
Targ = system
Targ = FILE.Sheets("Sheet2").Range("e20")
End If
Next
It doesnt work, it gets to b.row.value and throws up an error, i realise im using the wrong code but I dont know enough vba script to resolve the issue
I have a timesheet and a data base spreadsheet, the db spreadsheet opens the timesheet (many, one after another) and I want it to look for each name in the db and if the name cell on the timesheet it has open matches then i want it to remember the row value (on the db), then look through the dates in the db until it finds the matching date to the one in the timesheet, i want it to store this column value (in the db) so I can concat the row and column to get the activecell where I will be putting the total hours (a single cell reference) from the timesheets into the db.
I'm simply trying to search a directory for .xls files and count them. I have previously used the following code successfully to do so, but for some reason it has recently stopped working. Debugging shows .FileSearch.Count() = 0 after every search. I have atleast half a dozen .xls files in the "Reports" folder relative to the active workbook.
strReportDirectory = ActiveWorkbook.Path & "Reports"
With Application.FileSearch
.NewSearch
.LookIn = strReportDirectory
.SearchSubFolders = False
.Filename = "*.xls"
.Execute
'loop through each workbook in the directory
For i = 1 To .FoundFiles.Count
msgbox("I found: " & .FoundFiles(i))
next i
I need to add a space in front of a string of numbers/letters, but it still doesn't seem to match what's in the lookup range. Granted, i get the lookup range from HQ, so there may be a formatting issue.
View 2 Replies View RelatedI need a piece of VBA code to assign to an Excel form that determines the maximum value of a subset of one column whose cognate rows in an adjoining column satisfy a particular value.
View 2 Replies View RelatedI am trying to make a macro, but because of my inexperience, it's not working out. I have got a big list (list 1) of numbers in column E. each number has some information in the cells of columns C, D and L, all in the same row. on the same sheet, I have got another, smaller set of numbers (list 2), also in column E but at the bottom of the sheet. the numbers in list 2 are all present in list 1 (but not all numbers in list 1 are present in list 2). the columns C, D and L on the same row of the numbers in list 2 are empty...............
View 4 Replies View RelatedThis relates to Excel for Mac 2004
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 need to be able to open a filenamed in the following format.
c:/workbook20060112.txt.
However the lsat part of the string is dyanmic and works with one business days lag (hence format sheet (date, "yyyymmdd") ) is not approriate. i also have the problem that that MS Excel does not have a holiday calendar (so this would not work over bank holidays/easte/exmas ect). The folder also contains archived files as below.
c:/workbook20060112.txt
c:/workbook20061130.txt
c:/workbook20061129.txt
I have been told about a filesearch object as well as a filesystem object. What situations would you use one over the other?
What I want it the most upto data file to pick up, but I dont want to do a loop where it look through all the files or something that counts down from today until it 'hits' the correct file.
The Application.FileSearch method is not available in excel 2007, is there a similar functionality I can use?
View 9 Replies View RelatedI 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
For i = 1 To .FoundFiles.Count..............
I've gotten the Filesearch function to work in VBA when using a wildcard after the keywords like this: "SomeFile*.xls".
But I can't get it to work with keywords after the wildcard character like this: "*someFile.xls"
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
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:Temp1"
Sub CombineWBs()
Dim wb As Workbook
Dim newWB As Workbook
Dim i As Long
On Error Resume Next
With Application.FileSearch
.NewSearch
.LookIn = "C:Budget"
.Execute
Set wb = Workbooks.Open(.FoundFiles(1))
RenameWS wb
wb.Worksheets.Copy
Set newWB = ActiveWorkbook
wb.Close SaveChanges:=False
For i = 2 To .FoundFiles.Count
Set wb = Workbooks.Open(.FoundFiles(i))
RenameWS wb
wb.Worksheets.Copy After:=newWB.Worksheets(newWB.Worksheets.Count)
I have a macro that searches through a certain location for Excel files. The program and files were made in Office 2003. When another user runs the macro in Office 2007, the files are not recognized or the macro does not search, I'm not sure. Either way, the files are never located in their folder location. Any thoughts or ways around this?? The top part of the code is posted below,
Do
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:Documents"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = " Book*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count ' Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)