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 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 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.........................
Filesearch......
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"............
This 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.
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 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"
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.
The code that needs updating is as follows:
I am writing a class to be used in Excel 2007 that will mimic the action of the FileSearch in 2003, but I am stuck with the sort.
View 2 Replies View RelatedI 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?
View 6 Replies View RelatedFor those who use Application.FileSearch in Excel and Access, it's a huge pain that the object has been hidden in Office 2007.
I spent some time looking around for alternatives and have found two so far.
1. A FileSearch class that you can add to your project. Instead of using Application.FileSearch, you reference FileSearch instead
2. You can also use VBScript.
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
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 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
If .Execute > 0 Then
Call DoStuff()
End If
End With
End Sub...........................
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................
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)
I wrote a very simple macro in an Excel file a few months ago. Worked great. Worked on my computer, and worked on a dozen other people's computers perfectly. Now, months later, ONE person is getting the error message 'Code execution has been interrupted' every time she runs the macro. I've tested it on my computer with Excel 2007, and it worked. Tested it on another computer with Excel 2003 (offending computer has Excel 2003), and it worked again. So I tested a third computer on Excel 2003, and it worked again. I'm 100% positive the macro code isn't causing this error. This makes me think that it's related to an application setting that was changed somehow.
View 5 Replies View Relatedbool = IsError(Application.WorksheetFunction.Match(ws1.Range("D" & i) & ws1.Range("G" & i) & ws1.Range("H" & i), ws2.Range("A3:A" & LR2) & ws2.Range("B3:B" & LR2) & ws2.Range("C3:C" & LR2), 0) > 0)
I am wanting to return a boolean result for if a multi-condition match is found. In pseudocode, I want to basically determine if the value in Column D, G, and H on worksheet 1 for a given row is repeated in Columns A, B, and C on worksheet 2 in any row. I have all variables properly dimmed.
The following code is giving me an object-defined or application-defined error:
wsDst. Range("E" & NextRow) = "=VLOOKUP($B" & NextRow & ",PriceList!$A:$IZ,MATCH(HLOOKUP($I" & NextRow & ",PriceList!$C$3:$IZ$3,1,1,PriceList!$A$3:$IZ$3),FALSE)"
Im copying and pasting data from one workbook to another but when I want to close the source workbook, it comes up with this message that I have much data and if I want to keep this in a clipboard. I thought I could disable this with Application.DisplayAlerts = False but when I do this, Excel freezes. Im I doing something wrong. How can I supress this window?
Public path As String
Sub Get_data()
path = "\Nlchoosa.nlOPS_Processes$OPS_ProcessesReports Sector performance"
Workbooks.Open Filename:=path & "ReportsSector Performance Reporting week.xls"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Windows("Sector Performance report Week.xls").Activate
ActiveSheet.Paste
Application.DisplayClipboardWindow = False
Windows("Sector Performance Reporting week.xls").Activate
Application.DisplayAlerts = False
End Sub
I am getting an 'application defined or object defined' error at the code coloured red. I'm trying to sort a list of names starting at c5
'Update Names on Sheets
If Sheets("Daily Visits May").Range("e1") = "y" Then Goto 10
If Sheets("Daily Visits May").Range("d1") > 38837 Then Else Goto 10
Sheets("Daily Visits Apr").Select
Range("b5:c660").Select
Selection.Copy
Sheets("Daily Visits May").Select
Range("b5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
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...................................
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 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