Code For File Directory Loop - Use Of Application.Filesearch

Feb 11, 2009


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.

View 9 Replies


ADVERTISEMENT

Code That Used Application.FileSearch

Apr 18, 2009

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.

View 9 Replies View Related

Loop Through Directory And Return Path File And A Value

Mar 15, 2014

I need a macro to loop through a dir and the sub directories to find xlsm files, when it finds one open, go to the sheet named data, look at c1 and if the value is between 12.1 and 13.4 then i need it to write the file path, filename to a1 in my excell sheet and then write the value from c1 in the original file to A2 of line in my excel sheet.

I have hobbled pieces of code together without any good results.

View 2 Replies View Related

Application.filesearch

Jun 27, 2007

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.........................

View 5 Replies View Related

Loop Through All Folders In A Directory - Find Matching File

Jun 28, 2014

Code that loops through all folders in a directory, to check each workbook in each of those folders. If any workbook name in any of these folders matches a predefined string, then open this workbook for some other codes to run.

View 2 Replies View Related

Application.Filesearch 2007

Feb 5, 2010

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:

View 11 Replies View Related

Application.FileSearch Error '445'

Mar 3, 2007

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"............

View 9 Replies View Related

Excel For Mac, Application.FileSearch Not Working

Nov 3, 2009

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.

View 12 Replies View Related

Application.FileSearch Is Not In Excel 2007

Jun 26, 2007

The Application.FileSearch method is not available in excel 2007, is there a similar functionality I can use?

View 9 Replies View Related

Application.filesearch Replacement For 2007

Jun 26, 2008

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

For i = 1 To .FoundFiles.Count..............

View 9 Replies View Related

Application- FileSearch Method In 2007

Sep 24, 2007

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"

View 3 Replies View Related

Application.FileSearch Excel 2007 Update

Jul 3, 2009

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:

View 10 Replies View Related

Logic Behind The Application.FileSearch Sort Method

Jul 6, 2009

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 Related

Application.filesearch Not Working In Excel 2007

Dec 20, 2009

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?

View 6 Replies View Related

Application.FileSearch Is Gone In Excel 2007 -- Alternatives

Jun 24, 2007

For 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.

View 9 Replies View Related

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

View 9 Replies View Related

Application.filesearch Replacement For Office 2007

Jun 20, 2007

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............

View 9 Replies View Related

Speed Up Search Application.filesearch Is Slow To Respond

Apr 4, 2007

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...........................

View 6 Replies View Related

Application.FileSearch Not Returning Results On Some Pre 2007 Computers

Feb 11, 2008

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................

View 4 Replies View Related

VBA Code That Opens A File From A Directory

Jun 4, 2006

In the following code, What do I need to add to allow the selected file to open when I click on it? I have a drop down menu that lists my .xls files.....as of now when I choose a file nothing opens.

Sub Auto_Close()
On Error Resume Next
Application. CommandBars("Patient List").Delete
On Error Goto 0
End Sub

'================================

Sub Auto_Open()

Dim cb As CommandBar
Dim ctrl As CommandBarControl

On Error Resume Next
Application.CommandBars("Patient List").Delete
On Error Goto 0

View 3 Replies View Related

Prompt User For Directory And Filename; Then Save File In Directory With File Name

Jun 10, 2009

I am trying to prompt the user for the directory to be saved in and file name to be saved as; then save the workbook in the input directory with the inputted file name.

View 3 Replies View Related

VBA - Code To Save File In New Directory Not Working

Oct 29, 2012

I have some code that copies a worksheet and saves into a new workbook and saves into a specified folder and renames the new workbook.

It is all working fine however it is saving to my desktop and not the specified directory. I know there is a lot similar out there and i have exhausted my google skills trying to find the similar format to the one i am after.

Code:

Sub MonthlyReset()
Dim sourceSheet As String
Dim clearR As Range
Dim newFile As String

[Code]....

View 2 Replies View Related

Check If File Directory Exists In Code

Sep 28, 2006

I have some VBA which currently creates a directory when it produces an error. the code is as follows


Function create_year()

On Error Goto makenew
ChDir "I:Reports" & Year( Date)
Goto skipmakenew
makenew:
MkDir "I:Reports" & Year(Date)
skipmakenew:

End Function

What I would like to do is have an IF statement which checks if the directory exists.

View 9 Replies View Related

FileSearch Code Errors In 2007

Jan 26, 2008

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)

View 9 Replies View Related

FileSearch Code Error In 2007

Jun 20, 2008

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)

View 3 Replies View Related

Determine File Extension With FileSearch Method

Aug 23, 2007

I am attempting to write a macro that will open every file in a given folder, then save the files with a new file names while keeping the original file extension. The files I'm opening with excel have file extensions of ".VA", ".TOU", and ".KVA". The new file names I want to save the files with are contained in cell A1.

An example would be:
The excel macro opens file "R003890.TOU"
The macro assigns a the value in cell "A1" (B1040) to a variable
The macro assigns the file extension (.TOU) to a variable
The macro saves the file as file name variable and file extension variable (B1040.TOU)

View 9 Replies View Related

Loop Through All Files In Subdirectories Of A Directory

Feb 26, 2010

I am having a bit of trouble with a bit of code. I have a bunch of code that I want to run on all excel files that live one level down from my main directory.

I have a general directory (orders) which contains a variable amount of subfolders each month (in picture below, 'Dan', 'Frank', 'Steve'), and I would like to look through all the excel files in 'Dan', 'Frank', 'Steve', but not in the main directory (orders), or any subdirectories within 'Dan', 'Frank', 'Steve'.

I am able to loop through all the excel files in the subdirectories individually if I hardcode the directory names, but as they are variable,

View 9 Replies View Related

Copy File Names From A Directory Into An File

Mar 11, 2007

Is it possible w/ some vba code to copy the names of files, either from a search or a specific directory to an Excel Worksheet?

View 9 Replies View Related

Each Loop Says Object Or Application Defined Error

Aug 7, 2014

I am trying to run for_each_loop to in a module run an activity on other excel file.

Other excel file is having 8 sheets and loop is working fine till 4th. but when loop goes to 5th sheet it says Application defined or object defined error. I am confused because i have defined the objestSh as worksheet in the beginning.

This line of code creating problem.

Sh.Range("A2").CurrentRegion.SpecialCells(xlCellTypeVisible).Offset(1, 0).Copy

View 1 Replies View Related

Bloomberg BDH Refresh Via Macro Loop Application.Ontime

Dec 10, 2009

Firstly you don't need to know anything about Bloomberg to answer this query, only that Bloomberg has various functions that take time to update.

The code Application.Run "RefreshEntireWorkbook" will update these functions but Application.OnTime (Now + TimeValue("00:00:25")), "RunList" is required to allow them time to get the data from the feed. However this only works if the refresh and wait commands are in a seperate function. So in simplified terms it must go like this in vba:

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved