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.

ADVERTISEMENT

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

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

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

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

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

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

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

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

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

Excel 2007 :: Change Orientation Of Entire Application

Apr 11, 2012

Someone Im working with says she used to change Excel to work on it on some vertical monitor. Is there a way to do that in Excel 2010 or even 2007?

Obviously I'm not talking about print layout, but viewing the whole application in portrait view, as if she turned a monitor sideways and altered the Aps view.

View 6 Replies View Related

Fix For FileSearch In 2007

Feb 9, 2009

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

View 9 Replies View Related

FileSearch Fails In 2007

Aug 22, 2008

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)

View 4 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

FileSearch Method In 2007, Run-time Error 445 Object Doesn't Support This Action

Sep 14, 2007

I was using the following macro on Excel 2000, 2002 & 2003 for many years without a fuss. Recently, I upgraded to Excel 2007. When I run the macro now, I get the following error message:- Run-time error 445 Object doesn't support this action.

Sub test()
With Application.FileSearch
.NewSearch
.LookIn = "C:Documents and SettingsDesktopCommercial Database"
.SearchSubFolders = True
.Filename = "*.*"
.TextOrProperty = "BANK"
.MatchAllWordForms = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
End If
End With
End Sub

Additional info:- The 1st line of my code which is With Application.FileSearch is highlighted in yellow when I run this macro.

View 2 Replies View Related

Application File Search From 2003 To 2007

Jul 28, 2008

in adapting the previous code to MS Excel 2007. I have found that the "application.filesearch" object no longer exist in 2007! The code goes to a file path and extracts the text and places the data in the specified excel column.

here's the original code as given by Parry:
[url]

View 9 Replies View Related

Alternatives To GET.CELL And VB

May 17, 2006

Has anybody created a listing of possible alternative options for the various GET.CELL functions? Ideally, since it gives the annoying warning message at startup, I would prefer not to use the outdated GET.CELL function.

I am specifically interested in determining if a given cell is currently visible (height=0). I can do this with GET.CELL or with a very simple VB Macro - I am just wondering if there is a way to accomplish the task just through built-in excel functions.

View 9 Replies View Related

Alternatives To CELL (filename)

Apr 7, 2009

CELL("filename") gives you the name of the current workbook but if you switch to another workbook and then come back, the name has switched to the other workbook. You have to refresh to make the name switch back to that of the workbook you used the formula in.

What alternatives exist, short of writing some VBA, which is an option, to fetching the name of the workbook via a formula and having it remain the name of that workbook when you go away and come back again?

View 2 Replies View Related

Data Validation From Two Different Named Range Alternatives

Jan 28, 2013

I have a cell for which I need the only options for data to be entered from a list in two different tabs from which I understand cant be done but is it possible to have two drop down options for the same cell?

The attached spreadsheet should explain this slightly better...

View 9 Replies View Related

Closing Another Application From Excel VBA

Sep 19, 2013

I have code that opens a .pdf file and copies the content to an excel spreadsheet.

However, in order to loop through multiple pdf's, I need to be able to close the Adobe Reader application before I open each new pdf file.

With excel being the active application, any suitable code to activate and close Adobe Reader?

View 1 Replies View Related

How To Auto Close Excel Application

Jun 15, 2014

Any way to auto-close the excel application after finishing my macro run?

I tried this code,but it doesn't work... i added a sub and ran it like this:

Sub CloseandSave()
ActiveWorkbook.Close SaveChanges:=True
End Sub

to be more specific,there is a macro running,closes all open workbooks,but excel itself stays open...

View 4 Replies View Related

VBA To Exit Excel Application Not Only Workbook?

Feb 17, 2012

I have some code that I am using to save and close the active workbook, this code works perfectly, but I need the code to also close the excel application, not just the workbook. I have tried active window.close but it didn't work even though when I recorded the actions that was the code that I was give.

background -I set a task in my OS to open the workbook, then I wrote some code on the Workbook.open trigger to run and then save and close, but only the activebook closes.

View 2 Replies View Related

Application.Quit Not Closing Excel

Sep 13, 2006

I hate to have to come back to the forum after I Thanked you all for solving my problem, but here I am.

The code below disables the (X) button, no problems

Option Explicit
Public BooleanForClosing As Boolean
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If BooleanForClosing = False Then
Cancel = True
MsgBox "Please Use Exit Button"
End If
End Sub

The next code saves the workbook , but won't Quit the programme and goes back to the Exit Button ...

View 3 Replies View Related

Excel 2013 :: Worksheet Freeze With Application?

May 28, 2014

I encountered a strange anomaly where a worksheet "freezes" when Application.DisplayFormulaBar = False. I can only replicate this in Excel 2013. Excel 2010, for example, works perfectly well, and as expected.

[Code].....

To replicate the anomaly (Excel 2013 only):

1. In a new workbook, insert an ActiveX command button on "Sheet1", no code required.

2. Run the following code

[Code] .....

3. Click on the command button.

4. Now click on any cell and try to enter a value.

Is your screen "frozen"? If so, go to another sheet, return to Sheet1 and try again. Does it work?

Here's an alternative code for MyTest() that causes no problems. Can spot the difference? Is there a reasonable explanation?

[Code] ....

Even more curious, call the following MyTest3 on Workbook_Open() and the workbook behaves. Run MyTest3 again and the screen starts freezing(!)

[Code] .....

View 3 Replies View Related

Direct Writing To Excel Cell From External Application

Jul 20, 2012

How I can directly write info from an external application to an Excel sheet

Actually, I'm using a special dll collection to do it, but I would need to buy the 64 version soon. Problem is I use only one function of the entire collection so I want to look on how to create it my self. Basically, it took data from my charting software and write directly to my Excel Worksheet.Cell

View 3 Replies View Related

Excel 2010 :: Keyboard Shortcut To Resize Whole Application?

Apr 3, 2014

I need to resize Excel pretty frequently - I need to size the whole Excel app tall and all the way to the left, taking up about a third of my screen - then maximize it - then back to tall and left, etc. I can do it by clicking the "Restore Down" icon in the top right corner, but I can't figure out how to do it using the keyboard.

I've tried Ctrl-F8, Ctrl-F9 and Ctrl-F10, but they all act on individual workbooks within the Excel application. I want to resize the whole Excel application. Does it exist?

View 3 Replies View Related

Excel 2003 :: Saving Files Generated By External Application

Jul 23, 2013

I have the following code which edits an excel file and saves it in a different format, I think want the code to take this newly saved file and open it in an external application (Softplot) and then save it through this new application.

VB:
Sub FormatMacro1a(ws As Worksheet)
ws.Copy
Rows(1).Delete
Columns("D:E").Cut Destination:=Columns("J:K")
Columns("F:K").Cut Destination:=Columns("D:I")
Range("E1:E201").Value = "0"

[Code] .....

As it stands I have the file saving in a new format and I can open a specific file in softplot through VBA however where I am getting stuck is opening my newly saved file and then saving it.

I have tried the following :

VB:
Path = "C:Program FilesSoftPlot-8softplot.exe"
File = "ActiveWorkbook"

View 3 Replies View Related

Bring Excel Application Out Of Minimize Before Displaying Warning Message

May 27, 2014

I am using Windows 7 and have a piece of code that brings up a warning message after 20 minutes to warn the user they have been in a workbook for too long.

If they have the Excel application window minimized, is there a way to get Excel to take the application out of minimized mode into max screen, before revealing the message.

Currently when the warning message activates, the excel icon just flashes in the start bar. Some users may not see this.

Here is the code I have so far:

[Code] ....

I have already tried simply adding a piece of code with maximize screen, however this doesn't seem to work.

View 2 Replies View Related

Excel 2010 :: Application Visible And Display Alerts Not Working

Nov 23, 2011

I am trying to hide the excel 2010 application, open a csv file on a network drive, show the excel application.

The problem is a "Downloading" progress indicator appears, during the workbook open event I assume.

Code:
Sub Macro1 ()
Application.Visible = False
Application.DisplayAlerts = False
Workbooks.Open Filename:="S:LOGT5.CSV"
Application.Visible = True
Application.DisplayAlerts = True

I have seen the similar progress indicator during the "Save" event too. How to hide this?

View 3 Replies View Related

Excel 2003 :: Conditional Formatting Based On Application Of AutoFilter?

Dec 7, 2011

I was wondering if it is possible to have cells formatted based on the presence of an autofilter? I have a large worksheet, and to clean it up I would like to make the entire sheet (minus the category headers) invisible. Then, when the user applies a filter, the cells become visible.

View 1 Replies View Related

1004 - Object Or Application Undefined Error In Macro Excel

Nov 7, 2012

Am getting 1004- object or application undefined error in macro excel.

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],[Book1]Sheet1!R1:R65536,4,FALSE)"

View 4 Replies View Related

Copy And Paste All Data (cells And Charts) To A New Excel Application

Jul 8, 2008

Im simply trying to copy all the data on one sheet, open up a new excel application:

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Add

and then paste all the information to the new workbook. When I try, the charts dont paste, only the values and formats.

View 9 Replies View Related

Field To Populate NETWORKDAYS But Also To Populate Alternatives If Fields Are Blank!

Aug 11, 2009

i have two fields with dates - one field A1 for date authorised (for a data request) and one field B1 for date actioned (data request)

i need a formula to populate in C1 the following:

if A1 is blank then C1 is 'not actioned'
if B1 is blank then C1 is 'not complete'
if both contain dates then C1 to calculate the number of working days between the dates eg. A1 10.08.09, B1 11.08.09...C1 = 1 working day

View 3 Replies View Related

ADVERTISEMENT