Good afternoon all - our company recently switched over to Office2010 (don’t ask why now) and surprise surprise I’m running into macro errors in excel. While I have been able to determine that filesearch is causing this when I go out onto the web I get absolutely confused and lost reading and trying to understand the various excel messagebaords providing answers. Is there anyone on here who can get the following script to work? Or direct me somewhere on the web that can do it for me?
Sub CopySheet() 'Name of Macro
Dim basebook As Workbook 'sets basebook as a temp name and as a workbook
Dim mybook As Workbook 'sets mybook as a temp name and as a workbook
Dim i As Long 'sets the counter as i
Application.ScreenUpdating = False 'freezes the screen
Application.DisplayAlerts = False 'turns off the error messages
Range("B11").Select 'goes to cell in the workbook which is the hotel number
Hotel = ActiveCell.Value 'sets Hotel as the hotel number (Note: Not used)
Range("C11").Select 'goes to cell in the workbook which is the hotel name
HName = ActiveCell.Value 'sets HName as the hotel name
Range("B9").Select 'goes to cell in the workbook which is the directory of the files
Temp = ActiveCell.Value 'sets Temp as the directory
BName = Temp & "\" & HName & " F98.xls" 'Prepares to save the new consolidated file in the same directory
'with file name as the hotel name with F98.xls at end
With Application.FileSearch 'starts a loop
.NewSearch
.LookIn = Temp 'opens the directory
.SearchSubFolders = False 'only looks in the directory, not subdirectories
.FileType = msoFileTypeExcelWorkbooks 'only Excel files will be selected
If .Execute() > 0 Then
Set basebook = Workbooks.Add(xlWBATWorksheet) 'adds a new workbook with one sheet
For i = 1 To .FoundFiles.Count 'loop for all the files in the directory
Set mybook = Workbooks.Open(.FoundFiles(i)) 'opens the first / next file
'(1) in the line below indicates the first sheet will be copied
mybook.Worksheets(1).Copy After:= _
basebook.Sheets(basebook.Sheets.Count) 'copies the first sheet of opened file into the new book as the last sheet of the book
Temp = mybook.Name 'sets temp equal to the name of the file just opened
TLen = Len(Temp) 'counts the length of the filename
For t = 1 To TLen 'loops through the filename
SS = Mid(Temp, t, 1) 'goes through filename one letter at a time
If SS = "-" Then ST = t - 1 'to the first hyphen (end of dept name); sets ST as end of the dept name
If SS = "-" Then t = TLen + 1 'will end the loop once hyphen found
If t = TLen Then ST = t - 4 'if end of filename and no hyphen found, sets ST as end of filename before .xls (4 char)
Next
SName = Left(Temp, ST) 'sets SName as the file name until hyphen (or .xls); should be dept name
ActiveSheet.Name = SName 'Changes the sheet name to SName as determined; should be the dept name
TBook = basebook.Name 'Tbook is the new workbook name
mybook.Close 'closes the opened file that has been consolidated
Next i 'looks for next file in directory
End If 'file consolidation complete
There’s also an End Sub missing, but I assume that was just left out in the copy-paste.
The whole thing could indeed be simplified in several places, but to start with, it’s a mess of partly declared variables, making reading it a real nightmare. Proper indentation would help quite a bit, too.
The basic problem is that Application.FileSearch has been deprecated from Excel 2007 and 2010, and isn’t going to come back.
I’ve followed the workaround outlined here (Solved: Alternative for Application.Filesearch in Office 2010 - VBA | Experts Exchange) and found it to correctly compile and run. Whether it does what you want it to do is another question, of course. Once you’ve added in the references and the new class module, your code looks like this (changes highlighted in red):
Sub CopySheet() 'Name of Macro
Dim basebook As Workbook 'sets basebook as a temp name and as a workbook
Dim mybook As Workbook 'sets mybook as a temp name and as a workbook
Dim i As Long 'sets the counter as i
Application.ScreenUpdating = False 'freezes the screen
Application.DisplayAlerts = False 'turns off the error messages
Range("B11").Select 'goes to cell in the workbook which is the hotel number
Hotel = ActiveCell.Value 'sets Hotel as the hotel number (Note: Not used)
Range("C11").Select 'goes to cell in the workbook which is the hotel name
HName = ActiveCell.Value 'sets HName as the hotel name
Range("B9").Select 'goes to cell in the workbook which is the directory of the files
Temp = ActiveCell.Value 'sets Temp as the directory
BName = Temp & "\" & HName & " F98.xls" 'Prepares to save the new consolidated file in the same directory
'with file name as the hotel name with F98.xls at end
Dim search As New clsFileSearch
With search 'starts a loop
.NewSearch
.LookIn = Temp 'opens the directory
.SearchSubFolders = False 'only looks in the directory, not subdirectories
.FileType = msoFileTypeExcelWorkbooks 'only Excel files will be selected
If .Execute() > 0 Then
Set basebook = Workbooks.Add(xlWBATWorksheet) 'adds a new workbook with one sheet
For i = 1 To .FoundFiles.Count 'loop for all the files in the directory
Set mybook = Workbooks.Open(.FoundFiles(i)) 'opens the first / next file
'(1) in the line below indicates the first sheet will be copied
mybook.Worksheets(1).Copy After:= _
basebook.Sheets(basebook.Sheets.Count) 'copies the first sheet of opened file into the new book as the last sheet of the book
Temp = mybook.Name 'sets temp equal to the name of the file just opened
TLen = Len(Temp) 'counts the length of the filename
For t = 1 To TLen 'loops through the filename
SS = Mid(Temp, t, 1) 'goes through filename one letter at a time
If SS = "-" Then ST = t - 1 'to the first hyphen (end of dept name); sets ST as end of the dept name
If SS = "-" Then t = TLen + 1 'will end the loop once hyphen found
If t = TLen Then ST = t - 4 'if end of filename and no hyphen found, sets ST as end of filename before .xls (4 char)
Next
SName = Left(Temp, ST) 'sets SName as the file name until hyphen (or .xls); should be dept name
ActiveSheet.Name = SName 'Changes the sheet name to SName as determined; should be the dept name
TBook = basebook.Name 'Tbook is the new workbook name
mybook.Close 'closes the opened file that has been consolidated
Next i 'looks for next file in directory
End If 'file consolidation complete
End With
End Sub
To add to Dervorin’s info, you might also look into the InStr built-in function, which will find the hyphen for you without having to write a loop to look at every character.
I also agree that it is better to declare variables. I suggest going into Tools, Options, Edit (from the VBA development window) and checking “Require variable declaration”. This will automatically insert the line
Option Explicit
at the top of each new module (you’ll have to type it into the one you already have). This requires all variables to be explicitly declared. Without this option, all undeclared variables default to type Variant. This seems awfully convenient at first glance but it causes two problems where you can have a bug that is very hard to diagnose:
The first is that if you intend for a variable to be a specific data type, for example, assigning a row number to a variable, and you accidentally use it for something else, it just happily runs. If you assign the letter “a” to a Variant variable but later try to use it as a row number, you will get a runtime error. You will have to search your code and figure out what’s wrong and how it got to be that way. If you declare the variable as Long, then it will fail at the point you try to assign a String value to it, so you will see exactly where the error is occurring.
The second is that if you have a typo in a variable name in the body of the code, it will default to a Variant. You code will happily run with this variable that is most likely not used anywhere else. If you’re lucky you will notice an obvious bug in your results. Then you will have to figure out the problem with no clues as to what could be causing it. If you have Option Explicit it will fail to compile and you see the error right away. (There is an apocryphal story about a bug in some NASA Fortran code caused by a typo in a line that caused it to be interpreted as an undeclared variable; early flavors of Fortran default undeclared variables to REAL, IIRC.)
OH yeah, there is that. I woke up in a cold sweat at 3:00 this morning over that one. I just thank my lucky stars there are no GoTo’s in there or I would have to invoke Dijkstra.
and 10 seconds into reading the responses my eyes already glazed over
I should have been slightly more informative in my question - I should have mentioned I only partially pasted in the macro (only the beginning). This macro is suppose to take the first tab of each file in the folder, move it into a new file and rename the tabs. The macro was written in excel 2003 originally (I think).
I inherited the macro and am far from an expert with them but I will give the work-around instructions a try as Dervorin linked to…
Yes, well, that’s what you get for asking for help from coders! Or in my case, a faux coder.
Give us a shout if you need any more help. One point to note (which tripped me up) is that you need a new *class *module, not a regular module, called clsFileSearch, into which you paste the code in that link. Seems to be pretty straightforward after that.
