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
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.........................
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'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 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
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.
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?
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.
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 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 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
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
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................
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)
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.
5427 Data 5427 Data 5427 Data 6565 Data 6565 Data 7765 Data
all I want to do is have the above table look like this :
5427 Data 6565 Data 7765 Data
The concept being that I can double click that row to see the additional information(i.e. expand to show extra information)
Real life use would be of such, the first column contains the invoice number, the 2nd 3rd etc contain item associated with such invoice.In grouping the column invoice number, you only have to look at one invoice, instead of it duplicating for each item contained within the invoice.
This will need to be done in an efficient manner(over 4000 rows need grouped)
I realize I could just create a list that has one copy of the duplicates, but I want to extract the original information as well. For example, it would look like the following :
Original : 1212 Data 1212 Data 2 1212 Data 3 2323 Data 1 2323 Data 2
Note : This grouping will also have to span across the whole row, sample rows : Invoice# Price Color Weight Item 1212 Column1Data Column2Data Column3Data Column4Data etc. 1212 Column1Data Column2Data Column3Data Item2Column4Data etc.
I encounter a runtime error '1004' if the "Invoices" sheet is not selected when I run this procedure. The last line of the code is one which is highlighted when I debug.
Sub ProcessData() Dim aiOldRows() As Integer, aiNewRows() As Integer ' Arrays of new/old rows Dim rngRaw As Range 'Data entry area Dim rngInvoices As Range 'Invoices range Dim rngOpenPoint As Range 'Top-left corner of data entry area Set rngOpenPoint = ThisWorkbook.Worksheets("Data Entry").Range("a3") Set rngRaw = Range(rngOpenPoint, rngOpenPoint.End(xlDown).End(xlToRight)) FindNew aiOldRows, aiNewRows, rngRaw InvoiceSequence aiOldRows, rngRaw Set rngInvoices = Range(ThisWorkbook.Worksheets("Invoices").Range("A2"), _ ThisWorkbook.Worksheets("Invoices").Range("A3").End(xlDown).End(xlToRight)) rngInvoices.Sort Key1:=Range("M2"), Order1:=xlAscending End Sub
You'll notice that there are two other procedures (FindNew & InvoiceSequence) being called by this procedure. I don't think those have anything to do with the error, but I can provide the code for those if needed. Oh, and one other secondary question. To declare the ranges rngRaw & rngInvoices I pick the top-left cell of the data and then do:.....................
i added a "Microsoft office spreadsheet 11.0 " object using additional control and used it in my form of Excel Macro. then i added some data from the combo box to the embedded excel object dispalyed. the form displays correctly and am even able to add data to cells.
but am not able to sort a column. gives me the error message "Sort method of range class failed" have tried to make sure the sheet is active using the ".Activate" with the object name. this is the only solution available on the existing forums.
I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.
The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.
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 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