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.
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.
i downloaded the beta version of the office 2007 for testing. can i install this version alongside my existing office 2003 version. i want to have the 2 versions installed on my computer so that i can choose between the versions when opening a file.
application.FileSearch.NewSearch application.FileSearch.LookIn = Workbooks(ActiveWorkbook.Name).Path application.FileSearch.FileType = msoFileTypeAllFiles application.FileSearch.SearchSubFolders = True application.FileSearch.Filename = "Zone Selling*.xls" application.FileSearch.MatchTextExactly = True application.FileSearch.Execute filecount = application.FileSearch.FoundFiles.Count For i = 1 To filecount Worksheets("Run").Cells(i, 1) = application.FileSearch.FoundFiles(i) Next i For i = 1 To filecount......................
But it won't list my files which is how the macro was running, it was returning a list of the files in the folder and then running them based on the path returned
I am using Excel 2007 so I CANNOT use application.filesearch.
With that said, I need to change one of my old macros that searched for the newest file within a specified folder, and then display the name of that file in a message box that gives the person an option of opening the file. I've read too many workarounds for application.filesearch and I'm not sure how to combine them all into something I can use.
Normally I have a bit of code to start with, but this one has me so baffled I don't know where to start.
I've created a workbook with code to email it to a reviewer then back to the originator when reviewed (along with other editing functions etc.). The problem - The originator is working in Excel 2003, the reviewer in 2007. The macros work great until the reviewer sends it back. I have not been able to figure out which FileFormat:= ???? to use when I SaveAs prior to emailing as an attachment. My Excel 2003 doesn't recognize the .xlsm file and other formats are eliminated the vba/macro coding
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"
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 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.
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 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 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?
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
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 have some schedules set up with multiple formulas and macros (originally setup in 2003) however when attempting to make this a 2007 file when I go to copy a sheet to create another schedule I get the message A formula or sheet you want to move or copy contains the name xx, which already exists on the destination worksheet. Do you want to use this version of the name? To use the name as defined in the destination sheet, click yes. To rename the range referred to in the formula or worksheet, click no, and enter a new name in the Name Conflict dialog box.
I would like to send a .xls file created in Excel 2007 to a user who will work with this file on a PC that runs Excel 2003. Will they be able to open and use a file created in a newer version of Excel?
I've been putting together an automated spreadsheet that has external data sources to SQL. I have been having some strange problems with it between versions of Excel. Unfortunately, the company's standard is 2003 but quite a lot of the company use 2007. I have been developing this report on 2007, the server that runs the automated version of the spreadsheet runs 2003 (and cannot be upgraded beyond 2003 as it is running Server 2000!).
The template opens fine on my 2007 machine and all development work has been run on my machine including the downloading of data. The VBA script runs something like
My company used to have Excel 2003. I had a PivotTable in a worksheet that accessed data in another, completely different worksheet. When we converted to Excel 2007, I saved many of my Excel files from .xls to .xlsm. My PivotTable that once accessed data from another .xls file is still trying to access that same .xls file. What I want, though, is for it to access the new .xlsm file.
I have a spreadsheet that collates a list of data from week order and puts them in a graph. I have a macro created that basically does the graph, it works fine in 2003 but not in office 2007. I get an error "Run Time Error '1004' Application-defined or object-defined error, then when I click the debug button it highlights "ActiveSheet.ChartObjects.Delete". I'm not sure if when its trying to create a new graph its attempting to delete the old one and isnt deleting it? The full code for the macro is below:
Sub updateStuff() Dim searchResult As Range 'Result of the find operation - to check if it has worked or not Application.ScreenUpdating = False If SheetExists("OTIF Chart") Then Application.DisplayAlerts = False Sheets("OTIF Chart").Delete Application.DisplayAlerts = True End If Sheets("OTIF's").Select Range("B4:N4").Select Selection.Copy Sheets("Graph").Select Range("B1").Select................................
I created a Macro in 2007 and it turns out I needed to develop it in 2003. Now none of my code works and I am trying to convert it. I would appreciate any assistance determining what needs to be corrected and how to correct it. Here iare two samples that is repeated throughout my code.
'Delete anything currently in spreadsheet Application.CutCopyMode = False Range("A2:N1048576").ClearContents Range("A2").Select ActiveWorkbook.Save
For a particular requirement I want to utilise the colours. ANd therefore I need to know, how many colours are present in the Colour Palette in both 2003 and 2007 and which colours would be same if I make the File in 2007?