PDA

View Full Version : Set fs = Application.FileSearch within 2003 Macro no longer valid?



JDaniel1221
06-03-2015, 01:57 PM
Hello,

I am trying to get this Macro up and running again, but not sure where to start. It worked in Excel 2003, apparently my engineering department never upgraded to 2007, now my Desktop support person has upgraded them to 2010 and their Macros are blowing up left and right. This is one of the more critical ones, and the Debug takes me to the "Set fs = Application.FileSearch" line.

My background in Excel VBA is beg, borrow, and steal from smarter people than me and then manipulate it a little. I don't really "know" the syntax so much as I can navigate someone else's.

If you could help me get this one up and running in Excel 2010, that would be greatly appreciated.

Also, if there is a GENERIC way to fix "Set fs = Application.FileSearch" to make it more compatible with Excel 2010 that would be greatly appreciated. It sounds like they use to use it a lot and a lot of Macros are going to fail soon.

Thanks:


Sub OpenAndCloseAll()
'Open and update all cut lists
clcutdate = Sheets("INFO SHEET").Cells(1, 17) & "-" & Sheets("INFO SHEET").Cells(1, 20)
'drivelocation1 = "C:\Excel\"
drivelocation1 = "\\fltfile04\Production\044CNC\Excel\"
drivelocation2 = "\\fltfile04\Production\044CNC\Excel\Misc\CNC Office Macros\"
'drivelocation2 = "C:\Excel\Misc\CNC Office Macros\"
'psdrivelocation1 = "C:\Excel\Misc\Previous Schedules\" & clcutdate
psdrivelocation1 = "fltfile04\Production\044CNC\Excel\Misc\Previous Schedules\" & clcutdate
FillArrayVariables
'macrotorun = "VS 1 Cut List Formulas.XLS"
'macrotoopen = drivelocation2 & macrotorun
'Workbooks.Open macrotoopen
Set fs = Application.FileSearch
dummy = 0
Workbooks.Open Filename:=drivelocation1 & "2012\VS 2\CNC\2012 VS 2 CNC Schedule.xls", UpdateLinks:=0
ActiveWorkbook.Save
ActiveWorkbook.Close
With fs
.LookIn = drivelocation1 & "2012\VS 1\CNC"
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i), UpdateLinks:=0
ActiveWorkbook.Save
'If (InStr(ActiveWorkbook.Name, "Tags") > 0) Then CreateScheduleBackUp
ActiveWorkbook.SaveAs psdrivelocation1 & "\VS 1\CNC\" & ActiveWorkbook.Name
ActiveWorkbook.Close
Next i
dummy = .FoundFiles.Count
End With
With fs
.LookIn = drivelocation1 & "2012\VS 1\Decor Tied"
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i), UpdateLinks:=0
'Application.Run ("'" & macrotorun & "'" & "!CutListFormulas")
Application.StatusBar = "UPDATED : " & ActiveWorkbook.Name
ActiveWorkbook.Save
ActiveWorkbook.SaveAs psdrivelocation1 & "\VS 1\Decor Tied\" & ActiveWorkbook.Name
If (InStr(ActiveWorkbook.Name, "ACCENT") > 0 Or InStr(ActiveWorkbook.Name, "HDWD") > 0 Or (InStr(ActiveWorkbook.Name, "PREFINISH") > 0 And InStr(ActiveWorkbook.Name, "VS113") = 0)) Then
CreateHolzmaCutList
Else:
CreateScheduleBackUp
End If
Next i
dummy = dummy + .FoundFiles.Count
End With
With fs
.LookIn = drivelocation1 & "2012\VS 1\Pine&Plywood"
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i), UpdateLinks:=0
'Application.Run ("'" & macrotorun & "'" & "!CutListFormulas")
Application.StatusBar = "UPDATED : " & ActiveWorkbook.Name
ActiveWorkbook.Save
ActiveWorkbook.SaveAs psdrivelocation1 & "\VS 1\Pine&Plywood\" & ActiveWorkbook.Name
If (InStr(ActiveWorkbook.Name, "PANEL SAW")) Then
CreateHolzmaCutList
Else:
CreateScheduleBackUp
End If
Next i
dummy = dummy + .FoundFiles.Count
End With
Application.StatusBar = dummy & " CUT LISTS UPDATED"
'Windows("Panel Saw Totals.xls").Activate
'ActiveWorkbook.Save
'ActiveWorkbook.Close
'Windows("Pine Totals.xls").Activate
'ActiveWorkbook.Save
'ActiveWorkbook.Close
End Sub

Kenneth Hobs
06-03-2015, 02:34 PM
Welcome to the forum!

You have your work cut out for you since Excel cut it out in 2007.

A Scripting.FileSystemObject (FSO) method is what most have gone to. I put together a class that someone else created which uses FSO, to do that which is similar to the old method. You can add the Class which is a matter of dragging and dropping from one Excel file (personal or mine) to others in the Visual Basic Editor (VBE). Then just add a few more things and you will be set. See my file with the class at: https://app.box.com/s/dzd5a74mryb9u1907pob

There are times when a simple Dir() method will suffice or another FSO routine. That would require alot more effort, IMHO.

I also use the class method in some other threads in this forum that you can search if you like.

JDaniel1221
06-04-2015, 06:05 AM
Thanks for the response! Some of that is Greek to me though. My apologies in advance.

I am looking at your Class Modules. Are class modules like personally developed functions? For instance, is the Class Module filling in the gap of the missing older function (in this case)? I've really only worked with straight Modules before.

Do I need all 3 Class Modules you have in your files, or just cFileSearch?

Do I need to copy your Modules, or do I just copy your Class Module(s) over and then modify my code above in my own existing Module? Or do I modify the Class Module and it "fills" in the gap of the older missing function?

I'll admit, I'm woefully lacking here as you say, "just add a few more things and you will be set", but I'm not sure what in the existing code exclusively belongs to the existing Set fs = Application.FileSearch, so I'm not sure what piece of the code I am to swap out and replace with in order to accommodate the new Class Module...

I'd more than likely break my existing code right now if I tried to progress with my current understanding. Once again, my apologies.

Kenneth Hobs
06-04-2015, 06:27 AM
You will break your code if you don't understand what you are doing for any workaround.

Yes, copy all 3 classes to yours when you are ready. I prefer drag and drop but you could export them and then import to others.

In my file, test it out first. My test routine to show how easy the cFileSearch class is to use is shown in Module1. Just change the X:\ drive and folder to any, say c:\myfiles\Excel\vbaexpress\ or whatever you like. After you run the routine, you will see the result (file count) in the Immediate Window of the Visual Basic Editor (VBE).

As you can see in my short example, the two lines with the Extra comment are all that you need to modify possibly in your routines once you add the classes. That seems pretty simple to me.

As always, test on backup copies of files and folders if those could be changed.

One last thing that you will need to do is to add via VBE's Tools > References, is the Microsoft Scripting Runtime, as commented in the Subfolder class. I guess I should change that to a late bound method. Early binding like this enables intellisense to work.


'Add, Tools > References... > Microsoft Scripting Runtime, scrrun.dll



Sub Test_cFileSearch()
Dim sFile As String
Dim fs As New cFileSearch 'Extra needed.
Dim sPath As String, i As Long
sPath = "x:\"
With fs 'Extra, used instead of the Application.FileSearch old way.
.LookIn = sPath
.SearchSubFolders = True
.FileName = "*" '*.* in Application.Filesearch
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
sFile = .FoundFiles(i)
' your code here
Next
End If
Debug.Print .FoundFiles.Count
End With
End Sub

I changed the Subfolder routine to use late binding so it would not need the Microsoft Script Runtime reference. It is at: https://www.dropbox.com/s/qt4bjt9qizb5agt/FileSearch.xls?dl=0